query plan gets worse

[bytemare]

Mysql, 5.5.21, high load, but very few slow queries. How, after about a week, we start to see like 30 slow queries a minute, and it gets worse and worse. The only thing I can do is restart the server. I did “explain” on one of the slow queries, and the result is unexpected, completely using the wrong index. After a server restart, I run the explain again and everything is fine (this query is no longer “slow” when it uses the correct index).

How do I go about finding out why this happens? So, one query is fine for about a week. Then suddenly it’s not, because mysql chooses the wrong query plan. Restart the server – query plan is fine, query run fast.

Thanks!

[sterin71]

What happens if you run ANALYZE on the table in question?

Is the index you are referring to close to ideal? I often consider that when the optimizer choose wrong it has to choose between two bad ones hence the ambivalent behavior.

Or check out the innodb_stats_sample_pages variable. Since if your table is large then you can increase the number of pages in the statistics sample without too much overhead and give the optimizer better statistics to work with.

[przemek]

Inconsistency at choosing the query plan in MySQL drives me crazy too
I guess the only thing you can do is to drop the wrong indexes (if they are not needed for other queries), or just use the query hints (http://dev.mysql.com/doc/refman/5.5/en/index-hints.html).

Even if you get consistent results after e.g. optimize table, you never know when MySQL will start to use the wrong index again.

[bytemare]

The one index that usually gets picked is pretty close to ideal. The other index isn’t even close, hence the query takes a long time. But why does this happen? I have done analyze table, but the other thing that makes it “go back to normal” is restarting the server. Can’t figure out what happens with mysql that makes suddenly choose bad execution plans. By the way, it’s not just this one query, it’s like one day all of the sudden everything starts running bad until the server gets huge load from all of these queries running slowly. I save off the innodb status during the issue but I don’t’ see anything unusual there…

[xaprb]

What’s happening is unstable statistics, and there are a variety of fixes for this that have been available for a while in Percona Server and in standard MySQL (although some are not released until MySQL 5.6 comes out). Check the manual for storing statistics in a table and for disabling automatic statistics update.

[federated_life]

Have you tried specifying an index to use?

http://dev.mysql.com/doc/refman/5.0/en/index-hints.html