Composite Indexes — MySQL

MySQL Composite Index

A composite index (also called a multi-column index) covers two or more columns in a single index structure. Done correctly, a composite index can replace several single-column indexes and dramatically reduce the work MySQL has to do to answer common queries. The key insight — and the most common mistake — is that column order matters enormously.

Defining a Composite Index

SQL

-- Create a composite index at table creation
CREATE TABLE orders (
  id         INT            NOT NULL AUTO_INCREMENT,
  user_id    INT            NOT NULL,
  status     VARCHAR(20)    NOT NULL,
  created_at DATETIME       NOT NULL,
  total      DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_user_status_date (user_id, status, created_at)
);

-- Add to an existing table
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- Check the index
SHOW INDEX FROM orders;

The Leftmost Prefix Rule

MySQL can only use a composite index starting from its leftmost column. An index on (A, B, C) can serve queries that filter on:

A alone
A + B
A + B + C

But it cannot help queries that filter only on B, only on C, or only on B + C (without A). The EXPLAIN output's key column will be NULL when the index is not used.

SQL

-- Index: (user_id, status, created_at)

-- USES the index (leftmost column present)
EXPLAIN SELECT * FROM orders WHERE user_id = 42G
-- key: idx_user_status_date

-- USES the index (leftmost two columns)
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'G
-- key: idx_user_status_date

-- USES all three columns
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid' AND created_at > '2024-01-01'G
-- key: idx_user_status_date

-- Does NOT use the composite index (missing leftmost column)
EXPLAIN SELECT * FROM orders WHERE status = 'paid'G
-- key: NULL

-- Does NOT use it (only rightmost column, no prefix)
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01'G
-- key: NULL

key_len in EXPLAIN — How Many Columns Are Used

The key_len column in EXPLAIN shows how many bytes of the index are used. You can reverse-engineer this to determine how many columns of a composite index are actually being applied to the query.

SQL

-- Index: (user_id INT=4 bytes, status VARCHAR(20) utf8mb4 = 83 bytes, created_at DATETIME=5 bytes)

-- Only user_id used
EXPLAIN SELECT * FROM orders WHERE user_id = 42G
-- key_len: 4  (4 bytes for INT)

-- user_id + status used
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'G
-- key_len: 87  (4 + 83)

-- All three columns used
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid' AND created_at > '2024-01-01'G
-- key_len: 92  (4 + 83 + 5)

Note

A VARCHAR(20) with utf8mb4 and NULL allowed uses 4*20 + 2 (length prefix) + 1 (NULL flag) = 83 bytes in the index. NOT NULL columns omit the 1-byte NULL flag. Use key_len to confirm all intended index columns are active.

Column Order Strategy: Equality First, Range Last

The golden rule for composite index column order:

Equality columns first — columns filtered with = or IN.
Range columns last — columns filtered with >, <, BETWEEN, LIKE prefix.

Once MySQL encounters a range condition in an index, it can no longer use subsequent columns in that same index for filtering.

SQL

-- Query: user_id = 42 AND status = 'paid' AND created_at > '2024-01-01'

-- GOOD index order: equality columns first, range column last
CREATE INDEX idx_good ON orders (user_id, status, created_at);
-- MySQL uses all three columns: filters user_id and status with equality,
-- then uses created_at for the range scan

-- BAD index order: range column in the middle
CREATE INDEX idx_bad ON orders (user_id, created_at, status);
-- MySQL uses user_id and created_at for the scan, but CANNOT use status
-- after the range column. Status filter becomes a post-index check (slower)

Covering Index

A covering index includes all the columns a query needs — both for WHERE filtering and for the SELECT list. MySQL can answer the query entirely from the index without reading the main table rows. This eliminates a second B-tree lookup and can cut query time by 50–90% on large tables.

Look for "Using index" in the EXPLAIN Extra column — that means a covering index is in effect.

SQL

-- Query that accesses user_id, status, and total
SELECT user_id, status, total FROM orders WHERE user_id = 42;

-- A covering index includes ALL columns needed by the query
CREATE INDEX idx_covering ON orders (user_id, status, total);
-- Now MySQL answers this query purely from the index

-- Verify with EXPLAIN
EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42G
-- Extra: Using index  <- covering index in effect, no table access

-- Compare: non-covering index (must read full row)
EXPLAIN SELECT * FROM orders WHERE user_id = 42G
-- Extra: NULL  <- must hit the clustered index for columns not in the index

Tip

A covering index that includes the SELECT list columns is sometimes called an "index-only scan." It is one of the most powerful query optimisation techniques available — aim for covering indexes on your most-frequent queries.

Composite Index and ORDER BY

A composite index can also eliminate the filesort operation for ORDER BY if the sort columns appear in the index in the same order and direction (ASC/DESC) as the query.

SQL

-- Index: (user_id, created_at)

