On 14 Aug 2002, at 14:46, Richard Baskett wrote: > 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:
You say the EXPLAIN output looks the same, so the 5 second savings is just random. Nothing changed. And sorry about the STRAIGHT_JOIN advice. Looking at the documentation again I see that it doesn't do what I thought it did. > 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 One more suggestion: Change the first INNER JOIN to a LEFT JOIN. It's a little silly, since you don't really want those nulls, but it should force Location to be selected first. The records with nulls should be eliminated by the later inner joins, or failing that by the JobTitle criterion. I thought there was a better way to control the process, but I don't see it at the moment. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org --------------------------------------------------------------------- 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
