what version of mysql are you running? I was having similar problem then I upgraded my mysql to 3.23.51 and the problem was solved.
Richard Baskett wrote: > Ok Im still having some serious speed problems and obviously since more > records are being added every day the problem is getting worse. Here is the > query: > > 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 > > EXPLAIN > table type possible_keys key key_len ref > rows Extra > Employers ALL PRIMARY NULL NULL NULL > 56467 where used; Using temporary; Using filesort > Jobs ref PRIMARY,EmpID EmpID 4 > Employers.EmpID 1 where used > JobsLocation ref PRIMARY PRIMARY 4 > Jobs.JobID 1 Using index > Location eq_ref LocID,LocName LocID 2 > JobsLocation.LocID 1 where used > > And yes I need all of those tables.. I took people's advice and used inner > join instead of left join. It takes 52 seconds now... still not even close > to satisfactory. Once I speed it up enough I am going to save the results > to a table with a timestamp and periodically update those results and call > those results if the particular query is being called again within the time > limit. > > Any help would be appreciated. Plus I was told in the last thread that > PRIMARY key is not an Index.. Is this true? Thanks! > > Rick > > "Science without religion is lame, religion without science is blind." - > Albert Einstein --------------------------------------------------------------------- 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
