If you were here right now I would kiss you! Well not really, but I can not believe what that did!! It takes around 2-4 seconds now for the query to execute completely! I wish I understood when to use what type of join since it is very obvious to me that it matters, and matters greatly! Thank you so much!
Here is the new Explain (sorry about the formatting due to email program): Location range LocName LocName 55 NULL 7 where used; Using temporary; Using filesort JobsLocation index NULL PRIMARY 8 NULL 80732 Using index Jobs eq_ref PRIMARY,EmpID PRIMARY 4 JobsLocation.JobID 1 where used Employers eq_ref PRIMARY PRIMARY 4 Jobs.EmpID 1 where used Rick "Youth furnishes the materials and plans for the future. Maturity takes and cuts the stones; provided the so-called wisdom of old age doesn't destroy the genius of youth." - Unknown > From: "Keith C. Ivey" <[EMAIL PROTECTED]> > > 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
