Chapter 24: Optimization and Query Tuning

SQL performance depends on how efficiently the database engine retrieves and processes data. Query tuning involves examining execution plans, managing indexes and statistics, and understanding how caching and memory usage affect results. Even simple queries can slow down when tables grow, so systematic tuning is essential for scalable applications.

Execution plans and EXPLAIN

Every SQL query produces an execution plan (a set of steps the optimizer chooses to retrieve data). The EXPLAIN (or equivalent) command shows how the engine interprets your query, which indexes it uses, and how tables are joined or scanned.

Reading an execution plan

An execution plan reveals the order of operations, join algorithms, and data access paths (index lookup, full table scan, etc.). Use it to detect missing indexes, redundant joins, or expensive sorts.

-- MySQL / MariaDB
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

-- PostgreSQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 5;

-- SQL Server
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM orders WHERE customer_id = 5;
GO
SET SHOWPLAN_TEXT OFF;

In MySQL and PostgreSQL, EXPLAIN ANALYZE (or EXPLAIN (ANALYZE, BUFFERS)) executes the query and reports actual run times and row counts.

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
GROUP BY customer_id;

Key indicators in a plan

• Seq Scan / Table Scan — full table read; may be slow for large data.
• Index Scan / Seek — uses an index to locate rows efficiently.
• Nested Loop / Hash Join / Merge Join — join strategy; the optimizer picks the cheapest for data size and available indexes.
• Sort — sorting step; watch for large memory usage or disk spills.
• Filter — condition applied after scanning; indicates possible index improvements.

Practical tuning workflow

1. Profile the query using EXPLAIN or the database’s profiler.
2. Check that filtering columns are indexed appropriately.
3. Remove unnecessary ORDER BY, DISTINCT, or subqueries.
4. Consider breaking very complex queries into smaller, staged ones.
5. Re-run EXPLAIN and compare cost estimates and runtime.

Index hints, statistics, and caching

Indexing, accurate statistics, and caching mechanisms are central to query performance. The optimizer relies on them to choose efficient paths. Poor statistics or unsuitable indexes can lead to slow queries even with correct logic.

Index hints

Engines usually select the best index automatically, but hints allow manual overrides when necessary for specific workloads or query patterns.

-- MySQL / MariaDB
SELECT * FROM orders USE INDEX (idx_customer_id) WHERE customer_id = 5;

-- SQL Server
SELECT * FROM dbo.Orders WITH (INDEX(idx_customer_id)) WHERE customer_id = 5;

-- PostgreSQL
-- No direct hint syntax, but you can influence plans via enable_* parameters
SET enable_seqscan = off;
SELECT * FROM orders WHERE customer_id = 5;
RESET enable_seqscan;

Updating and maintaining statistics

Statistics describe data distribution and help the optimizer estimate row counts. Outdated statistics can cause poor plan choices.

-- MySQL / MariaDB
ANALYZE TABLE orders;

-- PostgreSQL
ANALYZE orders;

-- SQL Server
UPDATE STATISTICS dbo.Orders;

Regularly analyze heavily updated tables. Most databases also collect statistics automatically, but manual refreshes may be needed after large data loads.

Caching and memory tuning

Database engines cache recently accessed pages, index blocks, and execution plans to reduce disk I/O. Proper configuration of cache size and query reuse can drastically improve performance.

• MySQL: innodb_buffer_pool_size controls the main cache; ideally set to 60–80% of system memory on dedicated servers.
• PostgreSQL: shared_buffers and work_mem affect caching and sort operations; effective_cache_size informs the planner about OS-level caching.
• SQL Server: uses dynamic memory management; tune max server memory and inspect plan cache hits with sys.dm_exec_cached_plans.

Rewriting queries for index use

Sometimes tuning requires minor rewrites to align with index structures. Common techniques include:

• Use exact column matches in WHERE clauses before applying functions (e.g., WHERE date_col >= '2025-01-01' rather than WHERE YEAR(date_col) = 2025).
• Prefer EXISTS over IN when joining large subqueries.
• Avoid SELECT *; fetch only needed columns to reduce I/O.
• Consolidate redundant joins or nested views.

Monitoring and profiling tools

Use each engine’s built-in monitoring features to detect slow queries and resource bottlenecks.

Denormalization and partitioning trade-offs

Normalization improves consistency and avoids redundancy, but highly normalized schemas can cause performance issues for large analytical queries or heavy joins. Denormalization stores repeated data intentionally to speed up reads, while partitioning divides tables into segments for manageability and performance.

When denormalization helps

Denormalization is useful for read-heavy systems, reporting layers, or data warehouses where query simplicity and speed outweigh small risks of redundancy. For example, storing a customer_name directly in an orders table avoids a frequent join.

-- Example of selective denormalization
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);
UPDATE orders
SET customer_name = c.name
FROM customers c
WHERE c.id = orders.customer_id;

Partitioning strategies

Partitioning splits large tables into smaller physical units (called partitions) that are queried transparently. It improves performance by reducing the data scanned, and aids in maintenance and archiving.

-- PostgreSQL: range partitioning by date
CREATE TABLE orders_2025 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- MySQL: range partitioning
CREATE TABLE orders (
  id BIGINT,
  order_date DATE,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

PostgreSQL, MySQL, and Oracle support range, list, and hash partitioning natively. SQL Server uses partitioned tables with schemes and functions.

Balancing normalization, denormalization, and partitioning

Combine approaches where each fits best:

• Keep OLTP (transactional) data normalized for integrity and small updates.
• Use denormalized or materialized summary tables for analytics.
• Partition large tables to reduce maintenance time and improve archival performance.