MySQL index cardinality

The cardinality of an index is the number of unique values ​​in the index’s columns. It is an estimate based on statistical information and is not necessarily exact.

The cardinality of the index is an important basis for the MySQL query optimizer to decide whether to use the index. The higher the index cardinality is, the more efficient the index is.

If the cardinality of the index is very low, a full table scan may be more efficient than using the index.

Show index cardinality

To view index cardinality, use the SHOW INDEXES command. In returned columns of SHOW INDEXES, the value in the Cardinality column is the index cardinality. It is an integer representing the number of unique values ​​in the indexed column.

We demonstrate it using the film table from the Sakila sample database.

The following SHOW INDEXES statement returns all indexes in the film table:

SHOW INDEXES FROM film;

+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| film  |          0 | PRIMARY                     |            1 | film_id              | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_title                   |            1 | title                | A         |        1000 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_language_id          |            1 | language_id          | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| film  |          1 | idx_fk_original_language_id |            1 | original_language_id | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)

We saw that each index has different index cardinality.

• Because the PRIMARY index column is the primary key, the number of index cardinality and the number of rows are the same.

• The base of the idx_title index is also 1000.

• The idx_fk_language_id index has a cardinality of 1 because all films in the film table have language_id 1. As we discussed in the Forcing Indexes tutorial, the MySQL query optimizer will not use an idx_fk_language_id index because its cardinality is too small. The following EXPLAIN statement says it all:

  EXPLAIN
  SELECT *
  FROM film
  WHERE language_id = 1;
  

  +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | film  | NULL       | ALL  | idx_fk_language_id | NULL | NULL    | NULL | 1000 |   100.00 | Using where |
  +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)

Conclusion

The Cardinality column returned by the MySQL SHOW INDEXES statement is the index cardinality. Index cardinality is an important basis for the MySQL query optimizer to decide whether to use an index.