MySQL Optimization, "like" vs "="

Question

I have a table with columns like this:

| Country.Number | CountryName |
| US.01          | USA     |
| US.02          | USA     |

I'd like to modify this to:

| Country | Number | CountryName |
| US      | 01     | USA     |
| US      | 02     | USA     |

Regarding optimization, is there a difference in performance if I use:

select * from mytable where country.number like "US.%"

or

select * from mytable where country = "US"

Answer 1

The later query:

select * from mytable where country = "US"

should be much faster because mySQL does not have to look for wildcards patterns unlike LIKE query. It just looks for the value that has been equalized.

Answer 2

The second is faster if there is an index on column country. MySQL has to scan less index entries to produce the result.

Answer 3

If you need to optimize, a simple = is way better than a like.

Why ?

• With an = either the string is exactly the same and it's true or it doesn't match and it's false.
• With a like, MySQL must compare the string and test if the other string match the mask, and that takes more time and needs more operations.

So for the sake of your database, use select * from mytable where country = "US".

Answer 4

Still, the performance difference may be miniscule, as mysql uses an index on "US.%". The performance degradation is mostly felt when searching for something like "%.US" (the wildcard is in front). As it then does a tablescan without using indices.

EDIT: you can look at it like this:

Mysql internally stores varchar indices like trees with first symbol being the root and branching to each next letter.

So when searching for = "US" it looks for U, then goes one down step for S and then another to make sure that is the end of the value. That's three steps.

Searching for LIKE "US.%" it looks again for U, then S, then . and then stops searching and displays the results - thats also three steps only as it cares not whether the value terminated there.

EDIT2: I'm in no way promoting such database denormalization, I just wanted to attract your attention that this matter may not be as straightforward as it seems at first glance.

Answer 5

Not technically an answer to the question.. but...I would understand them to be close enough in speed for it not to (usually) matter - thus using "=" would be better as it displays the intent in a more obvious way.

Answer 6

why dont you just make country_id a tinyint unsigned and have an iso_code varchar(3) column which is unique ? (saves you from all the BS)