What is sargability, and why does the database not use my index?
What is sargability, and why does the database not use my index?
A predicate is SARGable (Search ARGument able) if the engine can use an index to seek directly to matching rows. Most "my index is ignored" problems come from a predicate that is not.
| Problem | Slow | Fast |
|---|---|---|
| Function on the column | WHERE YEAR(created_at) = 2026 |
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' |
| Arithmetic on the column | WHERE price * 1.2 > 100 |
WHERE price > 100 / 1.2 |
| Leading wildcard | WHERE name LIKE '%son' |
prefix LIKE 'son%', or trigram / full-text index |
| Implicit type conversion | WHERE varchar_col = 12345 |
WHERE varchar_col = '12345' |
COALESCE / ISNULL on column |
WHERE COALESCE(status, 'new') = 'new' |
WHERE status = 'new' OR status IS NULL |
OR across columns |
WHERE a = 1 OR b = 2 |
UNION ALL of two indexed queries, or index merge |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.