In this lesson, you will learn how to arrange columns within a composite index to drastically reduce disk I/O and accelerate query execution.
The Power of Multiple Columns
Up until now, you have likely focused on indexing single columns, such as a primary key or a specific foreign key. However, analytical queries often filter by multiple criteria simultaneously—for example, looking for a specific customer_id within a specific region_id. A composite index allows the database engine to search across multiple columns as a single unit, avoiding the need to scan entire tables.
The "Left-Prefix" Rule
The secret to effective composite indexing is the Left-Prefix rule. When you create an index on (region_id, customer_id), the database organizes the data first by region_id and then by customer_id within those regions.
If your query filters by region_id, the index works perfectly. If your query filters by both region_id and customer_id, the index is even more efficient. However, if your query filters only by customer_id, the database cannot use this specific index effectively because the data isn't sorted by customer first.
Practical Implementation
Consider a large fact table where you frequently analyze sales by store and date. Instead of having two separate indexes, you can create one composite index to handle the most common filter combinations:
CREATE INDEX idx_store_date
ON fact_sales (store_id, sale_date);
When a user runs a query like the one below, the database engine jumps directly to the relevant store_id block and then immediately finds the specific range of sale_date values, bypassing thousands of irrelevant rows on the disk:
SELECT revenue
FROM fact_sales
WHERE store_id = 50
AND sale_date BETWEEN '2023-01-01' AND '2023-01-31';
By aligning the order of columns in your index with the order of columns in your WHERE clauses and JOIN conditions, you prevent the database from performing expensive "full table scans." You are essentially giving the database a sorted roadmap that matches the exact questions your users are asking most often.
Key Takeaway: Design your composite indexes by placing the most frequently filtered, high-cardinality columns at the start of the index definition to maximize the efficiency of disk-based data retrieval.