Re: Queries slow down daily, optimizing helps -> why?

[Rick James]

"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".