In the last episode (Jul 31), Richard Baskett said:
> >> SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company
> >> AS Comp, Jobs.JobID, Employers.EmpID
> >> FROM Employers
> >> LEFT JOIN Jobs USING (EmpID)
> >> LEFT JOIN JobsLocation USING (JobID)
> >> LEFT JOIN Location USING (LocID)
> >> LEFT JOIN JobsCategory ON Jobs.JobID=JobsCategory.JobID
> >> LEFT JOIN Category USING (CatID)
> >> WHERE Valid = '1' AND JobTitle IS NOT NULL
> >> AND (LocName LIKE 'US-Alabama-Birmingham%')
> >> AND Category.CatID IN ('2')
> >> ORDER BY Loc ASC LIMIT 0,50
>
> Ok here is the EXPLAIN query:
>
> table type possible_keys key key_len ref rows
>Extra
>
> Employers ALL NULL NULL NULL NULL 46175
>where used; Using temporary; Using filesort
> Jobs ref EmpID EmpID 4 Employers.EmpID 3
>where used
> JobsLocation ref PRIMARY PRIMARY 4 Jobs.JobID 581
>Using index
> Location eq_ref LocID LocID 2 JobsLocation.LocID 1
>where used
> JobsCategory ref PRIMARY PRIMARY 4 Jobs.JobID 581
>Using index; Distinct
> Category eq_ref PRIMARY,CatID PRIMARY 1 JobsCategory.CatID 1
>where used; Using index; Distinct
>
> Hopefully you can make out what it says in all of that jumble! :)
I reformatted it to make it more readable. My guess is all those LEFT
JOINs are killing you. Do you really have Jobs with no locations or
categories, and if you do, do you really care about them when you have
Category and Joblocation fields in your WHERE clause? Mysql can't
reorder the tables to look at Category and JobsLocation first when
they're constrained with LEFT JOIN.
--
Dan Nelson
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
