Stop Creating Single-Column Indexes: Mastering MySQL Composite Indexes

The Cost of 'Index Everything'

A common reflex when a MySQL query slows down is to add an index to every column mentioned in the WHERE clause. If you are filtering by user_id and status, it feels intuitive to create two separate indexes. However, MySQL can typically only use one index per table per query. When you provide multiple single-column indexes, the optimizer must choose one and then scan the resulting rows to filter the rest—a process that becomes prohibitively expensive as your dataset grows.

The solution is the Composite Index (also known as a multi-column index). Instead of separate lists, a composite index creates a single sorted structure containing multiple fields, allowing MySQL to narrow down the search space across several dimensions simultaneously.

The Leftmost Prefix Rule

The most critical concept in composite indexing is the Leftmost Prefix Rule. A B-Tree index stores data in a sorted hierarchy based on the order of columns defined in the index. If you define an index on (last_name, first_name), MySQL sorts the data by last name first, and then sorts entries with the same last name by first name.

• Supported: Queries filtering by last_name.
• Supported: Queries filtering by last_name AND first_name.
• Unsupported: Queries filtering only by first_name.

Because the index is not sorted by first name independently, the optimizer cannot jump to a specific first name without first knowing the last name. This makes the order of columns in your index definition the most important decision you will make during schema design.

Optimizing for Selectivity and Covering

To get the most out of a composite index, prioritize selectivity. A selective column is one with a high percentage of unique values (e.g., email is more selective than gender). Placing the most selective column first allows the B-Tree to discard the largest amount of irrelevant data as quickly as possible.

Furthermore, you can achieve a Covering Index. This happens when every column requested in your SELECT statement is included in the index itself. In this scenario, MySQL reads the data directly from the index B-Tree and never touches the actual table rows (the clustered index), eliminating expensive disk I/O.

Worked Example: Optimizing an Order Lookup

Consider a table orders with millions of rows. We frequently run a query to find active orders for a specific customer within a date range.

SELECT order_id, order_date FROM orders 
WHERE customer_id = 12345 AND status = 'active' AND order_date > '2023-01-01';

Creating three separate indexes would be inefficient. Instead, we implement a composite index. Run this command as a user with ALTER permissions on the database:

ALTER TABLE orders ADD INDEX idx_cust_status_date (customer_id, status, order_date);

Why this order?

1. customer_id: Highly selective; narrows millions of rows down to a few dozen.
2. status: Low selectivity, but used as an equality check to further refine the set.
3. order_date: A range filter. Range filters must come last in the index because once a range is encountered, subsequent columns in the index cannot be used for filtering.

Verifying the Result

To verify that MySQL is using the index correctly, prepend EXPLAIN to your query:

EXPLAIN SELECT order_id, order_date FROM orders 
WHERE customer_id = 12345 AND status = 'active' AND order_date > '2023-01-01';

Check the key column to ensure idx_cust_status_date is selected. Look at the Extra column; if it says Using index, you have successfully created a covering index.

Trade-offs and Limitations

Composite indexes are not a silver bullet. Every index you add increases the overhead for INSERT, UPDATE, and DELETE operations because MySQL must maintain the B-Tree structure during every write. Over-indexing can lead to "index bloat," where the indexes consume more disk space than the actual data.

Additionally, be wary of SARGability (Search ARGumentable). If you wrap an indexed column in a function, MySQL cannot use the index. For example, WHERE YEAR(order_date) = 2023 will ignore the index on order_date. Always use raw column comparisons (e.g., WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01').

Actionable Closing

Before adding your next index, map your most frequent WHERE clauses. Identify the columns used together and order them from most selective to least selective, placing range filters at the end. Use EXPLAIN to confirm the optimizer is behaving as expected, and remove any redundant single-column indexes that are now covered by your new composite index.