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