Re: general design issue

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 23 Apr 2009 19:16:47 -0300
Message-ID: <49f0e8d1$0$5491$9a566e8b_at_news.aliant.net>



paul c wrote:

> 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.

They are prefix queries. Even if he does not allow duplicates, he could still have gigabytes of index for a given prefix because all the remaining characters might be different.

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 Thu Apr 23 2009 - 17:16:47 CDT

Original text of this message