CODEWITHSUNDEEP
mysql
David542
David542

Reputation: 110382

Figuring out cardinality

How is cardinality calculated?

When I do:

select count(distinct(content_type)) from mturk_imdbentry

I get 10 distinct values. But when I check the database, it lists the cardinality of that column as 19. Why is this so?

Upvotes: 1

Views: 96

Answers (1)

spencer7593
spencer7593

Reputation: 108460

The method used for the estimation for "cardinality" value depends on the storage engine.

InnoDB uses sampling. How that's done (number of index dives, number of pages inspected), and when that happens, depends on the version of InnoDB, and the settings of some variables, e.g.

use_legacy_innodb_algorithm
innodb_stats_on_metadata
innodb_stats_persistent
innodb_stats_persistent_sample_pages
innodb_stats_auto_recalc
innodb_stats_method

With MyISAM: 

myisam_stats_method

Some additional information is available in the MySQL Reference Manual

http://dev.mysql.com/doc/refman/5.5/en/myisam-index-statistics.html

Upvotes: 1

Related Questions