Composite Indexes and Column Order

You have one composite index on (customer_id, status) and a query that filters on status alone. The index does not help, the query scans 50,000 rows, and adding a second index on status feels like the only fix. It usually is not, and the reason is visible in the plan if you write the query twice against two tables that differ only in the order of the columns.

The examples ran on MySQL 9.6.0 with InnoDB and SQLite 3.51.0, and the output is pasted as it came back.

All of it runs against two tables that hold the same 50,000 rows and differ in one thing. order_ab has KEY idx_cust_status (customer_id, status) and order_ba has KEY idx_status_cust (status, customer_id), and every query below is written twice, once per table.

The same query against both orders, and the plans are not the same

Start with a predicate on both columns. This is the case where column order looks like it does not matter:

EXPLAIN SELECT id, amount_cents FROM order_ab WHERE customer_id = 137 AND status = 'paid';
EXPLAIN SELECT id, amount_cents FROM order_ba WHERE customer_id = 137 AND status = 'paid';

table     type  key                key_len  ref          rows  filtered  Extra
order_ab  ref   idx_cust_status         54  const,const   250    100.00  NULL
order_ba  ref   idx_status_cust         54  const,const   250    100.00  NULL

Both plans are identical where it counts: access type ref, a two-part key length, and const,const in the ref column. With equality on every indexed column, the engine can seek by the first column and then seek within that group by the second, and that works in either order. Equality is commutative, so this case tells you nothing about column order.

Now write the predicate that most applications actually have, where you filter by one attribute and sort or filter by the other. Here is customer_id alone:

EXPLAIN SELECT id FROM order_ab WHERE customer_id = 137;
EXPLAIN SELECT id FROM order_ba WHERE customer_id = 137;

table     type   key                key_len  ref    rows   filtered  Extra
order_ab  ref    idx_cust_status         4  const     250    100.00  Using index
order_ba  range  idx_status_cust        54  NULL     5006    100.00  Using where; Using index for skip scan

order_ab seeks to the group of 250 entries for that customer. order_ba cannot, because customer_id is the second column and entries for one customer are spread across every status group. MySQL falls back to a skip scan and estimates 5,006 rows instead of 250, which is twenty times the work for the same answer.

EXPLAIN ANALYZE shows the same split measured rather than estimated:

EXPLAIN ANALYZE SELECT id FROM order_ab WHERE customer_id = 137;
-> Covering index lookup on order_ab using idx_cust_status (customer_id = 137)
     (cost=25.7 rows=250) (actual time=0.0556..0.0904 rows=250 loops=1)

EXPLAIN ANALYZE SELECT id FROM order_ba WHERE customer_id = 137;
-> Filter: (order_ba.customer_id = 137)  (cost=1264 rows=5006) (actual time=0.053..0.254 rows=250 loops=1)
    -> Covering index skip scan on order_ba using idx_status_cust over customer_id = 137
         (cost=1264 rows=5006) (actual time=0.052..0.232 rows=250 loops=1)

The estimated cost differs by a factor of fifty, and both plans return the same 250 rows. The second plan is not wrong, and the difference does not matter for a shared buffers hit on this machine. It matters when the table is 200 million rows and the skip scan touches every leaf.

Put equality columns first and range columns after them

The ordering rule follows from the tree, not from a preference. An index on (customer_id, created_at, status) is sorted by customer, then by time inside a customer, then by status inside a time. A seek can only start at the left edge of that sort order, and once a predicate matches a range rather than one value, the scan is inside that range and no later column can narrow it.

Three predicates on visits2, which has 50,000 rows and KEY idx_cust_created_status (customer_id, created_at, status), make the rule visible in the key_len column. Read that column as how many bytes of the index key the engine uses to position itself.

-- G: equality on the first column only
EXPLAIN SELECT COUNT(*), MAX(amount_cents) FROM visits2
 WHERE customer_id = 137 AND status = 'paid';

-- H: a range on the second column
EXPLAIN SELECT COUNT(*), MAX(amount_cents) FROM visits2
 WHERE customer_id = 137 AND created_at > '2026-01-01 10:00:00' AND status = 'paid';

-- I: equality on all three
EXPLAIN SELECT COUNT(*), MAX(amount_cents) FROM visits2
 WHERE customer_id = 137 AND created_at = '2026-01-01 00:02:17' AND status = 'paid';

