MySQL Composite Indexes: Leftmost-Prefix Rule and Column Ordering for InnoDB

The Problem: Queries Ignore Your Index

You add a composite index on (status, created_at) expecting faster lookups. Then a query filtering only on created_at still scans the whole table. The reason is the leftmost-prefix rule: an InnoDB composite index can only be used when the query references a contiguous prefix of the index columns, starting from the first column.

How the Leftmost-Prefix Rule Works

InnoDB stores secondary index entries in B-tree order by the declared column sequence. For INDEX(status, created_at), the tree is sorted first by status, then by created_at within each status value. This structure lets MySQL:

• Seek directly to a specific status value
• Seek to a status and then range-scan created_at within that status
• Scan all rows in status order (useful for ORDER BY status)

It cannot efficiently jump to a created_at range across all statuses because those values are scattered across the tree.

Worked Example: Orders Table

Consider an orders table with a primary key id and columns status (enum: 'open', 'shipped', 'cancelled') and created_at (datetime).

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  status ENUM('open','shipped','cancelled') NOT NULL,
  created_at DATETIME NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

Add a composite index to support the common dashboard query: "show open orders newest first."

CREATE INDEX idx_orders_status_date ON orders(status, created_at);

Run this where you have ALTER privilege on the database (typically a DBA or app migration role). The index builds online in MySQL 8.0+; on older versions it may lock writes. Verify with:

SHOW INDEX FROM orders WHERE Key_name = 'idx_orders_status_date';

Expected output shows Seq_in_index 1 for status, 2 for created_at.

Queries That Use This Index

Query Pattern	Index Use	Why
WHERE status='open'	Yes (ref)	Leftmost column equality
WHERE status='open' AND created_at > '2024-01-01'	Yes (range)	Equality on first, range on second
WHERE status='open' ORDER BY created_at DESC	Yes, no filesort	Index order matches ORDER BY
WHERE created_at > '2024-01-01'	No (full scan)	Missing leftmost column
WHERE status IN ('open','shipped') ORDER BY created_at	Partial	Multiple equality ranges; may still avoid filesort per status

Covering Indexes and the Implicit Primary Key

InnoDB appends the primary key columns to every secondary index entry. idx_orders_status_date effectively stores (status, created_at, id). If a query only needs those three columns, MySQL can answer entirely from the index—covering index—shown as Using index in EXPLAIN Extra column. This avoids the primary-key lookup (clustered index read) and cuts I/O dramatically on large tables.

Example covering query:

SELECT id, status, created_at
FROM orders
WHERE status='open'
ORDER BY created_at DESC
LIMIT 20;

No table access needed. Adding customer_id to the SELECT would break covering unless you extend the index to (status, created_at, customer_id)—but that widens the index and slows writes.

Column Ordering Strategy

Put columns used in equality conditions first, then columns used in range conditions or ORDER BY. Once a range condition appears, MySQL generally cannot use later index columns for sorting or further lookups.

Good: INDEX(status, created_at) for WHERE status='x' AND created_at > 'y'

Bad: INDEX(created_at, status) for the same query—range on first column prevents using status for filtering within the index.

If you have both WHERE status='open' AND customer_id=123 and WHERE status='open' ORDER BY created_at, choose based on selectivity and frequency. A single composite index cannot optimally serve both patterns if customer_id and created_at are both needed after status.

Verifying Index Usage with EXPLAIN

Run EXPLAIN (or EXPLAIN ANALYZE on MySQL 8.0.18+) on your actual query before and after adding the index. Compare these fields:

• key — which index was chosen
• rows — estimated rows examined (should drop sharply)
• Extra — look for Using index (covering), Using where (post-filter), Using filesort (sort not satisfied by index)

EXPLAIN SELECT * FROM orders WHERE status='open' ORDER BY created_at DESC LIMIT 20;

Run this in any MySQL client with SELECT privilege. No special permissions needed. If key shows idx_orders_status_date and Extra lacks Using filesort, the index works as intended.

Common Mistakes and Limits

1. Redundant Single-Column Indexes

After creating (status, created_at), an index on (status) alone is redundant—the composite index already serves status-only lookups. Drop it to save write overhead and storage.

2. Functions on Indexed Columns

WHERE DATE(created_at) = '2024-01-15' prevents index use. Rewrite as WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'.

3. Type Coercion

Comparing a VARCHAR column to a numeric literal triggers implicit conversion and blocks the index. Ensure literal types match column definitions.

4. Over-Indexing Write-Heavy Tables

Every INSERT/UPDATE touching indexed columns must modify each secondary index. On a table with 10 indexes, a single row insert performs 10 B-tree writes. Measure write latency on a staging copy with production-like load before adding indexes to high-throughput tables.

5. Optimizer Changes Across Versions

Cost-based choices shift with table size, statistics freshness (ANALYZE TABLE), and MySQL version. An index chosen on 8.0.30 may be skipped on 8.0.36. Re-verify after upgrades.

Practical Verification Checklist

1. Run EXPLAIN on target queries before adding the index; save output.
2. Add the composite index during a low-traffic window (or use online DDL on 8.0+).
3. Run EXPLAIN again; confirm key matches your index and rows drops.
4. On MySQL 8.0.18+, run EXPLAIN ANALYZE to compare estimated vs actual rows and time.
5. Check information_schema.STATISTICS for cardinality: SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='your_db' AND TABLE_NAME='orders';
6. Benchmark write throughput (INSERT/UPDATE) on staging with the new index for at least 10 minutes of representative load.

When to Consider Index Hints

FORCE INDEX(idx_orders_status_date) can override a misguided optimizer choice, but it hardcodes a plan. If data distribution changes (e.g., status='open' grows from 1% to 50% of rows), the forced index may become slower than a full scan. Use hints only as a temporary workaround while you investigate statistics or query rewrite.