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".
Subject
Views
Written By
Posted
4794
February 18, 2009 04:54AM
2418
February 18, 2009 09:04AM
2519
February 18, 2009 11:57AM
2488
February 19, 2009 01:00AM
2444
February 19, 2009 01:52AM
2463
February 19, 2009 03:32AM
1882
February 19, 2009 05:08AM
2337
February 19, 2009 12:24PM
2418
February 19, 2009 10:53PM
2534
February 20, 2009 08:34AM
2323
March 18, 2009 03:58AM
2436
February 21, 2009 06:15AM
2390
February 24, 2009 10:02AM
2644
February 26, 2009 02:30AM
2214
February 26, 2009 09:56AM
2415
February 26, 2009 08:14PM
2420
February 27, 2009 01:38AM
2307
February 27, 2009 07:56PM
2320
February 28, 2009 06:20AM
Re: Queries slow down daily, optimizing helps -> why?
2691
February 28, 2009 03:43PM
2444
February 27, 2009 04:21AM