Skip to content
Tutorials Center
My learning

Topics

Lesson 44 of 75 ยท Indexes and Performance

Designing Composite Indexes

Design multi-column MySQL indexes: the leftmost prefix rule, equality before range, sorting from the index, prefix, descending, functional and invisible indexes.

Most real queries filter on more than one column: this customer, with this status, in this date range, newest first. One index per column does not help much, because MySQL generally uses one index per table in a query. A composite index (also called a multi-column or compound index) covers several columns at once and, when its columns are in the right order, answers the filter and the sort in a single pass.

The key word is order. The same two columns indexed as (a, b) and as (b, a) are two very different indexes. This lesson gives you the rules for choosing the order, the extra index types MySQL 8.0 offers (prefix, descending, functional and invisible), and a way to check each decision. The examples run live on SQLite and read its EXPLAIN QUERY PLAN; the MySQL versions of the same ideas are shown as read-only code.

Column order rules

Equality first Columns tested with = go at the front.
One range next BETWEEN, <, > or sort column follows.
Nothing after a range Later columns cannot seek.

Think of the index as a phone book sorted by several columns.

1 / 2

The phone book model

A composite index on (last_name, first_name) is a phone book: sorted by last name, and within each last name, sorted by first name. That picture explains the rules:

  • You can find everyone named "Chen" (the first column).
  • You can find "Chen, Bo" (both columns).
  • You cannot efficiently find everyone named "Bo" with no last name: those entries are scattered across the whole book.
  • You can find all last names starting with "Ch", and within that range the first names are no longer sorted globally.

This is the leftmost prefix rule. An index on (a, b, c) can seek on a, on a, b, or on a, b, c. It cannot seek on b alone, c alone or b, c. It can still be scanned in full by those queries, which is cheaper than scanning the table when it is smaller or covers the query, but that is not a seek.

SQL
ALTER TABLE orders ADD INDEX idx_customer_placed (customer_id, placed_at);

-- Uses the index to seek:
SELECT * FROM orders WHERE customer_id = 7;
SELECT * FROM orders WHERE customer_id = 7 AND placed_at >= '2025-06-01';

-- Cannot seek: the first column is missing.
SELECT * FROM orders WHERE placed_at >= '2025-06-01';

MySQL 8.0.13 and later can sometimes use a skip scan when the first column has very few distinct values (shown as Using index for skip scan in Extra). Treat it as a fallback, not a design: the leftmost columns your queries filter on should lead the index.

Here is the same rule in SQLite, which runs live:

SQL The leftmost prefix rule

Press Run (or Ctrl + Enter) to see the result here.

Plans 1 and 2 say SEARCH ... USING INDEX. Plan 3 says SCAN orders. In plan 2, the detail shows customer_id=? AND placed_at>?: both columns are used for the seek.

Equality first, then one range

Within a composite index, MySQL can use columns from left to right as long as they are tested with equality. The first column tested with a range (<, >, BETWEEN, LIKE 'abc%') can still seek, but columns after it can no longer narrow the seek, because within a range the later column is not sorted globally. (MySQL may still use them to filter index entries early without reading rows, shown as Using index condition, but that is checking, not seeking.)

So for the query "paid orders placed in June":

SQL
SELECT * FROM orders
WHERE status = 'paid'
  AND placed_at >= '2025-06-01' AND placed_at < '2025-07-01';

the equality column status must come before the range column placed_at: INDEX (status, placed_at). The reverse, (placed_at, status), can seek only on the date range and has to look at the status of every order in June.

SQL Equality before range

Press Run (or Ctrl + Enter) to see the result here.

The first plan seeks on status=? AND placed_at>? AND placed_at<?. The second seeks only on placed_at, and the status test becomes a filter on every entry in the date range. On a table with millions of rows in each month, that difference is large.

Order among equality columns matters less for a single query, since all of them seek. Choose by reuse: put the column that other queries also filter on alone at the front, so one index serves several queries. Putting the highest-cardinality column first is a popular rule of thumb, but query shape beats it: design from your WHERE clauses, not from cardinality alone.

Letting the index do the sorting

An index is stored in order, so a query can skip its sort if the ORDER BY follows the index order after the equality columns. This is the second reason for column order. For "this customer's orders, newest first":

SQL
SELECT * FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC
LIMIT 20;

With INDEX (customer_id, placed_at), MySQL seeks to customer 7 and reads that customer's entries already ordered by placed_at, backwards for DESC, and stops after 20 rows. Without the index it must find all of the customer's rows, sort them (Using filesort in EXPLAIN) and then keep 20. For LIMIT queries this can mean reading 20 rows instead of 20,000.

The sort is only free when the order matches the index, with the equality columns in front:

  • WHERE customer_id = 7 ORDER BY placed_at: free.
  • WHERE customer_id = 7 ORDER BY total: needs a sort, because total is not next in the index.
  • WHERE customer_id > 7 ORDER BY placed_at: needs a sort. The range on customer_id returns several customers, so placed_at is only ordered within each one.
