Practical query construction.
WHAT TO SELECT
Only the columns needed.
WHY NOT EVERYTHING
It transfers more, prevents covering indexes, and breaks when the schema changes.
WHAT TO AVOID IN CONDITIONS
Functions wrapping the column Comparing different types Leading wildcards in pattern matches Conditions the database cannot reason about
WHAT TO PREFER FOR EXISTENCE CHECKS
An existence test rather than counting rows.
WHY
It stops at the first match.
WHAT TO BE CAREFUL WITH IN SUBQUERIES
Ones executed once per outer row.
WHAT TO PREFER
A join, or a derived table, where the optimiser handles it better.
WHAT PAGINATION USING LARGE OFFSETS COSTS
The database reads and discards everything before the offset.
WHAT TO USE INSTEAD
A condition on the last value seen.
WHY THAT MATTERS AT SCALE
Deep pagination becomes unusable otherwise.
WHAT BATCHING PROVIDES FOR LARGE UPDATES
Shorter locks, and no enormous transaction.
WHAT TO NEVER DO
Issue one query per row in a loop.
WHY
The round trips dominate, and it is orders of magnitude slower than one statement.
WHAT TO MEASURE
The query, on realistic data volume.
WHY NOT DEVELOPMENT DATA
A thousand rows hides everything.
