sql-optimization-patterns

SQL query optimization uses EXPLAIN plans to identify bottlenecks, then applies indexing strategies and refactors inefficient patterns. Interpret EXPLAIN output to locate sequential scans or expensive joins, then create B-tree, partial, or composite indexes and replace bad patterns like SELECT * with targeted queries to reduce execution time.

Indexing strategies include B-tree indexes for range queries, partial indexes for filtered subsets, composite indexes for multi-column lookups, and covering indexes to satisfy queries without table access. Apply these based on EXPLAIN analysis to eliminate full table scans and lower query cost.

EXPLAIN plans show query execution steps, scan types, row estimates, and cost metrics. Sequential scans indicate missing indexes; nested loops suggest join optimization; high actual-vs-estimated rows signal stale statistics. Use these insights to propose index creation or query refactoring.

Yes. This approach applies to debugging slow queries by running EXPLAIN analysis, identifying inefficient scans or joins, recommending indexes, and validating improvements through query plans and lightweight benchmarks across PostgreSQL and MySQL.

Avoid SELECT * on large tables, Cartesian joins from missing join conditions, and unindexed WHERE clauses on high-cardinality columns. Replace these with targeted column selection, explicit join conditions, and strategic indexing to reduce scan cost and improve latency.