Understanding Index Cardinality

Analyze index selectivity to decide whether to index a specific column.

In this lesson, you will learn how to measure index selectivity to decide whether a column is a good candidate for indexing.

What is Index Cardinality?

Cardinality refers to the number of unique values in a column. An index with high cardinality contains a high percentage of unique values relative to the total number of rows (e.g., a user_email column), while an index with low cardinality contains many repeated values (e.g., a gender or is_active status column).

When the database engine evaluates whether to use an index, it calculates the selectivity. Selectivity is defined as (Number of Distinct Values) / (Total Number of Rows). If the selectivity is very low, the database engine may conclude that scanning the entire table is faster than traversing a B-Tree index to find a needle in a haystack of identical values.

The Decision-Making Process

Imagine you have a users table with 1,000,000 rows.

Scenario A (High Cardinality): You index the user_id column. Because almost every value is unique, the query SELECT * FROM users WHERE user_id = 500 will return exactly one row. The index is extremely selective, and the engine will happily use it.
Scenario B (Low Cardinality): You index the gender column. With only two possible values (Male/Female), a query for WHERE gender = 'Male' will return 500,000 rows. The engine realizes it would have to jump back and forth between the index and the data pages for half the table. It is significantly faster to perform a sequential table scan.

To check the cardinality of your columns in MySQL/MariaDB, you can inspect the index statistics:

SHOW INDEX FROM users;

Look for the Cardinality column. If the number is low compared to the total row count, your index might be redundant. If you have an index that isn't providing a "shortcut" for the engine, you are simply wasting disk space and adding overhead to INSERT and UPDATE operations, as the engine must keep that low-value index updated.

Key Takeaway: Only index columns where the high ratio of unique values allows the query optimizer to filter out the vast majority of rows efficiently.