[RESOLVED] What's wrong with this SQL statement?

hagen00

The following SQL statement is taking a long time to execute. If one of the tables that it queries has around 30 or so records in it, it takes up to 30 seconds to execute.

SELECT DISTINCT(jobseeker.JobSeekerID) FROM jobseeker,relocatecountryselected, relocateregionselected, jobseekerlanguage,workpermitscountryselected,educationselected WHERE jobseeker.JobSeekerID = relocatecountryselected.JobSeekerID AND jobseeker.JobSeekerID = relocateregionselected.JobSeekerID AND jobseeker.JobSeekerID = jobseekerlanguage.JobSeekerID AND jobseeker.JobSeekerID = workpermitscountryselected.JobSeekerID AND jobseeker.JobSeekerID = educationselected.JobSeekerID

All that is happening here is that tables are being joined by the JobSeekerID. Each of the tables (except table jobseeker) might have the JobSeekerID more than once. e.g. a jobseeker may want to relocate to more than one country, and therefore the relocatecountryselected table will have the same JobSeekerID in it more than once. That's also why I use DISTINCT, since I just want the Distinct JobSeekerID returned, even if they are relocating to more than one country.

Any ideas why it's taking so long to execute? It eventually gets there, just takes forever.

Do you need more info? Should I use different SQL?

Many thanks

(This is just a part of the query. I append stuff to it, but that isn't causing any problems. )

bogu

Try this one:

SELECT 
	j.JobSeekerID
FROM jobseeker j
LEFT JOIN relocatecountryselected r ON j.JobSeekerID = r.JobSeekerID
LEFT JOIN relocateregionselected r1 ON j.JobSeekerID = r1.JobSeekerID
LEFT JOIN jobseekerlanguage ON j1 j.JobSeekerID = j1.JobSeekerID
LEFT JOIN workpermitscountryselected w ON j.JobSeekerID = w.JobSeekerID
LEFT JOIN educationselected e ON j.JobSeekerID = e.JobSeekerID
GROUP BY j.JobSeekerID

And check this: Optimizing Queries with EXPLAIN

Piranha

First I can't see any reason to join with other tables except if you don't want results that is not in the other tables. And since you want it distinct I don't think that you want any values from the other tables.

Second, why it takes time. The two things you can do with a RDBMS that takes most time is to join tables and order the result. Joining 6 tables then take time. To lower the time taken you can (and should) index JobSeekerID in all tables, then the RDBMS don't have to read the whole table to join the tables. Another thing that might or might not do it faster is to use GROUP BY instead of DISTINCT.

Third, are you sure that the time is in the query? The time may be used in PHP, especially if you try to order the result there. Use [man]microtime[/man] to find out exactly where the problem is.

hagen00

bogu wrote:

Try this one:

SELECT 
	j.JobSeekerID
FROM jobseeker j
LEFT JOIN relocatecountryselected r ON j.JobSeekerID = r.JobSeekerID
LEFT JOIN relocateregionselected r1 ON j.JobSeekerID = r1.JobSeekerID
LEFT JOIN jobseekerlanguage j1 ON  j.JobSeekerID = j1.JobSeekerID
LEFT JOIN workpermitscountryselected w ON j.JobSeekerID = w.JobSeekerID
LEFT JOIN educationselected e ON j.JobSeekerID = e.JobSeekerID
GROUP BY j.JobSeekerID

And check this: Optimizing Queries with EXPLAIN

That's fantastic. Thanks a lot. The query now takes a fraction of a second. I think i need to do some serious reading up on my SQL. I've come across join before, but never used it.