List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 31 2002 6:42pm
Subject:Re: Speed issues...
View as plain text  
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
	dnelson@stripped
Thread
Speed issues...Richard Baskett31 Jul
  • Re: Speed issues...Roger Baklund31 Jul
  • Re: Speed issues...Tod Harter31 Jul
  • Re: Speed issues...Troy Hakala31 Jul
Re: Speed issues...Richard Baskett31 Jul
  • Re: Speed issues...Dan Nelson31 Jul
    • Re: Speed issues...Richard Baskett31 Jul
      • Re: Speed issues...Dan Nelson1 Aug
        • Re: Speed issues...Richard Baskett1 Aug
          • Re: Speed issues...Richard Baskett1 Aug
          • Re: Speed issues...Dan Nelson1 Aug
  • Re: Speed issues...Tod Harter2 Aug