Covering index and the price of SELECT *

Using index in the Extra column of EXPLAIN. That is the cheapest read MySQL can do, and you often get it almost for free.

SELECT user_id, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

With an index on (user_id, created_at) everything the query needs is in the index leaves. The table is never touched. A composite index finds rows fast, a covering index answers the query on its own. Postgres calls this Index Only Scan, with one condition: the visibility map must be fresh, so a table that vacuum never visits quietly falls back to heap fetches.

Now change one thing. SELECT * instead of the two columns. The plan degrades at once: the index still finds the twenty rows, but each of them now needs a lookup into the table for the rest of the columns. Twenty random reads that were not there before. People say SELECT * is bad style. Here it is a more expensive query for the same result set.

Small InnoDB detail: secondary indexes carry the primary key in their leaves. So (user_id, created_at) covers id as well, and SELECT id, created_at WHERE user_id = ... is index-only too.

The wrong conclusion is tempting: stuff more columns into indexes until everything is covered. Every column in an index is paid on the write path, on every INSERT and every UPDATE of that column, plus buffer pool space that now holds fat index pages instead of data. Cover the two or three hottest queries, name the columns in SELECT, and stop.

I did not stop, once. Six columns in one index and the report query flew. Inserts on that table got slower the same week, and it took me a month to connect the two.