Query Optimization

Optimization is about reducing the work the database has to do. This involves choosing the right joins, writing sargable queries, and understanding how the database engine thinks.

Key Concepts

1. Execution Plan: A visual or textual representation of how the DB engine executes a query (e.g., EXPLAIN ANALYZE).
2. Sargability (Search ARGument ABLE): Writing queries so the engine can use indexes instead of full table scans.
3. Partitioning: Splitting a large table into smaller, more manageable pieces (e.g., partitioning by Date).

Reading an Execution Plan

Most databases provide EXPLAIN or EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

Key terms in the output:

• Seq Scan: Full table scan (bad for large tables).
• Index Scan: Using an index to find rows (good).
• Index Only Scan: All data comes from the index (best — no table access).
• Nested Loop: For each row in one table, scan the other (good for small sets).
• Hash Join: Build a hash table from one side (good for medium sets).
• Merge Join: Sort both sides and merge (good for sorted data).

Sargability

A query is sargable if the database can use an index on the filtered column. Wrapping a column in a function makes it non-sargable:

-- Non-sargable (full table scan)
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- Sargable (uses index on order_date)
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Common non-sargable patterns:

• WHERE YEAR(date_col) = 2024 → use range instead
• WHERE UPPER(name) = 'ALICE' → use case-sensitive column or generated index
• WHERE col + 1 > 10 → use WHERE col > 9
• WHERE SUBSTRING(name, 1, 1) = 'A' → use LIKE or full-text search

Common Optimization Techniques

1. Avoid SELECT *

Fetching unnecessary columns increases I/O and memory:

-- Bad
SELECT * FROM employees WHERE department_id = 5;

-- Good
SELECT id, name, salary FROM employees WHERE department_id = 5;

2. Use EXISTS instead of COUNT for existence checks

-- Bad (counts all matching rows)
IF (SELECT COUNT(*) FROM orders WHERE user_id = 1) > 0

-- Good (stops at first match)
IF EXISTS (SELECT 1 FROM orders WHERE user_id = 1)

3. Aggregate Before Joining

To avoid double counting, aggregate data before joining:

WITH OrderTotals AS (
    SELECT order_id, SUM(amount) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT o.id, ot.total, p.status
FROM orders o
JOIN OrderTotals ot ON o.id = ot.order_id
LEFT JOIN payments p ON o.id = p.order_id;

4. Use UNION ALL instead of UNION when duplicates don’t matter

UNION ALL skips the distinct sort, making it significantly faster.

Partitioning and Sharding

Partitioning helps with data archival (drop old partitions) and query pruning (only scan relevant partitions).

SELECT * FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date <= '2024-12-31';

WITH OrderAgg AS (
    SELECT order_id, SUM(price) as total FROM OrderItems GROUP BY order_id
)
SELECT o.id, oa.total, p.status
FROM Orders o
JOIN OrderAgg oa ON o.id = oa.order_id
LEFT JOIN Payments p ON o.id = p.order_id;