Enterprise SQL Performance Optimization Playbook

1. SARGable Query Rules

A Search Argument (SARG) enables the database query planner to leverage B-tree indexes rather than reading every page on disk.

• Rule 1: Never wrap indexed columns in functions on the left-hand side of an operator: -- ❌ WHERE CAST(order_id AS VARCHAR) = '101' -- ✅ WHERE order_id = 101
• Rule 2: Avoid leading wildcards: LIKE '%abc' causes full scans; LIKE 'abc%' uses the index.
• Rule 3: Avoid OR across different columns. An OR condition often invalidates indexes. Replace with UNION ALL.

2. Covering Indexes (INDEX ... INCLUDE)

A Covering Index contains all columns requested by a query. The engine fulfills the entire query directly from the B-tree index in RAM without making expensive secondary lookups to table data pages on disk!

High-Impact SQL Optimization Rules

• SARGable Date & String Rewrites: Replace WHERE DATE(ts) = '2026-11-10' with half-open ranges WHERE ts >= '2026-11-10' AND ts < '2026-11-11'; replace COALESCE(col, 'N/A') = 'N/A' with (col IS NULL OR col = 'N/A').
• Eliminate Correlated Subqueries in SELECT/WHERE: Correlated subqueries execute once per outer row unless the optimizer un-nests them; rewrite cleanly using CTE pre-aggregations, EXISTS, or JOIN.
• UNION ALL vs UNION: Always default to UNION ALL unless duplicate elimination across inputs is explicitly required—UNION forces an expensive cluster-wide DISTINCT sort/hash pass!