Making queries fast.
WHAT TO MEASURE FIRST
Which queries are slow, and how many run per request.
Never optimise by guessing.
WHAT USUALLY CAUSES SLOWNESS
Missing indexes Query multiplication in loops Retrieving more data than needed Aggregations over large tables Sorting on unindexed columns
WHAT AN INDEX DOES
Allows the database to find rows without examining every one.
WHAT TO INDEX
Columns used in filtering, joining and sorting.
WHAT TO CHECK
Whether an index is actually used, using the database's explanation facility.
WHAT ELSE HELPS
Selecting only needed columns Paginating rather than loading everything Caching expensive results
WHAT TO BE CAREFUL WITH CACHING
Staleness, and knowing when to clear it.
WHAT TO DO ABOUT REPEATED IDENTICAL QUERIES
Cache the result.
WHAT TO DO ABOUT SLOW AGGREGATIONS
Consider maintaining a running total rather than recalculating.
WHAT TO MONITOR IN PRODUCTION
Slow queries, which most databases can log.
