[Info-ingres] Using LIKE with alternate patterns

[Karl and Betty Schendel]

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

Now if it's the second one that's faster, I would be boggled.

Karl