table    type   key                     key_len  ref                rows  filtered  Extra
visits2  ref    idx_cust_created_status       4  const               250     10.00  Using index condition
visits2  range  idx_cust_created_status       9  NULL                 70     10.00  Using index condition
visits2  ref    idx_cust_created_status      59  const,const,const     1    100.00  NULL

Four bytes in the first plan is customer_id alone. Nine bytes in the second is customer_id plus created_at: equality on the first, range on the second, and the index stops there. Fifty-nine bytes in the third is all three columns, because all three are equalities. Notice the filtered column in the first two plans: it is 10%, the estimate for one status value out of four, and those filtering rows have to be examined and rejected after the index has produced them.

The EXPLAIN ANALYZE for the second query says the same thing in words, in the index condition clause:

-> Aggregate: count(0), max(visits2.amount_cents)  (cost=47.9 rows=1) (actual time=0.12..0.12 rows=1 loops=1)
    -> Index range scan on visits2 using idx_cust_created_status over
       (customer_id = 137 AND '2026-01-01 10:00:00' < created_at), with index condition:
       ((visits2.`status` = 'paid') and (visits2.customer_id = 137) and (visits2.created_at > TIMESTAMP'2026-01-01 10:00:00'))
       (cost=31.8 rows=70) (actual time=0.0935..0.116 rows=70 loops=1)

The range scan is positioned by the first two columns. status is not part of the positioning; it appears in the index condition, which is a filter applied to the entries the scan found. That is index condition pushdown doing its job: the check happens in the storage engine, but it is still a check on every entry in the range rather than a seek.

If status were a column you filter on constantly, the correct index would be (customer_id, status, created_at), not a fourth index. Equality columns first, the range column last, and that is the whole of the ordering rule. It also explains the common failure where someone indexes (created_at, customer_id) because the report is "about time": every query that filters by customer has the leading column missing, and the index is decorative.

An ORDER BY that matches the index removes the sort

Sorted index leaves mean a query can read rows in order instead of collecting and sorting them. The same three-column index, ordered two ways and then one way it cannot serve:

EXPLAIN SELECT id, created_at FROM visits2 WHERE customer_id = 137 ORDER BY created_at LIMIT 5;
EXPLAIN SELECT id, created_at FROM visits2 WHERE customer_id = 137 ORDER BY created_at DESC LIMIT 5;
EXPLAIN SELECT id, amount_cents FROM visits2 WHERE customer_id = 137 ORDER BY amount_cents LIMIT 5;

table    type  key                     key_len  ref    rows  Extra
visits2  ref   idx_cust_created_status       4  const   250  Using index
visits2  ref   idx_cust_created_status       4  const   250  Backward index scan; Using index
visits2  ref   idx_cust_created_status       4  const   250  Using filesort

The first plan reads forwards through a run of leaves that is already in order. The second reads the same run backwards, which is one of the reasons a DESC query is rarely the problem people expect. The third has no order to inherit, because amount_cents is not in the index, so MySQL builds a sort of 250 rows before returning five. The word to look for in Extra is Using filesort, and its presence means the ordering work is happening after the rows are found.

Two details make Using filesort easier to reason about. It does not necessarily mean a file: for a small row count the sort happens in memory, and the name is historical. It does mean a second pass over the rows, and when the row count is large that pass can spill to temporary files on disk. Lesson twelve shows how to catch a spill that is large enough to matter.

When one column already decides another, the index column is redundant

A postal code determines a city. If you index (postal_code) and a query filters on both, MySQL can still use the index for the postal code and check the city afterwards, because the index told it which rows to look at. Putting the city in the index as well changes the plan in a way that costs writes and buys nothing:

CREATE INDEX idx_postal_city ON addr (postal_code, city);
EXPLAIN SELECT street FROM addr WHERE postal_code = '28013' AND city = 'Madrid';

table  type  key               key_len  ref          rows  filtered  Extra
addr   ref   idx_postal_city       148  const,const     1    100.00  NULL

Drop the second column and the plan is a seek on postal_code with the city checked as a condition on the one row it found:

table  type  key         key_len  ref    rows  filtered  Extra
addr   ref   idx_postal       26  const     1     33.33  Using where

