Skip to content

CONCEPT Cited by 1 source

Index cardinality

Cardinality is the count of distinct values in an indexed column — computed as COUNT(DISTINCT col). It is a raw number (e.g. 3,009 distinct first names), not a ratio. MySQL maintains a sampled cardinality per index and exposes it via SHOW INDEXES and information_schema.STATISTICS.

(Source: sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index.)

Observing cardinality

SHOW INDEXES FROM people;

-- | Key_name   | Column_name | Cardinality | Visible |
-- | PRIMARY    | id          |      491583 | YES     |
-- | first_name | first_name  |        3028 | YES     |
-- | state      | state       |           1 | YES     |

The Cardinality column is the stored estimate — MySQL does not scan the table to compute it. It comes from random sampling performed at index build time and refreshed automatically once "10% of a table has changed" (see concepts/mysql-index-statistics).

Cardinality vs selectivity

Cardinality alone is insufficient for planner decisions — a column with 3,009 distinct values could be either highly selective (on a 3,100-row table, selectivity ≈ 0.97) or poorly selective (on a 491,583-row table, selectivity = 0.0060). The planner needs both cardinality and total row count to compute selectivity:

Cardinality refers to the number of distinct values in a particular column, while selectivity is a percentage of how unique those values are.

— Aaron Francis, Why isn't MySQL using my index?

For practical index-audit purposes both are useful: cardinality is the raw datum (directly exposed in SHOW INDEXES), selectivity is the derived ratio the planner reasons with internally.

Unique indexes have cardinality = row count

Because every row has a distinct value, a unique index satisfies COUNT(DISTINCT col) = COUNT(*) and thus cardinality equals the table row count. This makes unique indexes perfectly selective by construction — they always narrow a lookup to exactly one row.

Low-cardinality warning signs

A cardinality of 0 or 1 often indicates:

  • Empty table — no data has been inserted yet.
  • Degenerate column — only one distinct value across all rows (e.g. a bool flag that's always false).
  • Never-refreshed statistics — the stats haven't caught up with inserts (rare but possible immediately after bulk load if auto-refresh hasn't triggered).

The cardinality-based unused-index detection pattern uses exactly this signal — cardinality = 0 OR cardinality IS NULL in information_schema.STATISTICS — as a cheap first-pass filter for unused-index audit.

Seen in

Last updated · 470 distilled / 1,213 read