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...
Rick
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 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! :)
