MySQL Index Cardinality Explained

Summary: in this tutorial, you will learn about the MySQL index cardinality and how to view the index cardinality using the SHOW INDEXES command.

Introduction to MySQL Index Cardinality

Index cardinality refers to the uniqueness of values stored in a specific column within an index.

MySQL generates the index cardinality based on statistics stored as integers, therefore, the values 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 index cardinality to decide whether to use the index or not in the join operations.

If the query optimizer chooses the index with low cardinality, it may be more effective than scanning rows without using the index.

The terms “high” and “low” cardinality are relative. Typically, they are assessed in comparison to the total number of rows in a table.

Showing index cardinality

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 you don’t see the index cardinality column to see the index cardinality.

The orders table has two indexes PRIMARY and customerNumber.

The orderNumber column has index cardinality of 326 which is high because the number of rows in the orders table is 326.

The customerNumer column has an index cardinality of 98, meaning that there may be 98 unique values in the customerNumber column.

To verify it, you can count distinct values in the customerNumber column:

SELECT COUNT(DISTINCT customerNumber) 
FROM orders;

Output:

+--------------------------------+
| COUNT(DISTINCT customerNumber) |
+--------------------------------+
|                             98 |
+--------------------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

If a column has high cardinality, it’s likely to be a good candidate for indexing. For example, the following query that retrieves orders from a customer is likely to benefit from the index since the cardinality is relatively high:

SELECT * FROM orders 
WHERE customerNumber = 103;

Summary

Index cardinality measures the uniqueness of values in a specific index column.
MySQL uses index cardinality to optimize database performance and query efficiency.

Was this tutorial helpful?