SQL-Engineering-Handbook

SARGability and Index Usage

Module 16 · Query Optimization Home · Engineering Guide · Cheatsheet · Rewrite Cookbook

Lesson 03 of 12 · ← EXPLAIN · Next: Joins → · SQL Lab · Lab Benchmark

Introduction

An index doesn’t help just because it exists — it has to be usable for the specific way a predicate is written. This lesson covers the single most common reason a “properly indexed” production query still runs a full table scan: the predicate isn’t SARGable.

Learning Objectives

Why This Exists

Teams routinely add an index, watch the query stay slow, and conclude “indexes don’t work here” — when the real problem is that the WHERE clause wraps the indexed column in a function or implicit conversion, making the index invisible to the optimizer. This is the highest-leverage, most commonly misunderstood topic in practical query tuning.

Business Motivation

An HR system needs “everyone hired in 2023”:

-- Looks reasonable. Is not SARGable.
SELECT emp_name, hire_date
FROM employes
WHERE YEAR(hire_date) = 2023;

Even with a plain index on hire_date, this query can’t use it as a range scan — the engine must compute YEAR(hire_date) for every row before it can compare against 2023, which means reading every row via a full table scan. (An expression/functional index built specifically on YEAR(hire_date) could restore index usage on some engines — MySQL 8+ and PostgreSQL support functional indexes — but that’s a deliberate, narrow fix for one expression, not a substitute for writing SARGable predicates in the first place.)

What “SARGable” Means

SARGable vs Non-SARGable

A predicate is SARGable when the engine can use an index to jump directly to matching rows, instead of having to evaluate an expression against every row first. The rule of thumb: the indexed column must appear alone, on one side of the comparison, with nothing applied to it.

Non-SARGable SARGable rewrite
WHERE YEAR(hire_date) = 2023 WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01'
WHERE UPPER(emp_name) = 'AMMAR' WHERE emp_name = 'Ammar' (or a case-insensitive collation/index)
WHERE dept_id + 1 = 5 WHERE dept_id = 4
WHERE emp_name LIKE '%mmar' WHERE emp_name LIKE 'Ammar%' (leading wildcard defeats standard B-tree indexes)
WHERE CAST(dept_id AS VARCHAR) = '4' WHERE dept_id = 4 (avoid implicit/explicit type conversion on the column)

The pattern in every “non-SARGable” row: something is applied to the column itself. The pattern in every fix: the column is left bare, and any transformation happens to the constant instead.

Composite Indexes and Column Order

An index on (dept_id, hire_date) supports:

But does not efficiently support:

Column order in a composite index should generally put the highest-selectivity, most frequently equality-filtered column first.

Selectivity

Selectivity = how much a predicate narrows down the row set. dept_id (a handful of distinct values across millions of rows) is low-selectivity; an index on it alone helps far less than an index on a near-unique column like emp_id. Extremely low-selectivity columns (e.g., a boolean flag with 90% TRUE) often aren’t worth indexing at all — the optimizer may correctly choose a sequential scan over such an index anyway.

Covering Indexes

Covering Index

Index Usage Decision Flow

flowchart TD
    A[Predicate in WHERE/JOIN] --> B{Function or cast on column?}
    B -->|Yes| C[Non-SARGable — rewrite predicate]
    B -->|No| D{Leading wildcard in LIKE?}
    D -->|Yes| E[Non-SARGable — consider full-text/trigram index]
    D -->|No| F{Matching index exists?}
    F -->|No| G[Full scan: Seq Scan/type ALL — add or redesign index]
    F -->|Yes| H{All SELECT columns in index?}
    H -->|Yes| I["Index-only access: Index Only Scan (PG) / Using index (MySQL) — optimal"]
    H -->|No| J[Index lookup + table read: Index Scan (PG) / ref,range (MySQL)]
    C --> K[Rewrite and re-check plan]
    E --> K
    G --> K

A covering index includes every column a query needs, so the engine can answer entirely from the index without a separate lookup into the table (an “Index Only Scan” in PostgreSQL’s EXPLAIN output, or Extra: Using index in MySQL’s — see Lesson 02):

-- Index: (dept_id, emp_name, hire_date)
SELECT emp_name, hire_date
FROM employes
WHERE dept_id = 4;

Because emp_name and hire_date are both present in the index, the engine never has to visit the underlying table row.

Engineering Notes

MySQL / PostgreSQL / SQL Server Notes

Common Mistakes

Anti-patterns

Interview Questions

Edge Cases

Troubleshooting Guidance

Scalability Considerations

Every index added is write overhead on every future INSERT/UPDATE/ DELETE that touches indexed columns. At high write throughput, indexing decisions are a genuine tradeoff against write latency — audit existing indexes for actual read usage (most engines expose index-usage statistics) before adding new ones, rather than only ever adding.

Additional Dialect Notes

Summary

An index is a promise, not a guarantee — the WHERE clause has to be written so the optimizer can actually keep that promise. SARGability is the single check to run first whenever “I added an index and it didn’t help.”

Practice Challenges

  1. Rewrite WHERE dept_id * 1 = 4 to be SARGable (trick question — spot why it’s already effectively non-SARGable and fix it).
  2. Given an index on (location_id, dept_name), write one query that uses it efficiently and one that cannot, and explain why for each.

Real Company Usage

Production Applications

These scenarios are illustrative case studies inspired by common production failure modes; specific figures are illustrative unless an external source is cited in PRODUCTION_INCIDENTS.md. See its disclaimer for the full explanation.

Performance Notes

Optimization Notes

Interview Insight

SARGability questions test whether you understand why indexes fail, not just when to add them. Strong answers rewrite the predicate, explain the leftmost-prefix rule for composite indexes, and mention covering indexes as the next optimization step. Weak answers stop at “add an index on that column.” Interviewers also probe leading-wildcard LIKE patterns — know that standard B-tree indexes cannot help %value searches.

Further Experiments

  1. Create an index on hire_date, run the non-SARGable YEAR(hire_date) = 2023 query with EXPLAIN, then rewrite to a date range and compare plans.
  2. Build a composite index (dept_id, hire_date) and test three queries: filter on dept_id alone, filter on both columns, and filter on hire_date alone — observe the leftmost-prefix rule.
  3. Complete the SARGability Benchmark — measure the function-on-column → range rewrite → covering index progression.

Continue Learning

02_EXPLAIN · 04_Join Optimization · 06_Anti-Patterns

Further Reading

This site is open source. Improve this page.