SQL Query Optimization

Review SQL queries for performance problems that only surface under load. Focus on patterns that cause full table scans, excessive memory usage, or lock contention.

Scan every query in the target file for these specific problems:

1. SELECT * in application queries. Replace with explicit column lists. The cost is not just bandwidth; it breaks covering indexes and forces unnecessary I/O.

2. Implicit type conversions in WHERE clauses. When a VARCHAR column is compared to an integer literal, the engine casts every row instead of using the index. Always match the literal type to the column type.

3. OR conditions on different columns. These typically prevent index usage. Rewrite as UNION ALL of two indexed queries when each branch targets a different column.

4. Correlated subqueries that execute per row. Replace with JOINs or lateral joins (PostgreSQL) / CROSS APPLY (SQL Server). Flag the estimated row multiplier when possible.

5. OFFSET-based pagination beyond a few thousand rows. Recommend keyset pagination (WHERE id > last_seen_id ORDER BY id LIMIT n) instead.

6. Missing index hints for queries filtering or joining on non-primary-key columns. Do not suggest indexes blindly; check if the column has sufficient cardinality. Low-cardinality columns (boolean, status enum) rarely benefit from a B-tree index alone.

7. N+1 query patterns in ORM-generated SQL. When a loop issues one query per iteration, flag it and suggest eager loading or a single IN-list query.

8. Unbounded queries with no LIMIT. Any query that could return an unpredictable number of rows in application code must have a LIMIT or TOP clause.

9. Functions applied to indexed columns in WHERE clauses (e.g., WHERE YEAR(created_at) = 2024). These prevent index seeks. Rewrite as range predicates: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.

10. DISTINCT used to mask a bad join. If DISTINCT is needed to deduplicate results, the join logic is likely wrong. Fix the join before adding DISTINCT.

When dialect matters, call it out explicitly. PostgreSQL, MySQL, and SQL Server diverge on execution plans, lateral joins, CTEs, and window function optimization. Do not assume one dialect.

Always use parameterized queries in examples. Never interpolate user input into SQL strings.

Severity levels for findings:

Critical -- Query will degrade under load or cause table locks. Examples: correlated subquery on a large table, missing LIMIT on an unbounded select, SELECT * in a hot path.

Warning -- Query works but leaves performance on the table. Examples: OFFSET pagination, function on indexed column, OR across different columns.

Suggestion -- Minor improvement or readability gain. Examples: explicit column list on a small lookup table, adding an alias for clarity.