What Is Index Cardinality?
Author: Domeny.ForSale editorial team Published
In brief: Index cardinality is the number of distinct key values stored in an index, often reported by the database as an estimate. It helps the optimizer assess how many rows may match a query condition and whether using the index is worthwhile. High cardinality generally helps when looking up specific records, but does not guarantee faster performance on its own.
What index cardinality actually means
An index is an additional data structure that makes it easier to find rows without scanning the entire table. Its key consists of the value of one column or a set of values from several columns. Cardinality describes how many distinct keys of this kind occur in the data, not the total number of entries in the index.
Suppose the customers table has 100,000 rows. If every customer has a different identifier, the cardinality of the index on the id column is 100,000. If the status column contains only the values active, inactive and blocked, its cardinality is 3.
The number shown in administrative tools is not always an exact count of the data. The engine may calculate it from a sample and store it in statistics: information about table contents used to plan queries. Check the documentation for the specific engine to understand how NULL values are interpreted and how cardinality is presented.
Cardinality and query selectivity
Cardinality is a property of the data in an index. The selectivity of a condition, by contrast, describes how much that condition narrows the result set. In practice, a condition that selects a small portion of a table is called highly selective; when comparing numerical measures, check their definitions, because conventions can differ.
When searching for one unique identifier, the database may find a single row. For the condition status = 'active', it may need thousands or tens of thousands of rows. In the latter case, reading the index and then retrieving records from the table may cost more than a full table scan.
The number of distinct values alone does not tell you how often each one occurs. If 99,000 customers are active and only 20 are blocked, two conditions on the same column may differ greatly in how useful the index is for them. The cardinality is still 3, but the data distribution is uneven.
For this reason, the optimizer—the part of the engine that chooses how to execute a query—may also use other statistics, such as histograms describing the distribution of values. Their availability and use depend on the database.
Understanding composite index cardinality
A composite index covers more than one column. For an index on (country, city), what matters is the number of distinct pairs, not just the number of countries or cities considered separately.
For example, a table may contain 10 countries and 500 distinct country–city pairs. The cardinality of the first part of the key is then 10, while the cardinality of the entire key is 500. You should not automatically multiply the number of distinct countries by the number of distinct cities, because not every possible combination necessarily occurs in the table.
Some tools show separate estimates for successive leading portions of the index. In MySQL, the Cardinality field in the output of SHOW INDEX should be interpreted together with the column position specified by Seq_in_index: the estimate applies to the index prefix ending at that position.
The order of columns also affects how the index can be used. For a typical B-tree index, an index with an ordered tree structure, (country, city) naturally supports searches by country and by country and city. Searching by city alone may require a different access method; the available optimizations depend on the engine.
Why high cardinality is not enough
An index on a nearly unique column may be a good choice for comparison with a specific value, but less useful for a broad range. A query retrieving most records by date may not use the index, even if the dates have many distinct values.
How the condition is written also matters. Applying a function to an indexed column, such as converting a date to just its year, may make it harder to use a regular index. Check whether the specific engine allows a function-based index or an appropriate rewrite of the query.
An index can also help with sorting, joining tables, or reading data without accessing the table if it contains all the required columns. Low cardinality is therefore not, in itself, a reason to remove an index. You also need to account for its cost: the space it occupies and the additional work involved in writing and updating data.
How to check whether an index helps
Evaluate an index using an actual query, not just the number shown in the administration panel:
- Choose a typical query and representative parameter values, including both common and rare values.
- Check the execution plan using
EXPLAINor the equivalent in your engine. - Look at the chosen index, the estimated number of rows, and the method used to access the table.
- If it is safe to do so, compare the estimates with actual execution. Remember that commands that analyze execution may run the query.
- If there are large discrepancies, check whether the statistics are up to date and consult the database documentation for the rules governing how they are refreshed.
After a bulk import, record deletion, or a change in the distribution of values, statistics may no longer reflect the current data. Before adding another index, check the plan for the problematic query and the state of the statistics—this will help distinguish a missing suitable index from an incorrect cost estimate.
See also: What Is FireFox?
Read also
