When should I add an index?

Añadir 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).

Optimizar lento SQL queries

Identificar y fix lento SQL queries usando EXPLAIN análisis, index optimization, y consulta 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

Formateador SQL →
Formatear 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
Ver receta Abrir en cadena de herramientas

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.

Preguntas frecuentes

When should I add an index?: Añadir 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).