-- Uses the index for both WHERE and ORDER BY — no filesort
EXPLAIN SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESCG
-- Extra: Using index condition  (no "Using filesort")

-- This requires a filesort (status not in index)
EXPLAIN SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY statusG
-- Extra: Using filesort  <- must sort in memory/on disk

-- Index covering WHERE + ORDER BY + SELECT (ideal)
CREATE INDEX idx_user_date_total ON orders (user_id, created_at, total);

EXPLAIN SELECT total FROM orders
WHERE user_id = 42
ORDER BY created_at DESCG
-- Extra: Using index  (covering + order: perfect)

Index Skip Scan (MySQL 8.0)

MySQL 8.0 introduced Index Skip Scan, which allows the optimizer to use a composite index even when the leading column is not in the WHERE clause — under specific conditions. Skip scan works by iterating over the distinct values of the leading column and applying the non-leading predicate to each range. It is most effective when the leading column has low cardinality (few distinct values).

SQL

-- Index: (status, created_at) where status has only ~5 distinct values

-- Without skip scan: status not in WHERE, index normally unusable
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01'G
-- type: range (or index if skip scan kicks in)
-- Extra: Using index for skip scan  <- MySQL 8.0 optimisation

-- Skip scan effectively runs:
-- SELECT * WHERE status='pending'   AND created_at > '2024-01-01'
-- UNION ALL
-- SELECT * WHERE status='paid'      AND created_at > '2024-01-01'
-- ... for each distinct status value

-- Force skip scan or prevent it
SELECT /*+ SKIP_SCAN(orders idx_status_date) */ * FROM orders
WHERE created_at > '2024-01-01';

Note

Skip scan is automatic when the optimizer determines it is cheaper than a full scan. It works best when the leading column has low cardinality (under 100 distinct values). High-cardinality leading columns make skip scan more expensive than a table scan.

Composite Index vs Multiple Single-Column Indexes

When a query filters on multiple columns, MySQL can sometimes combine two single-column indexes using Index Merge, but this is generally less efficient than a single composite index covering those columns.

Scenario	Composite Index	Multiple Single Indexes
Two equality filters	One efficient lookup	Index merge possible but slower
Equality + range filter	Efficient if order is right	Index merge usually suboptimal
Covering query	Possible (include all cols)	Cannot cover across indexes
ORDER BY support	Yes (right column order)	No (cannot merge for sorting)
Write overhead	One index to maintain	Two separate indexes to maintain

SQL

-- Prefer this composite index:
CREATE INDEX idx_user_status ON orders (user_id, status);

-- Over these two separate indexes:
CREATE INDEX idx_user   ON orders (user_id);
CREATE INDEX idx_status ON orders (status);

-- Check if MySQL is doing an index merge (suboptimal):
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'G
-- If Extra shows "Using intersect(idx_user,idx_status)"
-- -> replace with a composite index

Redundant Indexes

A single-column index on column A is redundant if a composite index starting with A already exists, because the composite index can serve all queries the single-column index can. Redundant indexes waste write overhead — every INSERT/UPDATE/DELETE must maintain all indexes.

SQL

-- Composite index on (user_id, status)
CREATE INDEX idx_user_status ON orders (user_id, status);

-- This single-column index is NOW REDUNDANT:
CREATE INDEX idx_user ON orders (user_id);  -- covered by idx_user_status already

-- Find redundant indexes (MySQL 8.0 sys schema)
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'myapp';

Warning

Redundant indexes do not improve query performance but they do slow down every write operation. Review and drop them regularly, especially after schema refactoring.

Practical E-Commerce Query Optimisation

Scenario: an order history page loads the 20 most recent paid orders for a user, showing the order total and status. Before adding the right composite index, EXPLAIN shows a full scan.

SQL

-- BEFORE: no composite index
EXPLAIN SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20G
-- type:  ALL          <- full table scan!
-- key:   NULL
-- rows:  500000       <- scanning all rows
-- Extra: Using where; Using filesort

-- CREATE the right composite index
CREATE INDEX idx_orders_user_status_date
  ON orders (user_id, status, created_at);

-- AFTER: excellent plan
EXPLAIN SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20G
-- type:  range
-- key:   idx_orders_user_status_date
-- key_len: full (all 3 columns used)
-- rows:  ~25            <- tiny fraction of table
-- Extra: Using index condition  (no filesort!)

Design Guidelines

Put the highest-cardinality equality column first (e.g., user_id before status).
Equality (=, IN) columns come before range (>, <, BETWEEN) columns.
Include ORDER BY columns at the end of the index after WHERE columns.
Add SELECT columns to make the index covering when possible.
Avoid indexes wider than 4-5 columns — diminishing returns and high write cost.
One well-designed composite index often outperforms three single-column indexes.
Use EXPLAIN to verify your index is actually being used as intended.
Check key_len in EXPLAIN — it tells you exactly how many index columns are active.
Use sys.schema_redundant_indexes to find and drop redundant indexes periodically.