SQL Sorting from the index

Press Run (or Ctrl + Enter) to see the result here.

Look for USE TEMP B-TREE FOR ORDER BY in plans 2 and 3. It is SQLite's version of MySQL's Using filesort.

Descending, prefix, functional and invisible indexes

Descending key parts. Before MySQL 8.0 you could write DESC in an index definition, but it was ignored. In 8.0 it is real, and it matters for mixed sort directions: ORDER BY customer_id ASC, placed_at DESC can only be read straight from an index declared as (customer_id ASC, placed_at DESC). A fully reversed order (everything DESC) works on an ascending index by reading it backwards.

SQL
ALTER TABLE orders ADD INDEX idx_cust_asc_placed_desc (customer_id ASC, placed_at DESC);

Prefix indexes. For long strings, INDEX (url(28)) indexes only the first 28 characters, keeping the index small. The trade-offs: a prefix index cannot be a covering index, and it cannot be used for ORDER BY or GROUP BY of the full value. Choose the shortest prefix that is nearly as selective as the whole column. Measure it:

SQL Choosing a prefix length

Press Run (or Ctrl + Enter) to see the result here.

The shared start https://shop.example/p/ is 23 characters, so short prefixes collapse every URL into one entry. At 28 characters all 700 values stay distinct, so url(28) is enough. In MySQL the same measurement is SELECT COUNT(DISTINCT LEFT(url, 28)) / COUNT(DISTINCT url) FROM pages;, and you want a ratio close to 1.

Functional indexes. MySQL 8.0.13 and later can index an expression, so a query with a function in the WHERE clause can still seek. Note the extra parentheses:

SQL
ALTER TABLE users ADD INDEX idx_email_lower ((LOWER(email)));

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

SQLite supports the same idea as an index on an expression, and it shows (<expr>=?) in the plan. We return to this in Query Optimization Techniques. Under the hood, MySQL implements it as a hidden virtual generated column, as covered in Generated Columns.

Invisible indexes. Dropping an index on a big table is risky, because if a query suddenly needs it, rebuilding takes hours. MySQL 8.0 lets you hide it first:

SQL
ALTER TABLE orders ALTER INDEX idx_customer_placed INVISIBLE;
-- watch for slow queries for a week ...
ALTER TABLE orders ALTER INDEX idx_customer_placed VISIBLE;   -- instant rollback
ALTER TABLE orders DROP INDEX idx_customer_placed;            -- or commit to removal

An invisible index is still maintained on every write, but the optimizer ignores it. You can test with it using SET SESSION optimizer_switch = 'use_invisible_indexes=on';. The primary key cannot be made invisible.

The ALTER TABLE ... ADD INDEX statements here are read-only on this site, because they run on a real MySQL server. The SQLite examples use CREATE INDEX, which works the same way for our purposes.

A design recipe

For each important query, in order:

  1. List the columns tested with equality. They go first.
  2. Add one column: the range column, or if there is none, the ORDER BY column that you want the index to deliver sorted.
  3. Optionally append columns the query selects to make the index covering.
  4. Check the result with EXPLAIN, then look for existing indexes that already start with the same columns. Extend an index instead of adding a near-duplicate, because (a) is redundant once (a, b) exists.

Remember that every index slows writes. Aim for a small number of well-designed composite indexes shaped by your real queries, not one index per column.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A table has INDEX (country, city, zip). Which query can seek on the index using city?

    The leftmost prefix rule: an index can seek on country, country+city, or country+city+zip. A query that skips country cannot seek (skip scan is only a fallback).
  2. 2. For WHERE status = 'paid' AND placed_at BETWEEN a AND b, which index order is best?

    The equality column goes first and the range column second, so both narrow the seek. With (placed_at, status), only the range narrows it.
  3. 3. With INDEX (customer_id, placed_at), which query still needs a sort step?

    total is not in the index, so the matching rows must be sorted. The other queries read the index in order, forwards or backwards.
  4. 4. Why make an index invisible before dropping it?

    An invisible index is still maintained but unused by the optimizer. Making it visible again is instant, while rebuilding a dropped index on a big table can take hours.
  5. 5. What is the main limitation of a prefix index such as INDEX (url(28))?

    Only part of the value is stored, so MySQL must still read the row to see the full value and cannot rely on the index for complete ordering.

Summary

  • A composite index is sorted by its first column, then the second within it, and so on. The leftmost prefix rule decides which queries can seek.
  • Put equality columns first, then one range or sort column. Columns after a range column cannot narrow the seek.
  • Matching ORDER BY to the index (after the equality columns) removes the sort and makes LIMIT queries cheap.
  • MySQL 8.0 adds true DESC key parts, functional indexes (((LOWER(email)))), and invisible indexes for safe removal. Prefix indexes save space but cannot cover or fully sort.
  • Extend existing indexes instead of adding near-duplicates, and verify every design with EXPLAIN.

Next: EXPLAIN and EXPLAIN ANALYZE, the tool that tells you whether the index you designed is really used.