language-sqllisted

# SQL ## Query optimization basics - Start with the access pattern: filters, joins, grouping, sorting, and result size. - Index columns used for selective `WHERE`, join keys, and stable `ORDER BY` clauses. - Prefer narrow projections over `SELECT *`; return only the columns callers need. - Avoid accidental row multiplication in joins; check cardinality before adding `DISTINCT`. - Watch for N+1 query loops at application boundaries. ## EXPLAIN and EXPLAIN ANALYZE - Use `EXPLAIN` to inspect the planned access path before changing indexes or query shape. - Use `EXPLAIN ANALYZE` when you need actual timing and row counts; run it against safe data and statements. - Compare estimated vs actual rows. Large gaps often mean stale statistics, skewed data, or missing predicates. - Optimize the highest-cost operation first, but confirm the full query got faster. ## Schema design - Model durable facts, not current screens. Let queries influence indexes, not table names. - Choose primary keys deliberately; use foreign keys for integrity unless there is a measured reason not to. - Normalize to remove update anomalies, then denormalize only for proven read pressure. - Encode invariants with constraints: `NOT NULL`, `UNIQUE`, `CHECK`, and referential actions. - Plan migrations as expand-and-contract changes when existing clients need compatibility. ## Transactions and isolation - Keep transactions short; do not wait on users or remote services while holding locks. - Pick the weakest isola