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

[Shane Cruz]

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?