Actually if I just run the Location/Category query once and throw it into an
array, then take the variables that way.. That should cut off that part of
the MySQL query.. Very nice! You've given me a light.. This tunnel was
really beginning to get dark.. :)
Thanks! And of course anymore ideas would be greatly appreciated!
"Work like you don't need the money. Dance like no one is watching. And love
like you've never been hurt." - Mark Twain
> From: Richard Baskett <rick@stripped>
> Date: Wed, 31 Jul 2002 13:41:56 -0700
> To: Dan Nelson <dnelson@stripped>
> Cc: MySQL <mysql@stripped>
> Subject: Re: Speed issues...
> Ok I've Analyzed each table and it looks like it has cut around 20 seconds off
> of the average time.
> Im trying to figure out how I can take out the left joins.. I need some of
> that data to be displayed to the user.. Would it be quicker to get rid of the
> location and category left join and then for every displayed record find the
> location that corresponds with that Location ID in the main query? That seems
> like a whole lot of hits on the database...
> With that number you gave me.. I can see why it's taking so long..
> "And God shall wipe away all tears from their eyes; and there shall be no more
> death, neither sorrow, nor crying, neither shall there be any more pain: for
> the former things are passed away." - Revelation 21:4
>> From: Dan Nelson <dnelson@stripped>
>> Date: Wed, 31 Jul 2002 15:09:24 -0500
>> To: Richard Baskett <rick@stripped>
>> Cc: MySQL <mysql@stripped>
>> Subject: Re: Speed issues...
>> In the last episode (Jul 31), Richard Baskett said:
>>> 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
>> Well, with no criteria, it should run pretty fast. The problem is that
>> criteria based on Category and Location fields cannot speed the query
>> up, because your left joins force it to look at Employers, then pull
>> matching Jobs for that record, then pull JobsLocation for that Job,
>> then pull Location for that JobLocation and only then can it start
>> Take out the left joins, and your EXPLAIN output should show that at
>> least one of Category and Location have floated up to the top, and the
>> total estimated rows scanned will be much lower (the current explain's
>> count is 46175*3*581*581 = 46760637525). Also try running an ANALYZE
>> TABLE on each table to update the key distributions for each index.
>> Dan Nelson