SQL `LIKE` vs `=`: When Is One Faster Than the Other?

SQL 'like' vs '=' Performance: Contextual Analysis

The question of whether 'like' or '=' performs better when using SQL wildcards has been addressed in the past, but the conventional wisdom is that '=' is faster. However, it is important to consider the specific context of the query.

In the scenario where a column contains a limited number of fixed, hardcoded varchar identifiers, and the task is to select all rows matching one of them, it might appear that 'like' would have an advantage. 'Like' only needs to test a few initial characters for a match, while '=' must compare the entire string.

However, as explained in an article by John Nelson (archived at https://web.archive.org/web/20150209022016/http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx), the rules for index usage with 'like' are nuanced:

• If the filter uses '=' on an indexed field, an INDEX/CLUSTERED INDEX SEEK is likely used.
• If 'like' is used with no wildcards, index usage is similar to the case of '='.
• If 'like' is used with a wildcard at the beginning, an INDEX SCAN on a full or partial range of the index may be used.
• If 'like' is used with a string first and wildcards after, an INDEX SEEK may be used to find matching rows, which are then checked for an exact match.

Therefore, the performance of 'like' versus '=' depends on the specific query context, the location of wildcards, and the presence of indexes. In the case of a limited number of hardcoded identifiers, 'like' may indeed perform faster than '=' with a full string comparison. However, it is important to evaluate the specific use case and consider index optimization to determine the optimal approach.

The above is the detailed content of SQL `LIKE` vs `=`: When Is One Faster Than the Other?. For more information, please follow other related articles on the PHP Chinese website!