-1

I'm using MySQL 5.6. There is a MYISAM table with 1 million rows. The 'county' column in this table has a BTREE index on it, and the column only has 61 possible values. When I run 'show index in TABLE' on this table, it reports that the cardinality of the index on the 'county' column is 117554.

I am expecting the cardinality of a column index to reflect the number of unique values in the column. The numbers are very different in this case -- is that indicating some kind of problem, like a corrupted index?

I have run 'optimize table' and it does not change the cardinality of this index.

John Malpas
  • 1

1 Answers1

0

The MySql documentation on indexes states:

Cardinality

An estimate of the number of unique values in the index. To update this number, run ANALYZE TABLE or (for MyISAM tables) myisamchk -a.

Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

The bolded emphasis is mine. A lot goes into calculating cardinality on an index, including server memory availability and disk space.

I wouldn't focus on the cardinality calculation not being correct, especially since you have already run the optimize table command. Instead, check to make sure your queries are utilizing the index by running explain plans on your queries.

Sam M
  • 4,136
  • 4
  • 29
  • 42