Intermediate LevelSQL & MySQL
What is a Sargable Query (Search Argument Able) and why do functions break index usage?
❓ Interview Prompt: Explain SARGable queries (Search Argument Able), non-sargable patterns (wrapping indexed columns in functions, wildcard prefixes), and query refactoring.
A query is SARGable (Search Argument Able) if the database engine can utilize B-Tree indexes to execute range scans or seek lookups:
Non-SARGable Anti-Patterns (Index Bypassing):
•
Wrapping columns in functions: 'WHERE YEAR(created_at) = 2026' (Non-SARGable!) $\rightarrow$ Refactor to: 'WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'' (SARGable!).
•
Performing arithmetic on columns: 'WHERE price * 0.9 > 100' $\rightarrow$ Refactor to: 'WHERE price > 100 / 0.9'.
Code Implementation Example
sql
-- Non-SARGable (Bypasses B-Tree Index):
-- SELECT * FROM orders WHERE DATE(created_at) = '2026-01-15';
-- SARGable Refactored Version (Uses B-Tree Index):
SELECT * FROM orders
WHERE created_at >= '2026-01-15 00:00:00'
AND created_at <= '2026-01-15 23:59:59';💡Refactoring non-sargable date queries to enable B-Tree index utilization.
🎯 Interview Key Takeaway
Avoid wrapping indexed columns inside functions to ensure queries remain SARGable and utilize B-Tree indexes.
#SQL#SARGable#Performance#Indexes#Intermediate
Back to all SQL & MySQL Questions→