Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Transactions and Performance

SQL sargable predicates

Concept guide to sargable SQL predicates: why wrapping a column in a function stops index use, and how to rewrite WHERE conditions so indexes help.

A predicate is sargable (from "search ARGument ABLE") when the database can use an index to jump straight to the matching rows instead of testing every row. The idea is simple: an index is sorted by the stored column value, so it can only help when the condition compares that raw, unchanged column to a value.

The moment you wrap the column in a function or do arithmetic on it, the database would have to compute the expression for every row before it could compare, and the index, which stores the original values, is no use. The fix is usually to move the work to the other side of the comparison, onto the constant.

SQL
-- Not sargable: the function hides the column
WHERE strftime('%Y', ordered_at) = '2024'

-- Sargable: the column is compared as it is stored
WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'

Seeing the difference

The query plan tells you whether an index was used. SEARCH means the index narrowed the rows down; SCAN means every row was read. (The exact wording of the plan can vary slightly between SQLite versions.)

SQL Function on the column vs a range ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN orders USING COVERING INDEX: SQLite reads the whole index from start to end (it is narrower than the table, but still every entry). The second says SEARCH ... (ordered_at>? AND ordered_at<?): it jumps into the index and reads only the range. Both queries return the same rows; only the second lets the index do the work. The half-open range (>= start, < next start) also handles timestamps such as '2024-12-31 23:59:59' correctly, which BETWEEN '2024-01-01' AND '2024-12-31' would miss.

SQL Moving arithmetic to the constant side ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Common rewrites

Not sargable Sargable rewrite
WHERE YEAR(created) = 2024 WHERE created >= '2024-01-01' AND created < '2025-01-01'
WHERE LOWER(email) = '[email protected]' store emails normalized, or create an index on LOWER(email)
WHERE price * 1.2 > 100 WHERE price > 100 / 1.2
WHERE name LIKE '%son' WHERE name LIKE 'son%' (prefix) or full-text search
WHERE col + 0 = 5 WHERE col = 5
WHERE ISNULL(col, 0) = 0 WHERE col = 0 OR col IS NULL
WHERE CAST(id AS TEXT) = '42' WHERE id = 42

A leading wildcard cannot use an ordinary index, because the index is sorted from the first character. A pattern with a fixed start can. In SQLite, LIKE only uses an index when the column has a matching collation or the case_sensitive_like setting is on; GLOB is easier to optimize.

Indexes on expressions

Sometimes you really need the function. Several databases can index the expression itself, and then the same expression in WHERE is sargable again.

SQL An index on an expression ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL
-- PostgreSQL, SQLite, Oracle (function-based index)
CREATE INDEX idx_customers_lower_email ON customers (LOWER(email));

-- MySQL 8.0.13+ (functional key part needs extra parentheses)
CREATE INDEX idx_customers_lower_email ON customers ((LOWER(email)));

-- SQL Server: use a computed column, then index it
ALTER TABLE customers ADD email_lower AS LOWER(email);
CREATE INDEX idx_customers_lower_email ON customers (email_lower);

The expression in the query must match the indexed expression exactly.

Other things that defeat an index

  • Implicit type conversion. Comparing a text column to a number (WHERE phone = 5551234) can force a conversion of every stored value in some databases. Compare with the column's type.
  • OR across different columns. a = 1 OR b = 2 needs one index per branch; without them the database scans.
  • Low selectivity. A condition that matches most rows (such as status <> 'archived') is faster to scan; the optimizer is right to ignore the index.
  • Leading column of a composite index. An index on (country, city) helps WHERE country = 'DE', but not WHERE city = 'Berlin' alone.
  • SELECT *. Not a sargability problem, but it stops an index from being "covering".

Dialect notes

The principle is identical in every database. What differs is how well each optimizer rewrites simple cases for you. Some can rewrite col + 0 = 5 or fold constants like 1000000 / 12, but none can undo a function such as YEAR(col) or CAST in general. SQL Server and Oracle report the problem in their plans as a scan or an index scan instead of a seek; PostgreSQL shows Seq Scan instead of Index Scan.

Usage notes

  • Check the plan, not the theory. Run the query plan before and after a rewrite; see EXPLAIN.
  • Keep columns bare. Put calculations, casts and function calls on the constant side of the comparison whenever you can.
  • Use half-open date ranges instead of formatting or extracting date parts.
  • Do not over-index. Every index slows writes and uses space; add one for queries that matter.
  • Small tables scan fine. With a few hundred rows, a scan can be as fast as an index; the problem shows at scale.