List:General Discussion« Previous MessageNext Message »
From:Richard Baskett Date:July 31 2002 8:47pm
Subject:Re: Speed issues...
View as plain text  
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!

Cheers!

Rick

"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..
> 
> Rick
> 
> "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
>>> itself...
>> 
>> 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
>> filtering.
>> 
>> 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
>> 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