The database indexes I wish I'd learned sooner

For years I blamed Eloquent for slow pages. It was almost always a missing or badly ordered index.

Read EXPLAIN first

Before touching code, run the query with EXPLAIN. A full table scan on a table with a million rows explains most mysteries.

EXPLAIN SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;

Column order matters

A composite index on (user_id, created_at) serves the query above. The same columns in the opposite order mostly do not.

Cover the hot queries

If a query only needs three columns, an index containing all three lets the database skip the table entirely.

Do not index everything

• Every index slows down writes.
• Low-cardinality columns rarely help on their own.
• Unused indexes are just storage with opinions.

Profile, add one index, measure again. Boring, and it works.