I am writing a search feature where users look up accounts by name. Currently, our SQL Queries use string matching with leading wildcards. Performance has dropped significantly as our user base grew. How can we optimize text matching?
3 answers
Standard B-Tree indexes are completely ignored by the query optimizer whenever you place a wildcard at the very beginning of your search term, such as LIKE '%keyword'. This occurs because the engine cannot utilize the index tree left-to-right, forcing a slow full table scan. If you must use leading wildcards, you should look into implementing a Full-Text Search index or a specialized trigram index instead. These structures tokenize text fields, allowing your SQL Queries to find substrings instantly.
Is there any flexibility in the user experience design that would allow you to switch from a full substring match to a trailing wildcard search instead?
For massive enterprise datasets, offloading these complex text searches entirely to an external search engine like Elasticsearch is usually the best approach.
Offloading text searches is a great architectural choice, Clara. It keeps your primary transactional database focused purely on fast CRUD operations.
Switching to a trailing wildcard like 'keyword%' makes an enormous difference. It allows the query planner to perform an index range scan, which instantly drops execution time for your SQL Queries from seconds to milliseconds.