Hi To help improve the performance, it would help knowing
- table structures, use show create table <name>; - how many records in each table - have all the tables being analyzed/optimized recently Inner joins whilst very useful, do impact on performance. In some cases it is more efficient to use a single simple main query to produce the raw data that can be enhanced by many smaller queries. This approach is usually most valid with queries on very large data sets that return small amounts of data. I hope this helps Simon 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: > >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 > > > > --------------------------------------------------------------------- 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
