Composite Indexes in MySQL: Column Order Matters

One index for several columns

A composite index contains more than one column. MySQL organizes it by the first column, then by the second within each group of first-column values, and so on.

CREATE INDEX idx_pesanan_status_tanggal
    ON pesanan (status, dibuat_pada);

The leftmost-prefix rule

A composite index can support queries filtering on a prefix of its columns, starting from the left.

-- Memakai indeks: menyaring status, lalu terurut oleh dibuat_pada
SELECT * FROM pesanan
WHERE status = 'selesai'
ORDER BY dibuat_pada DESC;

-- Tidak memakai indeks di atas secara efektif
SELECT * FROM pesanan
WHERE dibuat_pada > '2026-01-01';

The second query filters only on dibuat_pada without including status, so it cannot use that index's leftmost prefix in the same way.

Choose the column order

A common starting point is to put equality filters (=) before range or ordering columns (>, <, ORDER BY). Consider selectivity together with actual query patterns rather than assuming one order suits every workload.

Inspect the index actually used

EXPLAIN SELECT * FROM pesanan
WHERE status = 'selesai'
ORDER BY dibuat_pada DESC;

The key column in EXPLAIN identifies the index selected by the optimizer. possible_keys lists available candidates.

Exercise

Create two indexes with reversed column orders on the same table. Compare EXPLAIN output for two queries using different filtering patterns.