MySQL Forums
Forum List  »  Optimizer & Parser

Re: Analyze question
Posted by: Rick James
Date: July 14, 2010 10:48PM

You failed to say so, but I'll bet the table is InnoDB.

ANALYZE and other cardinality metrics are poorly estimated in InnoDB. And they are not saved adequately. InnoDB, as you open the table, makes 8 (or is it 10?) random probes to determine the statistics. That is a woefully small number for huge tables, and the "randomness" is poor. This old page is still reasonably valid:

The good news is that the InnoDB "plugin" has a slight improvement:
Note the two VARIABLES mentioned in that entry...
innodb_use_legacy_cardinality_algorithm = OFF
innodb_stats_sample_pages = 50

Options: ReplyQuote

Written By
July 13, 2010 03:30PM
Re: Analyze question
July 14, 2010 10:48PM
July 15, 2010 11:26AM
July 15, 2010 08:54PM

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.