Why my MySQL composite index has less Cardinality than a single index on the same column?

Question

I first created 2 separate indexes in my table: uid and time. Then I decided to create a composite index(uid, time). But why the Cardinality of uid in a composite index(row 3) is less than the Cardinality of uid in a single index(row 1)?

mysql> show index from full_data;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| full_data |          1 | uid       |            1 | uid         | A         |    26394792 |     NULL | NULL   | YES  | BTREE      |         |               |
| full_data |          1 | time      |            1 | time        | A         |     6934463 |     NULL | NULL   | YES  | BTREE      |         |               |
| full_data |          1 | composite |            1 | uid         | A         |    23166632 |     NULL | NULL   | YES  | BTREE      |         |               |
| full_data |          1 | composite |            2 | time        | A         |    86380688 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.05 sec)

Statistics not updated yet? Try ANALYZE TABLE full_data; – fifonik Apr 10 '19 at 23:10 — fifonik, Apr 10 '19 at 23:10

score 0 · Accepted Answer · answered Apr 22 '19 at 22:38

Cardinality, in that context, is a crude estimate based on a few random probes into the index BTree. INDEX(uid) performs one set of random probes; INDEX(uid, time) probes a different BTree.

When you have both INDEX(uid) and INDEX(uid, time), there is virtually no need to keep the former. It clutters the disk, adds to insert/update/delete time, and does not noticeably speed up SELECT. It could even slow down SELECT sometimes.

ANALYZE TABLE will re-probe to refresh the cardinality statistics. The values are likely to change, but the accuracy may or may not improve.

Why my MySQL composite index has less Cardinality than a single index on the same column?

1 Answers1