Re: [GENERAL] indexed regex select optimisation missing?

On Thu, 04 Nov 1999, Gene Selkov, Jr. wrote:
> > select key from inv_word_i where word='whatever' order by count desc ;
> >
> > and this is fast, however, if I use:
> >
> > select key from inv_word_i where word~'^whatever.*' order by count desc ;
> >
> > it is very slow.
>
> Did you try '^whatever' instead of '^whatever.*'? Based on common
> sense, the former should be much faster than the latter, which would
> match any cahracter any number of times, unless the regexp is
> optimized to avoid that.

unfortunately '^whatever.*' is what I'm trying to locate (ie: all words
starting with whatever, but with nay trailing text), the problem seems to be in
the termination of the index scan, not in the actual regex match (which actually
seems very good, speed wise..) otherwise I could just use ='whatever', which
runs very very fast.

I've had one hint that I need to build with --enable-locale, which I have to
look into, so far I've just been using the redhat RPMs, so have not actually
delved the build options, but I would have thought this would be a safe option
everywhere (but thinking about it, character order is locale specific, so maybe
it's not.. I'll look, if this is the case, enable-locale is a VERY important
option for text searching, perhaps should be added the the section 4.x in the
FAQ where the regex optimisation is mentioned?)

 >
> --Gene
--
------------------------------------------------------------
Stuart Woolford, stuartw@newmail.net
Unix Consultant.
Software Developer.
Supra Club of New Zealand.
------------------------------------------------------------