`LIKE` vs. `=` in SQL: When is Wildcard Matching More Efficient?

The Performance of 'LIKE' vs '=' in SQL When Using Wildcards

It is generally believed that '=' is more efficient than 'LIKE' when using wildcards in SQL queries. However, this may not always be the case.

Consider the following example:

SELECT * FROM table WHERE value LIKE 'abc%'

SELECT * FROM table WHERE value = 'abcdefghijklmn'

In this case, 'LIKE' only needs to compare the first three characters to find a match. '=' must compare the entire string. Therefore, it might seem that 'LIKE' would have an advantage in this scenario.

However, index usage can affect the performance of 'LIKE' queries. As stated in this article by Jonathan Nelson at MyITForum:

• If your filter criteria uses '=' and the field is indexed, it will likely use an INDEX/CLUSTERED INDEX SEEK.
• If your filter criteria uses 'LIKE' with no wildcards, it is about as likely to use the index as '='.
• If your filter criteria uses 'LIKE' with a wildcard at the beginning, it is much less likely to use the index.
• However, if your filter criteria uses 'LIKE' with a STRING FIRST and has wildcards somewhere AFTER that, SQL may use an INDEX SEEK to find rows that have the same first starting characters and then look through those rows for an exact match.

Therefore, the performance of 'LIKE' vs '=' queries with wildcards depends on various factors, including the index usage, the location of the wildcards, and the specific SQL engine being used.

The above is the detailed content of `LIKE` vs. `=` in SQL: When is Wildcard Matching More Efficient?. For more information, please follow other related articles on the PHP Chinese website!