Both plans find the row. The second index is shorter, and every insert into addr maintains a smaller structure. The filtered value of 33.33 is the estimate for the city condition, and it applies to one row. The rule is not that redundant columns are always wrong, it is that a column the predicate already pins down does not need to be in the key, and a composite index kept "just in case" is a write cost paid forever. The one case where the extra column earns its place is when the index is asked to cover a query that selects it.

MySQL has no partial index, so the workaround is a different index

A partial index covers only the rows that satisfy a predicate. PostgreSQL and SQLite both have them, and the syntax is the same shape in both: CREATE INDEX ... ON ... (columns) WHERE predicate.

Take a table where one status is rare and interesting and the rest is history: 20,000 rows, 1,000 of them open, 19,000 archived. In SQLite:

CREATE INDEX idx_open ON orders (customer_id) WHERE status = 'open';
CREATE INDEX idx_full ON orders (customer_id);

name      bytes
idx_full  192512
idx_open   16384

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE status = 'open' AND customer_id = 3;
`--SEARCH orders USING COVERING INDEX idx_open (customer_id=?)

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE status = 'archived' AND customer_id = 3;
`--SCAN orders

The partial index is 16 KB against 192 KB, about a twelfth of the size, and it is used only when the query's predicate implies the index's predicate. The second query asks for archived, which the index does not contain, so SQLite scans the table. That asymmetry is the point: the index is small because it deliberately covers one case.

MySQL does not support this. The statement is a syntax error on 9.6.0:

CREATE TABLE pidx (id INT PRIMARY KEY, a INT, s VARCHAR(10), KEY k (a) WHERE s = 'open');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'WHERE s='open')' at line 1

MySQL also rejects the INCLUDE clause that PostgreSQL uses for covering indexes: KEY k (a) INCLUDE (b) is a syntax error on the same server, even though the manual lists the clause. In MySQL the equivalent of a covering index is to put the extra columns in the key, and the equivalent of a partial index does not exist. The closest you get is a generated column and an index on it, or a leading column whose value makes the interesting rows a contiguous range.

That last option is more useful than it sounds. If status is the distribution knob, an index on (status, customer_id) puts all the open rows in one region of the tree. The engine still has to find that region, and it will only prefer it to a scan when the region is small, which is the selectivity argument from lesson ten. For a rare status it works; for a 50/50 split it does not, and you should expect the planner to choose the table scan.

PostgreSQL's manual is worth reading on when a partial index is and is not a good idea, at 11.8 Partial Indexes. The two claims that matter in practice: the query planner can use a partial index only when it can prove the query implies the predicate, and a set of non-overlapping partial indexes on the same column is usually worse than one plain composite index.

Show the solution and the reasoning

The query is SELECT id FROM visits2 WHERE customer_id = ? AND status = 'paid' ORDER BY created_at DESC LIMIT 5, and the index you want is (customer_id, status, created_at). With the existing index, customer_id positions the scan and status is an index condition on every entry in that customer's group; the plan will show a key_len of 4 and a separate filter, or a filesort if the sort order cannot be inherited. With the new index, both equality columns position the scan and created_at is the range, so the plan shows a larger key_len covering customer_id and status, and the order-by is satisfied by reading the range backwards. The rule that made this work is the one from this lesson: equality columns first, range column last. Reversing the last two columns gives you (customer_id, created_at, status), which cannot use status for positioning after the range on created_at.

The second exercise has an asymmetry that is easy to miss. On order_ab, status is the second column, so the plan scans the index and filters, then sorts by customer_id unless the entire index order happens to help. On order_ba, status is the leading column, so the plan seeks to the status = '...' region, and within that region the entries are already ordered by customer_id, which can remove the sort. The trade-off is real: the first index is better for queries that filter by customer, the second for queries that filter by status. If both query shapes matter, the honest answer is two indexes and a write cost, which is the trade the first lesson measured.

For the last question, only the first predicate uses all three columns. a = 1 AND b = 2 AND c = 3 is three equalities and seeks all the way. a = 1 AND b > 2 AND c = 3 uses a and b to position and filters c, because the range on b ends the positioning. a > 1 AND b = 2 uses a only, and b becomes a filter. b = 2 AND c = 3 cannot seek at all, because the leading column is missing, so the best case is a full index scan or the skip scan MySQL chose in this lesson. The ordering of the predicates in the WHERE clause is irrelevant; only the column order in the index decides.