hessodreamy — 2014-07-21T10:17:11-04:00 — #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
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?
r937 — 2014-07-21T11:19:59-04:00 — #2
oh wow, how did you manage to paint yourself into ~that~ corner?
nothing wrong with your "messy" solution
hessodreamy — 2014-07-21T11:23:03-04:00 — #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!
r937 — 2014-07-21T11:36:58-04:00 — #4
I thought maybe it wasn't mathsy enough!
you could use
WHERE 123 IN ( FLOOR(id/10), FLOOR(id/100), FLOOR(id/1000)...)
but that wouldn't use the index
wwb_99 — 2014-07-21T16:52:21-04:00 — #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.