MySQL Composite Index Ordering: Fixing Hidden Full Table Scans

A common cause of database degradation under load is incorrect column ordering in composite indexes. Creating an index across multiple columns does not guarantee optimal query performance unless the index structure mirrors the query execution path.

The Leftmost Prefix Rule

MySQL composite indexes use B-Tree structures where entries are sorted sequentially based on columns from left to right. An index defined as (status, created_at, user_id) is ordered primarily by status, secondarily by created_at, and finally by user_id.

-- OPTIMAL: Matches the index leftmost prefix
SELECT * FROM orders 
WHERE status = 'completed' AND created_at >= 1700000000 
ORDER BY created_at DESC;

-- SUB-OPTIMAL: Bypasses the first column, triggering an index scan or full table scan
SELECT * FROM orders 
WHERE created_at >= 1700000000 AND user_id = 45;

Determining Optimal Column Order

Follow these rules when indexing multiple columns:

• Equality Columns First: Place columns filtered with exact equality (=) at the start of the index.
• Range Columns Second: Place range conditions (>, <, BETWEEN, LIKE) after equality columns. MySQL stops using composite index key parts after the first range condition.
• Sort Order Alignment: Align the index order with GROUP BY or ORDER BY clauses to prevent expensive Using filesort operations.

Analyzing Query Performance with EXPLAIN

Use EXPLAIN ANALYZE to inspect how MySQL processes a query:

EXPLAIN SELECT id FROM orders WHERE status = 'active' ORDER BY id DESC LIMIT 10;

Look for Using index in the Extra output column. This indicates a Covering Index, meaning MySQL retrieves data directly from the B-Tree without reading full rows from disk.