Understanding MySQL Index Cardinality and Selectivity

This article introduces MySQL index cardinality (the estimated number of unique values in an index) and explains how its accuracy directly affects the optimizer's ability to choose efficient query plans.

Index selectivity is closely tied to cardinality: higher cardinality yields better selectivity, while low cardinality leads to poorer selectivity and may cause the optimizer to fall back to less optimal indexes or full‑table scans.

Factors influencing cardinality include the number of sample pages, update frequency (typically when 1/16 of pages are modified), data distribution of indexed columns, and manual updates via commands such as ANALYZE TABLE or SHOW TABLE STATUS .

Cardinality can be inspected using SHOW INDEX FROM tablename or by querying the information_schema.statistics table.

The article provides a concrete example with a table ytt_sample containing 100 rows and several indexes (primary key, composite, and single‑column indexes). It shows how to retrieve index cardinalities, demonstrating that the primary key has the highest cardinality, followed by a composite index idx_u1 , while single‑column indexes idx_r4 and idx_r5 have low cardinalities and poor selectivity.

It further illustrates how to calculate composite index cardinalities by counting distinct values for each column combination, and discusses the impact of data distribution on index choice, recommending the creation of a composite index idx_u2(r4,r5) when both columns are frequently queried together.

Through a series of EXPLAIN statements, the article shows how the optimizer may choose a full‑table scan or an index scan depending on the selectivity of the filter condition, the amount of data returned, and the current cardinality values. It demonstrates that the same query can have different execution plans before and after data distribution changes, as reflected by updated cardinality numbers.

Finally, the article emphasizes that index design must consider data distribution and cardinality, as these factors heavily influence query performance and the optimizer's decisions.