Mysql: Using LIKE vs. = for exact string match

Question

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?

Answer 1

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.

Answer 2

This has previously been covered here on stackoverflow.

http://stackoverflow.com/questions/543580/equals-vs-like

I hope this helps.

Answer 3

In a decent DBMS (e.g., DB2 :-), the DB engine would recognize that there were no wildcard characters in the string and implicitly turn it into an "=" version. So, you'd only get a small performance hit at the start, usually negligible for any decent-sized query.