Most “add an index” tickets make writes slower and leave the slow query unchanged. Here is how we decide what to index.
We do not index every column that appears in a WHERE clause. We take a week of slow-query logs (or pg_stat_statements / MySQL performance_schema), sort by total time, and pick the top five statements that hit an API path users wait on. Indexes exist to serve those statements.
On the fleet dispatch work, the morning board filtered by dispatch_date and depot_id, then joined drivers and status. Separate indexes on date and depot still produced a large scan because the planner could not use both efficiently. A composite index on (dispatch_date, depot_id) matched the predicate order the application actually used.
Put equality filters first, then range filters. (depot_id, dispatch_date) is a different index from (dispatch_date, depot_id). If the API always sends a depot and a date range, depot first is usually the better leading column. If most traffic is “all depots for today,” date first wins. We check both EXPLAIN plans with production-like row counts, not with a 50-row dump.
We also check whether the query can be covered. If the SELECT list is a handful of columns, including them in the index (INCLUDE in PostgreSQL, extra trailing columns in MySQL) avoids heap lookups on the hot path. We only do this when the index stays smaller than the table and write volume is moderate.
Every insert, update, and delete maintains every index on that table. Status updates every few minutes on 120 vehicles made a fourth overlapping index more expensive than the read it was meant to help. We dropped two unused indexes after confirming they never appeared in EXPLAIN for the live endpoints.
Partial indexes are useful when a large fraction of rows are never queried — for example “open” dispatches only. We use them when the WHERE clause in the app matches the predicate exactly. A mismatch means the index is invisible to the planner.
We create indexes concurrently (PostgreSQL CREATE INDEX CONCURRENTLY, MySQL online DDL where the version supports it) so production traffic is not locked. After create, we compare P95 on the endpoint for 48 hours and watch write latency on the same table. If writes climb more than we budgeted, we revert before adding more indexes.
This is the same sequence we used before caching: indexes first, then Redis for aggregates that are still expensive after the joins are sane. Caching a bad query just hides it until the cache expires at 6:05 AM.
If you want this done on a live schema, see our backend work or the 4s → 600ms write-up.