Do wildcards take longer to run?

Impact of Wildcards on Database Search Performance

Wildcards, symbols that represent a single or multiple characters, are often used in database searches to broaden the scope of results. However, employing wildcards can come at a cost in terms of performance.

The Performance Penalty of Wildcards

Trailing wildcards (e.g., "%SEARCH TERM%") incur a significant performance penalty compared to precise logical operators (e.g., "=" or "LIKE"). This is because wildcards increase the range of potential matches the database must evaluate.

When a wildcard is used, the search algorithm must consider all possible matches that satisfy the wildcard pattern. This exhaustive evaluation leads to a longer processing time compared to searches that use specific values.

Reasons for the Performance Penalty

1. Larger Index Ranges: Wildcards expand the index ranges that the database must scan, increasing the number of index pages to be processed.

2. Frequent Backtracking: Wildcard searches often require the database to backtrack, revisiting previously read rows to verify matches that are not immediately obvious.

3. Limited Index Optimization: Index optimizations, which speed up searches based on specific values, are less effective with wildcards.

Best Practices to Mitigate Performance Impact

To minimize the performance impact of wildcards, consider the following best practices:

• Use Specific Values When Possible: For precise matches, use logical operators (e.g., "=" or "LIKE") instead of wildcards.

• Trailing Wildcards Should Be Avoided: Use trailing wildcards (e.g., "%SEARCH TERM%") only when necessary.

• Use Leading Wildcards Sparingly: Leading wildcards (e.g., "SEARCH TERM%") should be used judiciously, as they significantly increase the number of potential matches.

• Consider Partial Index Scans: Partial index scans can be used to limit the range of index pages that need to be scanned when using trailing wildcards.

Conclusion

While wildcards can be a powerful tool for broadening search results, their use comes with a potential performance cost. By understanding the reasons for the performance penalty and adopting best practices, database administrators can mitigate the impact and ensure efficient search operations.