MySQL indexes not used on LIKE for INT?

Question

I don't know if this is a bug or a feature, or if I'm doing something wrong. I have inherited a MySQL database with a few hundred thousand rows. This table includes the fields 'full_name' which is a VARCHAR, and 'workpack' which is an INT.

One thing this table is used for is providing an autocomplete feature when people start filling in an HTML form, and this is offered on the above fields. I noticed that when typing in a 'full_name' the autocomplete appears and updates very quickly but when typing in an integer for 'workpack' the autocomplete is slow to appear and update, to the point of being almost unusable.

Both these fields are indexed, a simplified example of the query structure is below:

SELECT distinct full_name       
FROM xx.xx
WHERE full_name LIKE 'Joe Bl%';

An EXPLAIN suggests this is using the index 'full_name' as expected.

An almost identical query for the 'workpack':

SELECT distinct workpack
FROM xx.xx
WHERE workpack LIKE '153%';

An EXPLAIN here shows it is not using the index 'workpack', even when I use FORCE INDEX.

Because the only difference I can see is that one is an INT and one a VARCHAR, I decided to experiment by creating a local copy of the table and changing the datatype of 'workpack' to VARCHAR. It worked! Perhaps not that surprising to some, but I want to know why this happens. Obviously my 'workpack' data should be stored as INT because that's what it is, but to have my autocomplete function work in a reasonable way it seems like I need to change it to VARCHAR. I realise LIKE is a string function, but is there a logical, technical reason for it to fail to use the index just because it's an INT, given that the LIKE function still works on INT?

Answer 1

The index is not suitable here because you are casting it to a string. And if your sort a list of number with their value or with their string representation, the result will be different. Take for exemple 125 and 1234. When sorting by value, you get this :

• 125
• 1234

And when sorting by string representation you get this :

• 1234
• 125

So the index is useless in your case because it is in the value order(since you field is an int) while your search would require the string order(because you are using like to find some string prefix). (Keep in mind that an index is really just a sorted list)