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:
http://dev.mysql.com/news-and-events/newsletter/2003-04/a0000000155.html

The good news is that the InnoDB "plugin" has a slight improvement:
http://dev.mysql.com/doc/innodb-plugin/1.1/en/glossary.html#glos_random_dive
Note the two VARIABLES mentioned in that entry...
innodb_use_legacy_cardinality_algorithm = OFF
innodb_stats_sample_pages = 50

Options: ReplyQuote


Subject
Views
Written By
Posted
4719
July 13, 2010 03:30PM
Re: Analyze question
1559
July 14, 2010 10:48PM
1697
July 15, 2010 11:26AM
1525
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.