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?