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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
