Part 138: Common Query Antipatterns & SARGability

A query is SARGable (Search Argument Able) if the engine can utilize an index to find the data.

1. The Function on Indexed Column Antipattern

Wrapping an indexed column in a function invalidates the index:

-- ❌ NOT SARGable (Full table scan! Engine must run LOWER() on every row):
WHERE LOWER(email) = 'alex@devify.com'

--  SARGable:
WHERE email = 'alex@devify.com'

-- ❌ NOT SARGable:
WHERE EXTRACT(YEAR FROM created_at) = 2024

--  SARGable (Uses index range scan!):
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

Key Takeaways

• Never wrap indexed columns in functions within WHERE clauses.

In Part 139, we explore Database Statistics, Histogram Buckets & The Optimizer!