Composite Index

Intermediate

Indexes

A composite index on (a, b, c) supports queries on (a), (a, b), and (a, b, c) due to leftmost prefix matching; column order matters — put high-cardinality, equality columns first.

Overview

A **composite (multi-column) index** covers queries on a combination of columns more efficiently than separate single-column indexes. InnoDB's B-tree composite indexes follow **leftmost prefix matching**: an index on `(a, b, c)` can satisfy queries on `(a)`, `(a, b)`, and `(a, b, c)` — but NOT on `(b)` or `(b, c)` alone. Column order in the index definition is critical. The general rule: put **equality filter columns first** (highest cardinality), then **range/sort columns last**. A well-designed composite index can also be a **covering index** — if all queried columns are in the index, MySQL satisfies the query without touching the actual data rows ("Using index" in EXPLAIN).

Leftmost Prefix Rule

For `INDEX(status, created_at, user_id)`: queries filtering on `status` alone, `status + created_at`, or all three use the index. Queries filtering only on `created_at` or only `user_id` cannot use this index (without a full scan or separate index). The optimizer stops using the index at the first column that has no equality filter — a range predicate on column 2 means column 3 is not usable.

MySQL — leftmost prefix matching

-- Index: (status, created_at, user_id)
CREATE INDEX idx_orders_status_date_user
    ON orders(status, created_at, user_id);

-- Uses index (status equality + created_at range = both usable)
EXPLAIN SELECT * FROM orders
WHERE status = 'PAID' AND created_at > '2025-01-01';
-- → type: range, key: idx_orders_status_date_user

-- CANNOT use index — skips leftmost column
EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';
-- → type: ALL (full table scan)

-- Uses only the first column (range on 2nd → 3rd column unused in range scan)
EXPLAIN SELECT * FROM orders
WHERE status = 'PAID' AND created_at > '2025-01-01' AND user_id = 42;
-- status: range scan; created_at used in range; user_id NOT used for range
-- (user_id may still be used for filtering via index condition pushdown)

Column Order: Equality Before Range

Place **equality filter columns** before **range/ORDER BY columns**. This maximises how many columns contribute to narrowing the B-tree scan. Example: for `WHERE user_id = ? AND created_at BETWEEN ? AND ?`, the correct index is `(user_id, created_at)` — `user_id` equality pins a slice of the B-tree, then `created_at` range scans within that slice. Reversed order `(created_at, user_id)` would scan all rows in the date range regardless of user.

MySQL — column order and covering index

-- BAD: range column first — date range scans ALL users
CREATE INDEX idx_bad ON orders(created_at, user_id);
-- Query: WHERE user_id = 1 AND created_at BETWEEN '2025-01-01' AND '2025-12-31'
-- Scans full date range, then filters user_id → many rows read

-- GOOD: equality column first — pins user, then scans date range
CREATE INDEX idx_good ON orders(user_id, created_at);
-- B-tree: navigates directly to user_id=1 subtree, then range-scans created_at
-- → far fewer rows read

-- Covering index example — all SELECT columns in index
CREATE INDEX idx_covering ON orders(user_id, created_at, status, total);
SELECT status, total FROM orders
WHERE user_id = 1 AND created_at > '2025-01-01';
-- → EXPLAIN shows "Using index" (no row lookup needed)

Index Merging and When NOT to Use Composite Indexes

MySQL can merge two single-column indexes (`index_merge`), but this is usually slower than a well-designed composite index. Index merge appears in EXPLAIN as `type: index_merge`. Avoid composite indexes with low-cardinality prefix columns (e.g., a boolean `active` column as the first column — only 2 distinct values means 50% of rows are scanned after the first key lookup). Also avoid over-indexing: every write must update all indexes.

MySQL — index merge pitfalls and diagnostics

-- Index merge — MySQL uses two single-column indexes and merges
-- EXPLAIN: type=index_merge, Extra=Using union(idx_status,idx_user)
-- This is usually worse than a composite index

EXPLAIN SELECT * FROM orders
WHERE status = 'PAID' OR user_id = 42;
-- Optimizer may merge idx_status + idx_user but a composite cannot help OR queries

-- Low-cardinality first column — avoid
CREATE INDEX idx_bad2 ON users(is_active, email);
-- is_active=TRUE selects 80% of users — nearly useless as the first key
-- Correct: put email (high cardinality) first
CREATE INDEX idx_good2 ON users(email, is_active);

-- Useful diagnostics
SHOW INDEX FROM orders;              -- see cardinality estimates
EXPLAIN FORMAT=JSON SELECT ...;     -- full access plan with cost estimates
SELECT * FROM sys.schema_unused_indexes;  -- find unused indexes to drop

Key Points to Remember

1Composite index (a, b, c) supports queries on (a), (a, b), (a, b, c) via leftmost prefix
2Queries filtering only on b or c alone cannot use the (a, b, c) index
3Put equality filter columns first, range/ORDER BY columns last
4A covering index includes all queried columns — no row lookup needed ("Using index")
5Low-cardinality first columns (e.g. boolean flag) make the index nearly useless
6Index merge (two single-column indexes) is usually slower than one well-designed composite

Interview Questions

Given an index on (status, created_at), which queries will use it?

MediumAmazon

Why should equality filter columns come before range columns in a composite index?

MediumGoogle

What is a covering index and how does it differ from a regular composite index?

MediumOracle

Why is a boolean column a poor choice as the first column of a composite index?

EasyBooking.com

What is index merging and when does MySQL use it?

HardPercona

Ask Aria about Composite Index

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

B-Tree Index

Covering Index