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
