When should I add an index?

Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes for multi-column conditions (order matters — put equality conditions first). Avoid indexes on low-cardinality columns (boolean, gender) — they rarely help. Monitor index usage and remove unused indexes (they slow writes).

Why is OFFSET bad for pagination?

OFFSET skips rows by scanning and discarding them — page 1000 with LIMIT 10 scans 10,010 rows. Use keyset pagination (WHERE id > last_id ORDER BY id LIMIT 10) instead — it scans only the 10 rows. For complex sort orders, use a composite cursor (sort column + unique id).

TASK

Optimize slow SQL queries

Identify and fix slow SQL queries using EXPLAIN analysis, index optimization, and query rewriting techniques.

1

Query optimization steps: (1) Run EXPLAIN ANALYZE to see the execution plan. (2) Look for sequential scans on large tables — add indexes. (3) Check join types — nested loops are slow for large tables. (4) Reduce the working set with WHERE before JOIN. (5) Avoid SELECT * — fetch only needed columns. (6) Use LIMIT for pagination instead of OFFSET.
2

SQL Formatter →
Format the query for readability before analysis. A well-formatted query makes it easier to spot inefficiencies: missing WHERE clauses, unnecessary subqueries, redundant joins. Format also helps when comparing the query with the EXPLAIN output.
3

sql-format → word-count
View recipe Open in Toolchain

Use the SQL format workflow to format, analyze, and document the query. This produces a clean, readable query with consistent indentation that is easier to optimize.

Frequently asked questions

When should I add an index?: Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes for multi-column conditions (order matters — put equality conditions first). Avoid indexes on low-cardinality columns (boolean, gender) — they rarely help. Monitor index usage and remove unused indexes (they slow writes).
Why is OFFSET bad for pagination?: OFFSET skips rows by scanning and discarding them — page 1000 with LIMIT 10 scans 10,010 rows. Use keyset pagination (WHERE id > last_id ORDER BY id LIMIT 10) instead — it scans only the 10 rows. For complex sort orders, use a composite cursor (sort column + unique id).