Re: Different Servers Using Different Index for Same Query on Same Data

[Jørgen Løland]

Hi Shane,

I'd say that this is exactly what FORCE INDEX is for: you happen to know which index should be used, but MySQL sometimes makes the wrong decision. The downside is that if the data changes so that in the future, another index/table scan is preferable, MySQL will still do as you explicitly told it to.

From your original posting, you say that the problematic query uses a wrong index, not table scan. I think max_seeks_for_key would be helpful only if the query was resolved by a table scan when an index would have been better.

Anyway, the problem you're facing is that the statistics are off. IMHO, this is best solved by refreshing the statistics. If you need to reclaim unused space in the clustered index [1], OPTIMIZE TABLE should be run every now and then. Otherwise, ANALYZE TABLE should be run every now and then instead (it's cheaper than OPTIMIZE). Without any knowledge of the usage pattern of your database, it's impossible to say what a plausible refresh interval is. Note that OPTIMIZE TABLE also updates the statistics, so running ANALYZE after OPTIMIZE is not necessary.

[1] I.e., if columns in your primary key have been updated or records have been deleted. I have no idea if this happens in your database.