I keep seeing senior developers flag the use of leading wildcards during code reviews. Can someone explain why using a leading wildcard conflicts so heavily with the best practices for writing efficient SQL queries? Is there any indexing strategy that can mitigate this issue?
3 answers
A leading wildcard, such as using LIKE percent-term, completely destroys performance because B-Tree indexes are sorted from left to right. When you hide the beginning of the string, the database engine cannot perform an Index Seek to find matches; it is forced to read every single row in the index or table, resulting in a costly Full Table Scan. To stick to the best practices for writing efficient SQL queries when pattern matching, always use trailing wildcards instead, or implement a dedicated Full-Text Search index if substring matching is absolutely required.
That makes perfect sense for standard B-Tree indexing limitations, but what if we are forced to stick to relational DBs and cannot spin up an external search engine? Is a trigram index a viable workaround?
Using a leading wildcard guarantees a full table scan. If you need efficient lookups, rethink your schema or use trailing wildcards so the engine can utilize standard index trees.
Diana is totally right. We changed our search inputs to disallow front wildcards, and our server CPU utilization instantly dropped from ninety percent to a stable five percent.
Philip, yes it is. Trigram indexes break strings down into three-character chunks, allowing the engine to index the middle of strings. It works wonders for wildcard queries, though it does increase your write overhead and storage footprint.