MySQL Forums
Forum List  »  Optimizer & Parser

Re: Different Servers Using Different Index for Same Query on Same Data
Posted by: Shane Cruz
Date: March 30, 2011 10:05AM

Thanks for the information, that is very helpful. What are some best practices that might prevent this from occurring in the future? Should we schedule a monthly process that runs OPTIMIZE on these large tables (to reduce fragmentation) and then runs ANALYZE to refresh the statistics?

This causes serious performance problems when it happens, so we are trying to find a way to make sure it doesn't happen again. We could always use FORCE INDEX on this specific query, but I was under the impression that using that isn't recommended. I also see that we can set max_seeks_for_key to a low value like 100 or 1000 to make sure a table scan doesn't happen, but are there downsides to setting that so low?

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.