MySQL Forums
Forum List  »  Optimizer & Parser

Advanced Search
New Topic

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

Navigate: Previous Message• Next Message
Options: Reply• Quote


Subject
Views
Written By
Posted
Analyze question
4793
Dorian Kuroki
July 13, 2010 03:30PM
Re: Analyze question
1599
Rick James
July 14, 2010 10:48PM
Re: Analyze question
1730
Dorian Kuroki
July 15, 2010 11:26AM
Re: Analyze question
1557
Rick James
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.