Indexing Every Column Separately Won't Fix a Slow Multi-Column WHERE Query

A query filters on three columns, it's slow, so someone adds an index to each of those three columns. The page still loads slowly, the slow query log keeps growing, and EXPLAIN shows the query still reading thousands of rows to return a handful. The idea behind that fix is that MySQL will combine all three indexes the way you'd stack filters in a spreadsheet. Most of the time, it doesn't. This comes up often in hosting support. A site owner or developer says they already indexed everything, and the first useful check is to compare two columns in EXPLAIN: which indexes the optimizer considered, and which one it actually used.

What the optimizer does with three separate indexes

For each table in a query, MySQL and MariaDB typically pick one index to find rows. The optimizer, the part of the server that decides how to run a query, looks at index statistics and estimates which single index narrows the result the most. It reads the matching entries from that index, fetches each full row from the table, and then checks the remaining WHERE conditions against the row itself. The other two indexes just sit there. They show up as candidates, but they do no work for this query. There is an exception called index merge. The optimizer can read two or more indexes separately and intersect or union the row IDs they return. When that happens, EXPLAIN shows type as index_merge and the Extra column mentions something like Using intersect(...). It sounds like exactly what you wanted, but in practice it's chosen only under specific conditions, and building and intersecting row ID lists costs CPU and memory on every execution. A rule of thumb: if you see index merge on a hot query, treat it as a sign that a composite index is missing, not as the solution.

Seeing the problem in EXPLAIN

Take an orders table with separate indexes on customer_id, status, and created_at, and a query that filters on all three. Both statements below are read-only.

SQL

-- List the existing indexes on the table
SHOW INDEX FROM orders;

-- Ask the optimizer how it plans to run the query (\G prints one field per line)
EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 AND status = 'paid' AND created_at >= '2024-01-01'\G

Illustrative output format (values will differ on your data):

TEXT

           id: 1
  select_type: SIMPLE
        table: orders
         type: ref
possible_keys: idx_customer,idx_status,idx_created
          key: idx_customer
      key_len: 4
          ref: const
         rows: 3000
     filtered: 3.33
        Extra: Using where

Read it field by field. possible_keys lists all three indexes, so the server knows they exist. key shows the one it chose: only idx_customer. rows is the estimated number of rows it will read through that index, and filtered is the estimated percentage of those rows that survive the other conditions. In this illustrative example, that's roughly 3,000 rows read to return about 100. Using where means the server is checking status and created_at after fetching each row, not inside an index. On a development copy with a small table, this plan feels fine. In production, the table grows, some customers have far more orders than others, and each of those row fetches may be a random read from disk if the data isn't already in the InnoDB buffer pool. Multiply that by every concurrent visitor running the same query, and a plan that looked harmless becomes the reason the server's disk and CPU are busy.

How a composite index changes the work

A composite index is one index built over several columns, for example (customer_id, status, created_at). It's stored as a B-tree sorted by the first column, then by the second column within each value of the first, then by the third. A printed phone book works the same way: sorted by last name, then by first name within each last name. That ordering is what makes the difference. With equality on customer_id and status and a range on created_at, all matching entries sit next to each other in the index. The server seeks to the first one and reads forward until the range ends. It no longer fetches thousands of rows only to discard most of them. The ordering also explains the leftmost prefix rule. An index on (a, b, c) can serve conditions on a, on a and b, or on a, b, and c. It can't efficiently serve a condition on b alone or c alone, for the same reason a phone book sorted by last name doesn't help you find everyone named Budi.

Choosing the column order

Column order is where most composite indexes go wrong. A few rules of thumb cover most real queries. Put equality columns first and range columns last. A range condition means >, <, BETWEEN, or LIKE 'prefix%'. Once the index seek hits a range column, columns after it can't narrow the seek any further. They can still be checked inside the index through index condition pushdown, which shows up as Using index condition in EXPLAIN, but that's filtering, not seeking. So (customer_id, status, created_at) fits this query well, while (created_at, customer_id, status) would scan every order since the start date and filter from there. Among the equality columns, the order matters less for this particular query. You'll often hear that the most selective column must always go first. It's not wrong, but it's not the main rule. A better question is which other queries could reuse the same index through its leftmost prefix. If the application also runs queries filtering only by customer_id, putting it first lets one index serve both. Consider ORDER BY as well. If the query sorts by created_at after filtering on customer_id and status, the composite index already returns rows in that order, and EXPLAIN won't show Using filesort. That can matter as much as the filtering itself on pages that list recent records. You can go one step further and add the selected columns to the index so the server never touches the table rows. EXPLAIN then shows Using index, which means a covering index. The trade-off is a wider index, more disk and memory, and more work on every INSERT and UPDATE, so it's worth doing only for queries that run constantly.

Adding the index and confirming the change

Adding an index changes a production table, so check the table size first and plan the timing. On InnoDB in modern MySQL and MariaDB, ADD INDEX usually runs as online DDL, so reads and writes can continue while the index builds. It still needs a short metadata lock at the start and end. If a long-running transaction or open query holds the table, the ALTER waits, and new queries can queue up behind it, which can make a site hang. Building the index also adds disk I/O. Run it during a quiet period, and test on a staging copy first if the table is large.

SQL

-- Add a composite index: equality columns first, range column last
ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at);

-- Re-check the plan for the same query
EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 AND status = 'paid' AND created_at >= '2024-01-01'\G

In the new plan, look for key showing the composite index, type showing range, and a rows estimate close to the number of rows the query actually returns. A larger key_len than before is a good sign too, because it shows the server is using more than just the first column of the index. EXPLAIN shows estimates. To see what actually happened, MySQL 8.0.18 and later support EXPLAIN ANALYZE, and MariaDB has the ANALYZE statement, both of which run the query and report real row counts and timings. Then watch the slow query log over the next few hours to confirm the query has dropped out of it.

Cleaning up the indexes you no longer need

Once the composite index exists, the single-column index on customer_id is redundant, because it's the leftmost prefix of the new one. A lone index on a low-variety column like status is rarely useful on its own either. Every index costs disk space, buffer pool memory, and extra work on every write, so leftover indexes slowly tax the whole table. Don't drop them blindly, though. Another query somewhere in the application might rely on one, and dropping an index isn't instantly reversible on a big table, since recreating it means another full build. A safer path is to check first. MySQL's sys schema has a schema_redundant_indexes view, and MySQL 8.0 can mark an index invisible so the optimizer ignores it while it still exists. Newer MariaDB releases have a similar ignored index option. Hide the index, watch the slow query log for a while, and drop it only when nothing gets worse. The practical habit is short: when a multi-column WHERE is slow, look at key before adding anything. If it shows one index while possible_keys lists several, the server is doing what it normally does, and more single-column indexes won't change that. Build one composite index that matches the query, with equality columns first and the range or sort column last, add it at a quiet time, confirm the new plan with EXPLAIN, and then retire the indexes it replaced.