MySQL Forums
Forum List  »  Optimizer & Parser

Re: Different Servers Using Different Index for Same Query on Same Data
Posted by: Øystein Grøvlen
Date: April 12, 2011 03:49AM

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

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.