Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using LIKE vs. = for exact string match

First off, I recognize the differences between the two:
- Like makes available the wildcards % and _
- significant trailing whitespace
- colation issues

All other things being equal, for an exact string match which is more efficient:

SELECT field WHERE 'a' = 'a'; 

Or:

SELECT field WHERE 'a' LIKE 'a'; 

Or: Is the difference so insignificant that it doesn't matter?

like image 819
mluebke Avatar asked Feb 18 '09 01:02

mluebke


People also ask

How do I use exact match in SQL?

So '%PC%' will match anything with PC in it. '%PC' will match ending in PC, and 'PC%' will match beginning with PC.

How do I match part of a string in SQL?

SQL Pattern Matching : It is used for searching a string or a sub-string to find certain character or group of characters from a string. We can use LIKE Operator of SQL to search sub-string. The LIKE operator is used with the WHERE Clause to search a pattern in string of column.

What is exact string match?

The Exact String Match comparison is a simple comparison that determines whether or not two String/String Array values match. Use the Exact String Match comparison to find exact matches for 2 values for a String identifier.

How do I find exact words in SQL?

mysql_query("SELECT * FROM products WHERE product_name LIKE '%". $search. "%'");


2 Answers

I would say that the = comparator would be faster. The lexical doesn't send the comparison to another lexical system to do general matches. Instead the engine is able to just match or move on. Our db at work has millions of rows and an = is always faster.

like image 130
Suroot Avatar answered Sep 18 '22 06:09

Suroot


In a decent DBMS, the DB engine would recognise that there were no wildcard characters in the string and implicitly turn it into a pure equality (not necessarily the same as =). So, you'd only get a small performance hit at the start, usually negligible for any decent-sized query.

However, the MySQL = operator doesn't necessarily act the way you'd expect (as a pure equality check). Specifically, it doesn't by default take into account trailing spaces for CHAR and VARCHAR data, meaning that:

SELECT age WHERE name = 'pax' 

will give you rows for 'pax', 'pax<one space>' and 'pax<a hundred spaces>'.

If you want to do a proper equality check, you use the binary keyword:

SELECT field WHERE name = binary 'pax' 

You can test this with something like:

mysql> create table people (name varchar(10));  mysql> insert into people value ('pax'); mysql> insert into people value ('pax '); mysql> insert into people value ('pax  '); mysql> insert into people value ('pax   '); mysql> insert into people value ('notpax');  mysql> select count(*) from people where name like 'pax'; 1  mysql> select count(*) from people where name = 'pax'; 4  mysql> select count(*) from people where name = binary 'pax'; 1 
like image 41
paxdiablo Avatar answered Sep 20 '22 06:09

paxdiablo