List:General Discussion« Previous MessageNext Message »
From:Richard Baskett Date:July 31 2002 7:22pm
Subject:Re: Speed issues...
View as plain text  
When they are searching sometiems they do not search for Location.. When
that's the case I leave the location string out, if they do not search for
Category, I leave the Category string out, but when they do.. That's the
query I get.. There has got to be a way of making this faster.. It seems
like MySQL would be able to handle this query without a hitch.. So there has
to be something wrong with the query itself...


A wise women once said: " No one can help everybody, but everybody can help
somebody." - Unknown
> 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
> From: Dan Nelson <dnelson@stripped>
> Date: Wed, 31 Jul 2002 13:42:41 -0500
> To: Richard Baskett <rick@stripped>
> Cc: Tod Harter <tharter@stripped>, MySQL
> <mysql@stripped>
> Subject: Re: Speed issues...
> 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 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! :) 

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