Re: general design issue
Date: Thu, 23 Apr 2009 21:57:31 GMT
Message-ID: <fv5Il.23789$Db2.5870_at_edtnps83>
Bob Badour wrote:
> skink wrote:
>
>> >> Bob Badour write: >> >>> Other than being non-standard, what is your objection to using GLOB >>> 'prefix*' ? >> >> i'm sorry i overlooked GLOB in the previous post. ok gave it a try on >> my sqlute and results are the same as LIKE: ~1200 ms...
>
> How many distinct items with the same prefix do you have? Presumably you
> have no more than about a couple dozen with the same prefix plus one
> character.
>
> Are the prefixes you use always the same length?
Note I tried it with a unique index because I guessed that whatever words he's storing, he stores each one only once but I would guess if the OP built his index allowing duplicates it would still use the index but maybe I'm wrong and the index needs to be unique. Perhaps he didn't build an index or perhaps he's still trying to use substr. Substr doesn't seem essential to me. Or maybe sqlite is invoking sort repeatedly because of the distinct keyword, if he puts 'explain' in front of the query, a call to 'sort' should show up, perhaps within a loop. If he wants to be more standard I don't see why he can't use word > 'xy' and word < 'xz' which seems to use an index just as GLOB does. If he wants to send his sample data privately, I'd be willing to try it, can't be that big if it's on an embedded device. Just my two cents. Received on Thu Apr 23 2009 - 23:57:31 CEST