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 <[EMAIL PROTECTED]> > Date: Wed, 31 Jul 2002 15:09:24 -0500 > To: Richard Baskett <[EMAIL PROTECTED]> > Cc: MySQL <[EMAIL PROTECTED]> > 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 > [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
