Mastering Composite Index Selectivity
In this lesson, you will learn how to arrange columns within a multi-column index to maximize performance by applying the 80/20 rule of selectivity.
The Power of "Filtering Power"
When you build a composite index (an index on two or more columns), the order of those columns is critical. Because B-Tree indexes rely on the Left-Prefix Match Requirement, the database reads columns from left to right. To make your index as efficient as possible, you want the most "selective" column—the one that filters out the largest percentage of data—to appear first.
Think of selectivity as the "filtering power" of a column. If you have a Users table with 1,000,000 rows, a column like Gender (only 2 distinct values) has very low selectivity. A column like Email (1,000,000 distinct values) has very high selectivity.
Applying the 80/20 Rule
The 80/20 rule here suggests that you should place the column that narrows down your search space the most at the beginning of your index definition. By placing high-selectivity columns first, the B-Tree can discard the vast majority of non-matching records immediately, drastically reducing the number of leaf nodes the database engine must traverse.
Consider a query looking for a specific status in a high-traffic table:
SELECT * FROM Orders
WHERE Status = 'Shipped'
AND CustomerID = 54321;
If Status has only 3 values and CustomerID has 50,000 unique values, an index defined as (Status, CustomerID) is suboptimal. The database must navigate the index to find all 'Shipped' orders first. If you instead define the index as (CustomerID, Status), the database immediately jumps to the unique CustomerID, and then filters the small subset of rows for that specific user by Status. This requires significantly less I/O.
Efficiency in Practice
When designing your indexes, calculate the cardinality (the number of unique values). Always prioritize the columns that provide the most granularity early in the index structure. This minimizes the search depth and ensures the most specific data path is used first.
Key Takeaway: Always place the most selective columns—those that filter the most data—at the beginning of your composite index to prune the search space as early as possible.