One Slow MySQL Query and the Index It Wanted

Layered red and cream canyon walls under a heavy cloud bank, with a shaft of rain falling on the left

“Index the columns you filter on” is true, and about as useful as “drive on the road.” Which columns? In what order? And how do you know the index is actually doing anything?

This note follows one slow query from a full table scan to reading twenty index entries and stopping. Then it goes the other way: finding indexes that do nothing and retiring them without a gamble. The examples use MySQL 8.4. Everything here also exists in 8.0, except that EXPLAIN ANALYZE needs 8.0.18 or later.

Find the query before you fix it

The sys schema already knows where the time went. Its statement_analysis view groups statements by normalized text and sorts them by total latency:

SELECT query, exec_count, total_latency, rows_examined_avg, rows_sent_avg, full_scan
FROM sys.statement_analysis
WHERE db = 'shop'
LIMIT 10;

Look for a big gap between rows_examined_avg and rows_sent_avg. That’s MySQL reading thousands of rows to return twenty. A * in full_scan means at least one execution scanned a whole table.

These numbers come from the Performance Schema, which is kept in memory and starts empty when the server starts. A server that restarted an hour ago only has an hour of opinions.

The slow query log works too, but it’s off by default and long_query_time defaults to 10 seconds. On a small web app, that mostly catches queries your users have already given up on.

Read the plan first

The example is an order-history page:

CREATE TABLE orders (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  status      VARCHAR(16) NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  created_at  DATETIME NOT NULL,
  note        VARCHAR(200) NULL
) ENGINE=InnoDB;

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20\G

Plain EXPLAIN doesn’t run the query. It only shows the plan. Here’s the trimmed output with about 200,000 rows and no secondary indexes:

         type: ALL
possible_keys: NULL
          key: NULL
         rows: 198816
     filtered: 1.00
        Extra: Using where; Using filesort

Five fields do most of the talking:

type is how rows are reached. ALL is a full table scan. ref and range mean an index narrowed the search. index means MySQL read an entire index from end to end, which sounds better than it is.
key is the index MySQL chose. NULL means none.
rows is an estimate of rows examined, not rows returned.
filtered is the estimated share of those rows that survive the WHERE. Here that’s 1% of 198,816: MySQL expects to read the whole table and sort about 2,000 survivors, all to hand back twenty.
Using filesort means an extra sorting pass. Despite the name, it only uses temporary files on disk when the sort doesn’t fit in memory.

One column helps a little

The obvious first move:

CREATE INDEX idx_customer ON orders (customer_id);

         type: ref
          key: idx_customer
          ref: const
         rows: 394
     filtered: 10.00
        Extra: Using where; Using filesort

MySQL now jumps straight to customer 42 instead of reading the table. But it still checks status row by row (Using where) and still sorts (Using filesort). The query asks three questions, and this index answers one of them.

Column order is the whole trick

An index is a sorted copy of some columns plus a way back to the row. A composite index sorts by its first column, then by the second within each first value, and so on. A phone book sorted by last name and then first name works the same way.

For this query, put the equality columns first and the sort column last:

CREATE INDEX idx_customer_status_created
  ON orders (customer_id, status, created_at);

         type: ref
          key: idx_customer_status_created
          ref: const,const
         rows: 199
     filtered: 100.00
        Extra: Backward index scan

ref: const,const means both equality columns were used for the lookup. The filesort is gone, because inside the (42, 'paid') slice of the index the entries are already in created_at order. MySQL reads that slice backwards for DESC. EXPLAIN ANALYZE executes the query and shows what really happened:

-> Limit: 20 row(s)  (cost=69.7 rows=20) (actual time=0.0757..0.0783 rows=20 loops=1)
    -> Index lookup on orders using idx_customer_status_created (customer_id=42, status='paid') (reverse)  (cost=69.7 rows=199) (actual time=0.0754..0.0774 rows=20 loops=1)

The estimate said 199 matching rows. The actual count is rows=20, because MySQL read twenty and stopped. Since EXPLAIN ANALYZE really runs the statement, keep it to SELECTs you’d be happy to run anyway. KILL QUERY stops one that takes longer than expected.

Now try the same index with a different query:

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE status = 'paid' AND created_at >= '2026-09-01'\G

         type: ALL
possible_keys: NULL
          key: NULL
         rows: 198816

Both columns are in the index, and MySQL ignores it anyway. Think of the phone book again: a book sorted by last name can’t help you find everyone named John. This is the leftmost-prefix rule. An index on (customer_id, status, created_at) can serve lookups on customer_id, on customer_id, status, or on all three. It can’t serve status without customer_id.

That gives a practical ordering rule:

