Lesson 7 of 11 · 24 min
When indexes don't help
By the end of this lesson you can recognise conditions an index can't use (non-sargable predicates), rewrite them so it can, and create an expression index when the expression itself is what you query.
A B-tree on email stores email values in sorted order. It can answer questions about email directly: equal to, greater than, between, starting with. It knows nothing about lower(email), email || 'x' or length(email): those are different values, sorted differently. A condition that compares the bare indexed column with something constant is called sargable (from Search ARGument ABLE): the index can use it as a search key. Wrap the column in a function or expression and the condition stops being sargable.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons