In this lesson, you will learn how the "variety" of your data determines whether an index actually makes your queries faster.
Understanding Index Selectivity
When you create an index on a table column, you are essentially building a map that tells the database engine exactly where to find specific rows. However, not all maps are equally useful. Selectivity refers to how unique or "distinct" the values are within a column.
Think of it like looking for a person in a crowd. If you are looking for "John Smith" (a common name), an index on a "First Name" column is not very helpful because thousands of people might share that name. You still have to check every one of them to find the right person. This is low selectivity.
If, however, you are looking for a unique "Social Security Number," there is only one person with that number. The index leads you straight to the target in a single step. This is high selectivity.
Why it Matters
The database engine checks selectivity before using an index. If you index a column with very little variety—like a Gender column containing only 'M' or 'F'—the database often ignores the index entirely. It realizes that scanning the entire table is actually faster than jumping back and forth between the table and a low-selectivity index.
Consider this table:
CREATE TABLE Employees (
employee_id INT,
status VARCHAR(10) -- Values are 'Active' or 'Inactive'
);
If 99% of your employees are 'Active', an index on status is inefficient. If you search for 'Inactive' employees, the database might use the index, but if you search for 'Active' ones, it will likely perform a full table scan because the index isn't "selective" enough to narrow down the results effectively.
Choosing Columns
When deciding which columns to index, always prioritize those with a high ratio of unique values compared to the total number of rows. Primary keys and unique identifiers are your best candidates, while columns with repetitive categories are usually poor candidates for indexing.
Key Takeaway: High-selectivity columns (those with many unique values) make for powerful indexes, while low-selectivity columns provide little to no performance benefit for your queries.