Q28: What makes a query SARGable, and what invalidates index usage?
SARGable stands for Search Argument Able. A query is SARGable if the database engine can leverage an index to speed up execution.
Anti-Patterns That Invalidate Index Usage
- Wrapping indexed columns in functions:
WHERE YEAR(created_at) = 2026(Not SARGable). - Wildcards at the start of LIKE patterns:
WHERE name LIKE '%smith'(Not SARGable). - Implicit Type Conversion: Comparing string columns against integer values directly.
- Arithmetic on indexed columns:
WHERE salary * 12 > 100000(Not SARGable).
Converting Non-SARGable Queries into SARGable Queries
-- BAD (Non-SARGable): Function forces full table scan
SELECT * FROM orders WHERE DATE(order_timestamp) = '2026-05-10';
-- GOOD (SARGable): Range scan uses index efficiently
SELECT * FROM orders
WHERE order_timestamp >= '2026-05-10 00:00:00'
AND order_timestamp < '2026-05-11 00:00:00';💡 Replacing function calls with explicit range conditions enables index seeks across range values.
Key Takeaways
- Avoid wrapping indexed columns in scalar functions within
WHEREclauses. - Use clear range bounds instead of function transformations to keep queries SARGable.