[Info-ingres] Using LIKE with alternate patterns

[Roy Hann]

Karl and Betty Schendel wrote:

>> On Nov 20, 2019, at 9:02 AM, Roy Hann <specially at processed.almost.meat> wrote:
>> 
>> II 11.0.0 (su9.us5/100)
>> 
>> Searching a temporary heap of about 1.2 million rows I am noticing a
>> massive performance difference between
>> 
>>  ... WHERE signature LIKE 'XM_LP XM_FL%'
>> 
>> and 
>> 
>>  ... WHERE signature LIKE 'XM_LP XM_FL%\|%X1_LP X1_FL%' ESCAPE '\'
>> 
>> The difference in running time is a factor of ~50. I can't persuade
>> myself I should have expected that.
>
>
> I trust it's the first one that's faster?  Makes sense to me.  The first like
> translates to an equality test without looking at the tail of the signature
> value once it matches the front.  The second like has an alternative that
> has an indefinite starting point and would require a position by position
> check.  

There must have been a transient problem with the system I was using.
Today the second one _is_ slower, but only by a factor of 9. Much more
reasonable.

> (Actually the %foo% type of pattern can be quickly searched for
> with something like the Boyer-Moore algorithm to skip multiple positions,
> but I don't know if we have that implemented as part of LIKE.)

If Ingres doesn't already use Boyer-Moore and someone were minded to
introduce something like it, Horspool's enhanced version
(Boyer-Moore-Horspool) is about 2.5 times faster still. 

Just sayin'...  :-)

Roy