Columns compared with = go first.
Next comes the one column used for a range (>=, BETWEEN, LIKE 'abc%') or for ORDER BY.
Once MySQL hits a range condition, the index columns after it stop narrowing the search.

If Extra says Using index for skip scan, MySQL found a way to hop over a missing leading column. It only applies under narrow conditions (one table, every referenced column in the index, and a few more), so enjoy it when it shows up, but don’t design around it.

One more plan worth comparing. With an index on a VARCHAR phone column:

EXPLAIN SELECT id FROM orders WHERE phone = 5550000042;    -- type: index, rows: 197618
EXPLAIN SELECT id FROM orders WHERE phone = '5550000042';  -- type: ref,   rows: 1

The only difference is the quotes. Lots of different strings ('5550000042', ' 5550000042', '05550000042') compare equal to the same number, so MySQL can’t seek to one spot in a string index and has to check every entry instead. If the right index exists and the plan still reads everything, check the types on both sides of the =.

Covering indexes skip the trip back to the table

The last good plan still visits the table for each of the twenty rows, because total isn’t in the index. InnoDB secondary indexes already store the primary key, so id comes along for free. Add total to the end and the query never leaves the index:

ALTER TABLE orders
  DROP INDEX idx_customer_status_created,
  ADD INDEX idx_customer_status_created_total (customer_id, status, created_at, total);

          key: idx_customer_status_created_total
          ref: const,const
        Extra: Backward index scan; Using index

Using index is the line you want. EXPLAIN ANALYZE calls it a Covering index lookup. This is also the real case against SELECT *: you can’t cover every column without building a second copy of the table.

Every index is extra work on each INSERT, each DELETE, and each UPDATE that touches an indexed column. A wide covering index pays for itself on the query that runs on every page view. It doesn’t pay for itself on a report someone opens once a quarter.

Find the indexes doing nothing

Indexes pile up, and two sys views help you find the dead weight:

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'shop';

+---------------+-------------+--------------+
| object_schema | object_name | index_name   |
+---------------+-------------+--------------+
| shop          | orders      | idx_customer |
+---------------+-------------+--------------+

SELECT redundant_index_name, redundant_index_columns,
       dominant_index_name, dominant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'shop'\G

   redundant_index_name: idx_customer
redundant_index_columns: customer_id
    dominant_index_name: idx_customer_status_created_total
 dominant_index_columns: customer_id,status,created_at,total

Both views point at idx_customer, and the second one says why. customer_id is the leftmost column of the bigger index, so anything idx_customer can do, the composite can do too.

Treat these views as leads, not verdicts:

“Unused” means no recorded index I/O since the Performance Schema started counting, which is normally the last restart. Check SHOW GLOBAL STATUS LIKE 'Uptime'; first. An index used only by the month-end report looks unused for 29 days.
schema_unused_indexes leaves out primary keys and unique indexes. That’s sensible, because a unique index enforces a rule even if no query ever reads it.
schema_redundant_indexes has an sql_drop_index column with a ready-to-paste ALTER TABLE. Hold off on pasting it for one more step.

Rehearse the drop with an invisible index

An invisible index is still updated on every write and still enforces uniqueness, but the optimizer acts as if it isn’t there. That makes it a reversible rehearsal for DROP INDEX:

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;

SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'orders' AND SEQ_IN_INDEX = 1;

+-----------------------------------+------------+
| INDEX_NAME                        | IS_VISIBLE |
+-----------------------------------+------------+
| idx_customer                      | NO         |
| idx_customer_status_created_total | YES        |
| PRIMARY                           | YES        |
+-----------------------------------+------------+

Leave it that way for a full business cycle, month-end report included. Watch sys.statement_analysis and the slow log for anything that got worse. Switching visibility is a fast in-place change, so the rollback is one line:

ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;

If you see ERROR 1176 (42000): Key 'idx_customer' doesn't exist in table 'orders', some query names that index in a hint like FORCE INDEX or USE INDEX. Hints that refer to an invisible index fail outright, which is a blunt but quick way to find them.

When nothing has complained, drop it for real:

ALTER TABLE orders DROP INDEX idx_customer;

Keep in mind that the invisible phase doesn’t save any write work, because MySQL keeps maintaining the index. The savings arrive with the drop. The primary key can’t be made invisible. Neither can a unique index on NOT NULL columns that serves as the implicit primary key of a table without one.

Advice that has expired

Older tuning guides often say to raise query_cache_size. That advice predates MySQL 8.0, which removed the query cache, query_cache_size, query_cache_type, and the SQL_CACHE modifier. On 8.x the variables aren’t there at all:

SHOW VARIABLES LIKE 'query_cache%';
-- Empty set

If a guide recommends it, check what else in that guide is older than your server.

References

Cover photo: Unsplash.