Fifty rows. Three indexes on the table, one per column. Still slow. The query is the standard one from any multi-tenant application:
SELECT * FROM orders
WHERE tenant_id = ? AND status = ?
ORDER BY created_at DESC
LIMIT 50;
“Add an index” is the typical reaction, and one index on each column is the typical result. The number of indexes is not the point. The order of columns inside one index is.
A B-tree index on (tenant_id, status, created_at) is sorted by tenant first, inside a tenant by status, inside that by date. MySQL jumps straight to the (tenant_id, status) slice, and inside the slice the rows already lie in created_at order. No filesort. Read fifty rows, done. EXPLAIN shows a short and quiet plan.
Change the order to (created_at, tenant_id, status) and the same index is nearly useless for this query. The tree is sorted by date first, your tenant’s rows are scattered through the whole thing. Leftmost prefix is the rule: the index helps only while you consume its columns from the left, equality matches first.
That is also why three single-column indexes are not one composite. MySQL usually picks one of them, filters the rest row by row, then sorts. It can sometimes merge two indexes, but the merge is rarely the plan you hoped for. Three narrow indexes cost three structures on every write and still lose on read.
My ordering rule, stolen from the SQL indexing literature and checked on our data: equality columns first, then the range or sort column. And run EXPLAIN on production-sized data, never on the ten rows in your dev database. The optimizer changes its mind with volume.
Design the index from the query. The table does not know what you are going to ask it.