Indexes: the 20% that fixes 80% of slow queries

Most 'the database is slow' complaints are one missing index away from solved. Before you reach for caching or sharding, read the query plan. EXPLAIN is a lie detector for your assumptions.

Indexes trade writes for reads — so the art is picking the few that match your real query patterns. Index what you filter, join and order by. Don't write an index for every column you can name; that's how writes get slower every year.

And an index isn't magic: a query that scans twenty thousand rows because of a WHERE on an unindexed column will still read all of them. Measurement first, always — the plan says what's expensive before your users do.read the EXPLAIN