Query Sargability

Sargable is a contraction of "Search ARGument-ABLE". Sargability refers to whether a database query condition can use an index to speed up execution. Queries that are sargable allow the database engine to quickly find data using indexes, improving performance. Non-sargable queries, often caused by using functions on columns in WHERE clauses, slow down execution because they can't efficiently use indexes.

Sargability affects JOIN, WHERE, ORDER BY, GROUP BY, and HAVING clauses, but not the SELECT clause. Some databases support functional indexes, allowing functions on columns to still be indexed and thus sargable.

Why This Optimization Works

1. Index Preservationi: When you apply a function to an indexed column (like YEAR(order_date)), the database cannot use the index because it doesn't know what the function result will be until it processes each row. This forces a full table scan.

2. Wildcard Positioningi: LIKE '%@gmail.com' starts with a wildcard, so the database can't use any index prefix. LIKE 'gmail@%' can use the index starting from 'gmail@'.

3. NOT IN vs NOT EXISTSi: NOT IN often performs poorly and has issues with NULL handling. NOT EXISTS can leverage indexes and is generally more efficient.

4. Multiple OR Conditionsi: Multiple OR conditions can sometimes confuse the query optimizer. Using IN or UNION ALL can allow better index usage.

Common Sargability Killers

Function on Column

Before (Inefficient Query)

SELECT * 
FROM orders 
WHERE YEAR(order_date) = 2024;

After (Optimized Query)

SELECT * 
FROM orders 
WHERE order_date >= '2024-01-01' 
    AND order_date < '2025-01-01';

Leading Wildcard

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE email LIKE '%@gmail.com';

After (Optimized Query)

SELECT * 
FROM users 
WHERE email LIKE '%gmail.com';

NOT IN

Before (Inefficient Query)

SELECT * 
FROM customers 
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);

After (Optimized Query)

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Multiple OR

Before (Inefficient Query)

SELECT * 
FROM products 
WHERE category = 'A' 
    OR category = 'B' 
    OR category = 'C';

After (Optimized Query)

SELECT * 
FROM products 
WHERE category IN ('A', 'B', 'C');

Type Mismatch

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE user_id = "123";

After (Optimized Query)

SELECT * 
FROM users 
WHERE user_id = 123;

Sargable Operators

Operator	Sargable
=	Yes
>, <, >=, <=	Yes
BETWEEN	Yes
LIKE 'prefix%'	Yes
IS [NOT] NULL	Yes
IN	Yes
<>	Yes, but less efficient
NOT IN	Yes, but less efficient
NOT LIKE	Yes, but less efficient
LIKE '%something%	No

Best Practices

1. Keep columns in their natural form - Don't wrap indexed columns in functions
2. Use parameters instead of expressions - Helps query plan caching and reuse
3. Avoid wildcards at beginning of LIKE patterns - Prefer LIKE 'expression%'
4. Use indexed columns for filtering - Filter on columns with indexes
5. Replace multiple OR with IN or UNION - Better optimizer usage
6. Optimize JOIN conditions - Join on indexed columns, avoid functions in JOINs
7. Consider computed columns or functional indexes - If your database supports them

Performance Impact

-- Non-sargable (full table scan on 1M rows)
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- Execution: ~500ms (full scan)

-- Sargable (index seek)
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- Execution: ~5ms (index range scan)