MySQL Forums
Forum List  »  Performance

Re: Queries slow down daily, optimizing helps -> why?
Posted by: Rick James
Date: February 28, 2009 03:43PM

"ran for years..." -- Some problems (like yours) were always a problem, but no on noticed until the data grew to the point where it was too big for cache, or it got so slow that people noticed, or whatever.

Sometimes adding an index helps, sometimes modifying an index, sometimes redesigning the SELECT, sometimes ANALYZE TABLE, sometimes OPTIMIZE TABLE, sometimes ALTER TABLE ORDER BY something, sometimes pulling blobs out, sometimes compressing text, ... I have found dozens of "fixes" for "slow" (or "slower than it used to be") queries.

I would love to have an algorithm that says "when EXPLAIN says X, do Y". But I haven't found the algorithm yet. When I get close, it often turns out to be "when EXPLAIN says X, do Y, _except_ if Z".

Your problem took several turns in finding the magic bullet...
* key_buffer
* query_cache
* indexes
* select structure

And will the lessons learned here help with the next query? Yes and no -- Yes in that you can avoid some of the pitfalls here, but No in that the next query may have a totally different "X" and "Y".

Options: ReplyQuote

Written By
March 18, 2009 03:58AM
Re: Queries slow down daily, optimizing helps -> why?
February 28, 2009 03:43PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.