An index that made writes slower than the reads it saved

Order insertion had gone from four milliseconds to thirty-one over eighteen months, with no change to the insert statement. The table had gained six indexes in that time, each added to make a specific report faster, each one individually justified and none of them measured against what they cost.

The symptom

mysql> SHOW INDEX FROM orders;
-- 11 indexes, 24 index columns, on a table with 9 columns

mysql> SELECT index_length / 1024 / 1024 AS idx_mb,
    ->        data_length  / 1024 / 1024 AS data_mb
    -> FROM information_schema.tables WHERE table_name = 'orders';
| idx_mb  | data_mb |
|  1841.2 |   402.8 |

-- the indexes are four and a half times the data.

Every insert updates eleven B-trees. Every update to an indexed column updates the indexes covering it, twice — once to remove the old entry and once to add the new one.

Why it happens

Adding an index is a visible fix for a visible problem and takes one migration. The cost is spread across every write, is invisible in any single request, and accrues to somebody other than the person who added it.

Nothing removes an index when the query it served is deleted, so the set only grows. Three of the eleven here served reports that had been rewritten years earlier.

The fix

Finding what is unused

SELECT object_name, index_name, count_star, count_read, count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = DATABASE()
  AND object_name = 'orders'
  AND index_name IS NOT NULL
ORDER BY count_read ASC;

-- | index_name          | count_star | count_read | count_write |
-- | idx_legacy_status   |          0 |          0 |           0 |
-- | idx_ref_2018        |          0 |          0 |           0 |
-- | idx_customer_email  |         41 |         41 |           0 |
-- | idx_placed_at       |   88104112 |   88104112 |           0 |

The counters reset when the server restarts, which is the caveat that makes this dangerous — a table read only by a monthly report looks unused after a restart three weeks ago. Recording the counters daily and comparing against a long window is the version that is safe to act on.

# a daily snapshot, so the window is real
$ mysql -Nse "INSERT INTO index_usage_history
  SELECT NOW(), object_name, index_name, count_star
  FROM performance_schema.table_io_waits_summary_by_index_usage
  WHERE object_schema = 'shop' AND index_name IS NOT NULL"

# and the question, over 90 days rather than since boot:
#   idx_legacy_status   0 reads in 90 days
#   idx_ref_2018        0 reads in 90 days
#   idx_customer_email  1,204 reads in 90 days

Overlapping indexes, which are three that are one

-- all three exist on this table
KEY idx_customer                (customer_id)
KEY idx_customer_state          (customer_id, state)
KEY idx_customer_state_placed   (customer_id, state, placed_at)

-- the third serves every query the other two do, because
-- an index serves any LEFTMOST PREFIX of its columns.
-- the first two are pure write cost.

SELECT ... WHERE customer_id = ?                   -- prefix (1)
SELECT ... WHERE customer_id = ? AND state = ?     -- prefix (1,2)

The prefix rule is well known and the redundancy it implies is routinely missed, because the three indexes were added at different times by different people each solving a query in front of them. A query against the schema finds them mechanically.

$ pt-duplicate-key-checker --databases shop
# ########################################################
# shop.orders
# ########################################################

# idx_customer is a left-prefix of idx_customer_state_placed
# Key definitions:
#   KEY `idx_customer` (`customer_id`),
#   KEY `idx_customer_state_placed` (`customer_id`,`state`,`placed_at`)
# Size: 412 MB

ALTER TABLE `shop`.`orders` DROP INDEX `idx_customer`;

The tool produces the statements and should not be trusted blindly on one point: a shorter index is smaller and therefore cheaper to scan, so on a table where the one-column form is used for a huge range scan the redundancy can be deliberate. That is rare and it is worth checking rather than assuming.

Testing a drop without committing to it

-- instant, metadata only, and reversible
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;

-- the optimiser now ignores it. the writes still maintain it.
-- run the workload for a week and watch for a plan change.

-- and to check a specific query WOULD have used it:
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ... ;

-- if nothing regresses:
ALTER TABLE orders DROP INDEX idx_legacy_status;

Invisible indexes exist for exactly this and turn an irreversible decision into a week-long experiment. The write cost is still paid while invisible, so this proves nothing is needed rather than saving anything — the saving comes at the drop.

A week is the right window because it covers the weekly reports, and a month would be better if any monthly job touches the table. Choosing the window from the schedule rather than from impatience is the part that gets skipped.

The measurement that should have happened first

-- before adding an index, on a copy:
-- 1. the write cost
SET profiling = 1;
INSERT INTO orders (...) VALUES (...);   -- x10000
SHOW PROFILES;

-- 2. the read benefit
EXPLAIN ANALYZE SELECT ... ;   -- with and without

-- 3. the ratio that decides it
--    writes/day × added write cost
--    vs
--    reads/day × saved read cost

The arithmetic is straightforward and almost nobody does it, because the read benefit is measured in a ticket and the write cost is measured nowhere. On this table the report ran forty times a day and saved 900 milliseconds each time; the index cost 2.4 milliseconds on 180,000 daily writes. The report was saving 36 seconds a day and the writes were losing 432.

That calculation changes the answer rather than merely quantifying it — the right fix for that report was a summary table, not an index, and nobody considered it because the index worked.

Verifying it worked

mysql> SHOW INDEX FROM orders;
-- 6 indexes, down from 11

mysql> SELECT index_length/1024/1024 AS idx_mb FROM ...;
|  idx_mb |
|   688.4 |        -- was 1841.2

-- insert latency, p95, over a day:
--   before  31.2ms
--   after    9.8ms

-- and the reports, checked one by one:
--   14 of 14 unchanged plan
--   1 slower: the monthly reconciliation, 2.1s → 2.4s

Checking every report rather than the ones somebody remembered is what makes a drop safe, and it needs a list of the queries that touch the table — which had to be assembled from the slow log because no such list existed. The one that got slower was acceptable and the decision to accept it was recorded.

The buffer pool hit rate improved as a side effect, because 1.1 gigabytes of index that was being maintained and rarely read had been occupying memory. That was not the goal and it was the largest single effect on read latency across the application.

What this costs

The index set is now a thing that requires maintenance rather than a thing that only grows, which means a periodic review that nobody will schedule. The daily usage snapshot is the mechanism that makes the review cheap when it happens, and it is a table that will be forgotten in eighteen months unless something reads it.

The honest risk is a dropped index that turns out to matter during a rare event — a year-end report, an unusual admin query, a support investigation. Invisible indexes reduce that risk and do not remove it, because a week of traffic does not contain an annual job. Keeping the CREATE INDEX statements for everything dropped, in a file with a date, is the cheapest possible insurance.