Index Cardinality refers to the uniqueness of values for a given column within an index. The value can be considered as the number of probable unique values found. Looking at the Primary Key or an Unique Key will provide a representative large number matching approximately(*) to the number of table rows. Having an index on a column with a small number of unique values will show a small number. For example, a status column with only the values of ‘Active’ or ‘Inactive’ will have a cardinality of 2.
You should always strive to create indexes with a good cardinality. If MySQL chooses an index with poor cardinality, it may be more effective to scan all data rows sequentially without an index. The SHOW INDEXES command can be used to view the cardinality of indexes. For example:
mysql> SHOW INDEXES FROM example; +---------+------------+----------+--------------+--------------+-----------+-------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | +---------+------------+----------+--------------+--------------+-----------+-------------+ | example | 0 | PRIMARY | 1 | example_id | A | 27595 | | example | 1 | FK_col1 | 1 | col1_id | A | 9198 | | example | 1 | FK_col2 | 1 | col2_id | A | 13797 | | example | 1 | FK_col3 | 1 | col3_id | A | 2 | +---------+------------+----------+--------------+--------------+-----------+-------------+
NOTE: This output has been modified for display purposes, some columns have been removed.
In the above output, the PRIMARY key for column example_id shows the table has 27595 unique values, while the col2_id column only reports 2 unique values.
(*) Index statistics are only approximate and may not represent the true size of the rows in the table. The ANALYZE TABLE command can be used to calculate more accurate information. This also may vary depending on the Storage Engine used.
