Re: [GENERAL] indexed regex select optimisation missing?

On Fri, 05 Nov 1999, Ross J. Reedstrom wrote:
> Ah, your description just tripped a memory for me from the hackers list:
>
> The behavior you describe has to do with the implementation of using an
> index for regex matching, in the presence of the USE_LOCALE configuration
> option.
>
> Internally, the condition: WHERE word~'^alongword' is converted in the
> parser(!) to:
>
> WHERE word >= 'alongword' AND word < 'alongword\377'
>
> since the index needs inequalities to be used, not matches. Now, the
> problem is the hack of tacking an octal \377 on the string to create
> the lexagraphically 'just bigger' value assumes ASCI sort order. If
> USE_LOCALE is defined, this is dropped, since we don't have a good fix
> yet, and slow correct behavior is better than fast, incorrect behavior.

just to add to my previous reply, the 'hack' I am using now is:

select key from inv_word_i where word>='window' and word<'window\372'

which matches very nearly everything in my database (actually, I limit data to
printable characters, so it should be safe), and words with my normal queries
(which are actually Zope queries, and therefore changing the actual search word
is a little non-trivial)

anyway, just a quick hack that helps performance by several orders of magnitude
if you have locale enabled (ie: are using the standard RPMs)
BTW, I assume that my databases will need requilding if I compile up a
non-locale aware version, which presents a problem currently :(

------------------------------------------------------------
Stuart Woolford,
stuartw(at)newmail(dot)net Unix Consultant.
Software Developer.
Supra Club of New Zealand.
------------------------------------------------------------