MySQL indexes are not used for LIKE for INT?
I don't know if this is a bug or a function, or if I am doing something wrong. I inherited a MySQL database with several hundred thousand rows. This table includes fields "full_name" which are VARCHAR and "workpack" which is INT.
One thing this table is used for is the autocomplete feature when people start filling out the HTML form and this is suggested in the fields above. I noticed that when entering "full_name", autocomplete appears and updates very quickly, but when entering an integer for a "work package", autocomplete appears slowly and updates to the point where it is almost unusable.
Both of 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%';
EXPLAIN assumes this is using the "full_name" index as expected.
Almost identical request for a "work package":
SELECT distinct workpack
FROM xx.xx
WHERE workpack LIKE '153%';
EXPLAIN shows that it does not use the index working package even when I use FORCE INDEX.
Because the only difference I see is that one is INT and one is VARCHAR, I decided to experiment by creating a local copy of the table and changing the data type "workpack" to VARCHAR. It worked! This may not be surprising to some, but I want to know why this is happening. Obviously my "workpack" data should be stored as INTs because that's what it is, but for my autocomplete function to work reasonably I feel like I need to change it to VARCHAR. I understand that LIKE is a string function, but is there a logical, technical reason why it cannot use an index just because it is INT, given that the LIKE function still works on INTs?
source to share
The index is not appropriate here because you are passing it to a string. And if yours sort a list of numbers with their value or with their string representation, the result is different. Take example 125 and 1234. When sorting by value, you get this:
- 125
- 1234
And when sorting by string representations, you get this:
- 1234
- 125
So the index is useless in your case because it is in value order (since the field is an int), while your search will require string order (because you are using a like
string prefix to find). (Keep in mind that the index is really just a sorted list)
source to share
An index is essentially a sorted data structure that displays a value column to the row containing it. Therefore, it can only be used when your query is using an actual value and not some kind of conversion (like conversion int
to varchar
).
One neat solution to such problems (available as of MySQL 5.7.6) is to add an auto-generated column for the sole purpose of indexing it and using it in such queries. This may waste some space (since you are holding two copies of the same data - the original data and the computed column), but if your application uses this query heavily, it might be worth it:
CREATE TABLE xx.xx (
full_name VARCHAR(100),
workpack INT,
workpack_str CHAR(20) AS CAST(workpack AS CHAR) STORED,
INDEX(full_name),
INDEX workpack_str
)
source to share