Using LIKE on numerical indexes

Databases
1

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


WHERE 
id =123
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?

2

oh wow, how did you manage to paint yourself into ~that~ corner?

nothing wrong with your “messy” solution

3

I’m doing an auto-suggest input kinda-thing.
But it’s good to know that this solution is OK. I thought maybe it wasn’t mathsy enough!

4

you could use

WHERE 123 IN ( FLOOR(id/10), FLOOR(id/100), FLOOR(id/1000)...)

but that wouldn’t use the index

5

I would not advise doing this with LIKE even if it was text. Use something like lucene to handle this – it could certainly handle this case very easily out of the box. Elasticsearch is a good option if you don’t want to get down and dirty with lucene and you’ve got somewhere to run it as well.