What is Index Cardinality in MySQL: Explanation and Examples

In MySQL, index cardinality refers to the number of unique values in an index. It helps MySQL estimate how selective an index is, which influences query optimization and performance.

How It Works

Think of index cardinality like the variety of items in a basket. If the basket has many different items, it has high cardinality; if most items are the same, it has low cardinality. In MySQL, cardinality measures how many unique values an index column holds compared to the total rows.

This matters because MySQL uses cardinality to decide if using an index will speed up a query. High cardinality means the index can quickly narrow down results, like finding a unique book by its ISBN. Low cardinality means many rows share the same value, so the index might not help much, like searching for all books by the same author.

Example

This example shows how to check index cardinality for a table with an index on a column.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  department VARCHAR(50),
  INDEX dept_index (department)
);

INSERT INTO employees (id, department) VALUES
(1, 'Sales'),
(2, 'Sales'),
(3, 'HR'),
(4, 'IT'),
(5, 'IT'),
(6, 'IT');

SHOW INDEX FROM employees WHERE Key_name = 'dept_index';

Output

Table: employees
Non_unique: 1
Key_name: dept_index
Seq_in_index: 1
Column_name: department
Cardinality: 3
Index_type: BTREE

When to Use

Use index cardinality to understand how effective an index will be for your queries. High cardinality indexes are great for columns with many unique values, like user IDs or email addresses, because they help MySQL quickly find specific rows.

Low cardinality indexes, such as those on columns with few distinct values like gender or status flags, might not improve performance much and can sometimes slow down writes. Knowing cardinality helps you decide which columns to index for faster searches and which to avoid.

Key Points

• Index cardinality measures unique values in an index.
• High cardinality means more unique values, better for query speed.
• MySQL uses cardinality to choose the best index for queries.
• Low cardinality indexes may not improve performance significantly.
• Check cardinality with SHOW INDEX to optimize your database.

Key Takeaways

Index cardinality is the count of unique values in an index column.

High cardinality indexes help MySQL quickly find specific rows.

Low cardinality indexes may not improve query performance much.

Use SHOW INDEX to check cardinality and optimize indexing.

Cardinality helps MySQL decide the best index for query execution.