MySQL Index Cardinality
Summary: in this tutorial, you will learn about the MySQL index cardinality and how to view the index cardinality using the SHOW INDEXES command.
Index cardinality refers to the uniqueness of values stored in a specified column within an index.
MySQL generates the index cardinality based on statistics stored as integers, therefore, the value may not be necessarily exact.
The query optimizer uses the index cardinality to generate an optimal query plan for a given query. It also uses the index cardinality to decide whether to use the index or not in the join operations.
If the query optimizer chooses the index with a low cardinality, it is may be more effective than scan rows without using the index.
To view the index cardinality, you use the SHOW INDEXES command.
For example, the following statement returns the index information of the orders table in the sample database with the cardinality (*):
mysql> SHOW INDEXES FROM orders;
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| orders | 0 | PRIMARY | 1 | orderNumber | A | 326 | NULL | NULL | | BTREE | | | YES |
| orders | 1 | customerNumber | 1 | customerNumber | A | 98 | NULL | NULL | | BTREE | | | YES |
+--------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.01 sec)Code language: PHP (php)
(*) scroll to the right of the output to view the index cardinality.
In the output, the PRIMARY KEY for the orderNumber column shows the table has 326 unique values, while the customerNumer column only has 98 distinct values.
As mentioned earlier, index statistics are only approximate and may not represent the real size of the rows in the table. To generate more accurate statistical information, you use the ANALYZE TABLE command.