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

[Øystein Grøvlen]

Shane Cruz Wrote:
-------------------------------------------------------
> 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?

Hi,

It seems that your issue is that ANALYZE is giving you inaccurate estimates. If that is the case, it will not help much to re-run ANALYZE since you might get even less accurate estimates on the next attempt. On the other hand, if the problem is that the statistics are outdated because your data has changed a lot since the last time it was generated, running ANALYZE might help.

To get more accurate estimates, you can change the setting of the system variable innodb_stats_sample_pages. It determines the number of index pages that are sampled in order to calculate the statistics. The default value is 8. As described in http://oysteing.blogspot.com/2011/04/more-stable-query-execution-time-by.html, I got more stable execution times by setting this variable to 100.

See also the manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-other-changes-statistics-estimation.html) for more information on how to control InnoDB statistics.

Hope this helps,

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway