In this lesson, you will learn why the placement of a wildcard character in a LIKE query determines whether your database can use an index or must perform a costly full table scan.
The B-Tree Index: A Sorted Directory
To understand wildcard positioning, think of a B-tree index like a physical phone book. Because names are sorted alphabetically (e.g., "Apple," "Banana," "Cherry"), if you want to find everyone whose name starts with "B," you can flip directly to the "B" section.
This is the power of a leading constant. When you write a query like:
SELECT * FROM Users WHERE Last_Name LIKE 'Smi%';
The database engine identifies that the string starts with a specific known value ("Smi"). It traverses the B-tree to the "Smi" section and retrieves the matching records efficiently.
The Leading Wildcard Problem
Now, consider what happens when you place a wildcard at the beginning of the search string:
SELECT * FROM Users WHERE Last_Name LIKE '%ith';
By starting with %, you are telling the database, "Find me anything that ends in 'ith'." Because the phone book is sorted by the first letter, the index becomes useless. "Smith," "Griffith," and "Meredith" are scattered across the entire sorted structure.
The database engine cannot skip to a specific section because it doesn't know where the matches might hide. Consequently, it is forced to abandon the index and perform a full table scan, reading every single row in the table to see if it matches your pattern. This turns a high-speed lookup into a slow, resource-intensive operation.
When Performance Matters
In large datasets, the difference between an index seek and a table scan can be measured in seconds or even minutes. While leading wildcards are sometimes necessary for specific business requirements, they bypass the optimization structures we have spent previous lessons discussing, such as index usage metrics and data model indexability. If you find your queries slowing down, check if your LIKE patterns are inadvertently "hiding" behind a leading wildcard.
Key Takeaway: Placing a wildcard at the beginning of a string prevents the database from using B-tree indexes, forcing a full table scan that degrades performance significantly.