Ok that saved me 5 seconds per query! So far so good! I couldnąt use the STRAIGHT_JOIN though (received errors) so I made it an INNER JOIN. This is the new query:
SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC The explain still looks the same though... Thanks! Rick "Not one of them who took up in his youth with this opinion that there are no gods, ever continued until old age faithful to his conviction." - Plato >> SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS >> Comp, Jobs.JobID, Employers.EmpID >> FROM Employers >> INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID >> INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID >> INNER JOIN Location ON JobsLocation.LocID = Location.LocID >> WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( >> LocName >> LIKE 'US-Alabama%' >> ) >> ORDER BY Loc ASC > > Maybe try changing the FROM part to > > ... > FROM Location > STRAIGHT_JOIN JobsLocation ON JobsLocation.LocID = Location.LocID > INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID > INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID > ... > > to force MySQL to use the Location table first? I'm assuming the > location selection is the criterion that will really cut down the > number of rows (as opposed to the Valid and JobTitle checks). --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
