# Using LIKE on numerical indexes

• Jul 21, 2014, 07:17
hessodreamy
Using LIKE on numerical indexes
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:
Code:

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
Code:

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?
• Jul 21, 2014, 08:19
r937
Quote:

Originally Posted by hessodreamy
I'm in a situation where I need to find all the ids what start with a given set of prefix.

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

nothing wrong with your "messy" solution
• Jul 21, 2014, 08:23
hessodreamy
Quote:

Originally Posted by r937
oh wow, how did you manage to paint yourself into ~that~ corner?

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!
• Jul 21, 2014, 08:36
r937
Quote:

Originally Posted by hessodreamy
I thought maybe it wasn't mathsy enough!

you could use
Code:

WHERE 123 IN ( FLOOR(id/10), FLOOR(id/100), FLOOR(id/1000)...)
but that wouldn't use the index
• Jul 21, 2014, 13:52
wwb_99
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.