I've got a table with 250,000 entries and a primary INT field. I'm in a situation where I need to find all the ids what start with a given set of prefix. So I tried a like:
WHERE id LIKE "123%"
But it does turn out to be pretty slow, and apparently doesn't use the index, presumably because a numerical index is useless for a string comparison function.
My next attempt was to check for a set of number ranges that are covered by the given prefix
OR id between 1230 AND 1239
OR id between 12300 AND 12399
OR id between 123000 AND 123999
OR id between 1230000 AND 1239999
#or however high up you want to check.
This method seems to be faster and uses the index. But it's a bit messier. Any other approach I could try?