MySQL index design starts with the query

Start from the access pattern

An index is a commitment to an access pattern. Before creating one, write down the filtering, ordering, and expected result size.

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

CREATE INDEX orders_customer_created
ON orders (customer_id, created_at);

Check the plan

Use EXPLAIN to inspect the chosen access path, then measure against representative data. An index that helps one read pattern also adds write and storage costs.

Different storage choices

An index is not the same thing as a storage architecture. Compare this access-path exercise with LSM Tree, then examine the background work in RocksDB.