33

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?

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
mluebke
  • 8,588
  • 7
  • 35
  • 31
  • 4
    Baring a completely braindead implementation, the cost of either flavor of string comparison is going to be dwarfed by the cost of moving data off the disk. Write out what you you really mean & get on with programming. – Sean McSomething Feb 18 '09 at 01:49
  • 3
    This has previously been covered [here](http://stackoverflow.com/questions/543580/equals-vs-like) on stackoverflow. I hope this helps. – user34867 Feb 18 '09 at 01:36
  • Thanks, I searched for this before posting but didn't see it somehow. – mluebke Feb 18 '09 at 01:39
  • What you can do is use google to search stackoverflow for you. It can sometimes come back with more relevant results. – user34867 Feb 18 '09 at 01:45

2 Answers2

34

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.

Suroot
  • 4,315
  • 1
  • 22
  • 28
10

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
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Good update, but of more relevance to this question, `LIKE` "performs matching on a per-character basis", which means the engine cannot optimize `LIKE` to `=`, so `LIKE` will almost certainly be slower. It also means the two operators behave differently depending on the collation, but that's less relevant for this question. – Jonathan Hall Jun 16 '17 at 08:11
  • @Flimzy Could you elaborate a bit? AFAIK, `=` performs a comparison (of course not matching) on a per-character basis as well, unless you specify `binary` somewhere. – Binarus Aug 27 '17 at 16:09
  • 1
    @Binarus `=`'s comparison is different for composite characters, such as `ä`. See [the manual for details](https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html). – Jonathan Hall Aug 27 '17 at 22:04
  • @Flimzy I see. Thank you and +1. I never ran into that problem because I always use utf8 as character set and utf8_bin as collation. – Binarus Aug 28 '17 at 06:21