A subscriptions table, about 400k rows. The query that runs on every cron pass looks for rows due for renewal:
SELECT id, user_id, plan_id
FROM subscriptions
WHERE status = 'active'
AND next_charge_at <= NOW()
ORDER BY next_charge_at
LIMIT 200;There was an index on (next_charge_at, status). Swapping it to
(status, next_charge_at) took the query from 380 ms to 4 ms. Same
columns, same table, same data.
An index is sorted left to right, like a phone book sorted by surname and then first name. You can find everyone named Petrov quickly, and among the Petrovs you can find Ivan quickly. You cannot find every Ivan quickly, no matter the surname.
The query has an equality on status and a range on
next_charge_at. Put the equality first and the engine jumps straight
to the block of active rows, then walks forward through them in date order until
the limit is satisfied. It reads 200 rows.
Put the range first and the engine has to walk every row with a date in the past
— most of the table — checking status on each one. It also cannot use the index for
the ORDER BY cleanly.
Equality columns first, then the range column, then anything needed only for sorting. If several queries need different orders, that is usually a sign you need two indexes rather than one clever one.
EXPLAIN ANALYZE SELECT ...;Look at two numbers. rows is what the optimiser expects to examine —
if it is close to the table size, the index is not being used the way you think.
And in the Extra column, Using filesort means the sort is
happening in memory after the fact rather than coming free from the index.
After adding the new index I left the old one in place, and the optimiser kept choosing the old one. Statistics were stale.
ANALYZE TABLE subscriptions;Then drop the index you no longer need. Two overlapping indexes on the same columns cost write throughput and give the optimiser a chance to pick wrong.