In this lesson, you will learn why B-tree indexes are highly efficient for specific string searches but struggle when wildcards are placed at the beginning of a search pattern.
The Anatomy of a B-tree
To understand index utilization for partial matches, think of a B-tree index like a physical telephone book. When you look up a name, you rely on the alphabetical order: A, B, C, and so on. If you want to find everyone whose name starts with "Sm...", you can jump straight to the "S" section, find "Sm," and read forward until the names change to "Sn." This is an Index Seek—it is lightning fast because the data is sorted.
The Wildcard Problem
The efficiency of a B-tree relies entirely on the left-to-right ordering of characters. When you use the LIKE operator in SQL, the database optimizer checks if it can use that sorting property.
Consider this query:
SELECT * FROM Customers WHERE LastName LIKE 'Smith%';
Because the search starts with a known character ('S'), the engine performs an Index Seek. It discards everything that doesn't start with 'S' and grabs the relevant range.
However, look at this query:
SELECT * FROM Customers WHERE LastName LIKE '%th%';
Here, the wildcard (%) is at the start. The database engine effectively says, "I cannot use the index because the match could be anywhere in the string." Since the index is sorted by the beginning of the last name, it has no way to know where "th" might be hiding inside the names. It is forced to perform an Index Scan (reading the entire index) or a Table Scan (reading the entire table), both of which are significantly slower.
Why It Matters
A trailing wildcard (e.g., 'abc%') is "sargable" (Search ARGumentable), meaning the index can be utilized. A leading wildcard (e.g., '%abc') makes the index useless for narrowing down the search, forcing the engine to examine every single row to see if it contains the pattern. When designing your indexes, always remember that the B-tree only understands order from the first character onward.
Key Takeaway: B-tree indexes only support efficient seeking for partial matches when the search pattern is anchored to the beginning of the string (the prefix).