SQL: QUERY OPTIMIZATION

1. How to Optimize SQL Queries

SQL is a declarative language: you specify what data you want, and the database engine decides how to retrieve it.

Behind every query, the database Cost-Based Optimizer (CBO) estimates multiple possible execution strategies and calculates a mathematical cost based on:

• Disk Page Reads: Number of 8KB data and index pages that must be loaded from persistent storage into memory.
• CPU Cycles: Computational overhead required to evaluate filters, perform string matches, and hash join keys.
• Memory Footprint: Amount of buffer pool cache and temporary work memory required to sort and aggregate records.

2. EXPLAIN and EXPLAIN ANALYZE

Before modifying a query or adding indexes, inspect the execution plan using the EXPLAIN command family:

• EXPLAIN: Generates the estimated execution plan using database statistics without running the query.
• EXPLAIN ANALYZE (PostgreSQL / MySQL 8+): Actually executes the query, recording real runtime execution statistics alongside initial estimates.

3. Reading a Query Execution Plan

Execution plans represent a tree of operations evaluated from the inside out (deepest child node first):

Table Access Operators

• Seq Scan / SCAN TABLE (Full Table Scan): The engine reads every single page in the table sequentially. Normal for small tables, but slow for selective queries on large tables.
• Index Scan / SEARCH TABLE USING INDEX: Traverses a B-Tree index to find qualifying keys, then reads table rows to fetch unindexed columns.
• Index Only Scan (Covering Scan): Satisfies the entire query directly from index leaf pages without loading table pages.

Join Algorithms

4. Index Optimization

Effective index tuning focuses on high-impact columns while avoiding index bloat:

• Index Selectivity: Selectivity measures the percentage of unique values in a column. A column with 99% unique values (like email) has high selectivity; an index will accelerate queries significantly. A column with 2 distinct values (like is_active) has low selectivity; the optimizer usually ignores the index.
• Eliminate Redundant Indexes: If you maintain a composite index on (department_id, salary), a separate standalone index on (department_id) is redundant because the composite index already satisfies queries filtered by department_id.

5. Avoiding SELECT *

Writing SELECT * in production backend services harms performance in four ways:

• Excess Network and Disk I/O: Transmits unnecessary columns across database sockets, slowing serialization.
• Destroys Covering Indexes: If an index contains columns (id, name), requesting SELECT * forces the engine to read table storage pages to retrieve missing columns.
• Buffer Pool Eviction: Fetching large unused TEXT or BLOB columns pushes valuable cached pages out of RAM.
• Application Fragility: Schema migrations adding new columns can break strict client JSON parsers.

6. Sargable Queries (Search Argument Able)

A query predicate is sargable if the database engine can directly traverse a B-Tree index rather than evaluating functions on every row.

The most common cause of slow queries is wrapping indexed columns inside functions in the WHERE clause:

7. Optimizing JOINs and Subqueries

Correlated subqueries execute once for every single row produced by the outer query. In high-volume tables, replacing correlated subqueries with set-based JOIN operations can speed up execution by orders of magnitude:

8. The N+1 Query Problem

The N+1 query problem is a performance issue introduced by Object-Relational Mappers (ORMs) when fetching parent records and their associated children:

• The 1 Query: The application executes SELECT * FROM orders LIMIT 25; to fetch 25 orders.
• The N Queries: Inside a loop, the application code accesses each order's customer, executing SELECT * FROM customers WHERE id = ?; 25 separate times.
• Total Overhead: 1 + 25 = 26 individual network trips across the application and database.

Solutions

• Single Relational JOIN: SELECT o.*, c.name FROM orders o JOIN customers c ON c.id = o.customer_id;
• Batched IN Clause: Collect all 25 customer IDs and execute a single lookup: SELECT * FROM customers WHERE id IN (1, 2, 3, ...);
• ORM Eager Loading: Use keywords like include (Prisma), joinedload (SQLAlchemy), or select_related (Django).

9. Partitioning Tables

Table partitioning divides massive tables into smaller, distinct physical storage units while presenting a single logical table to queries:

• Range Partitioning: Splits tables by continuous numeric or date ranges (e.g., one partition per calendar year).
• List Partitioning: Splits tables by discrete key values (e.g., partitioning by region or country).
• Hash Partitioning: Distributes rows evenly across partitions using a mathematical hash key.

10. Query Caching

Relational databases cache frequently accessed information across two distinct tiers:

• Buffer Pool / Shared Buffers: Keeps recently accessed 8KB data and index pages in RAM. When a query needs a row, it reads from memory at nanosecond speeds rather than reading from disk at millisecond speeds.
• Application Cache (Redis / Memcached): Caches parsed JSON API responses outside the database entirely, bypassing SQL parsing overhead on identical read-heavy endpoints.

11. Finding and Fixing Slow Queries

Systematic database performance tuning relies on active profiling tools:

• MySQL Slow Query Log: Records queries that exceed a configurable latency threshold (e.g., long_query_time = 1.0).
• PostgreSQL pg_stat_statements: Tracks aggregated execution counts, average latency, and disk read statistics across all executed queries.
• SQL Server Query Store: Captures historical execution plan changes and flags performance regressions.