MySQL Forums
Forum List  »  Optimizer & Parser

Re: Different Servers Using Different Index for Same Query on Same Data
Posted by: Jørgen Løland
Date: March 31, 2011 11:52PM

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.

Options: ReplyQuote

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.