Re: general design issue
Date: Thu, 23 Apr 2009 19:16:47 -0300
Message-ID: <49f0e8d1$0$5491$9a566e8b_at_news.aliant.net>
> 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.
I suspect either a volume of data issue or the collation thing causing him problems.
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.
The range query idea is an interesting kludge. It might have to be >= 'xy' and < 'xz' for an xy prefix, though; assuming an empty string might be a valid suffix.
> 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 Fri Apr 24 2009 - 00:16:47 CEST