Subject How does it do this...?
Author Benny Schaich

I've been thinking about this for years and as it came up again recently
I'd like to ask it here:
Together with some other people I've been trying out IB's LIKE statement
and we found that it is unlikely fast.
Now I know that there are possibilities to trick a LIKE as long as it is
something like:

Of course an optimizer could redo this and make it use an index (anybody
knowing if it does this?).

But at least at the point where we have a LIKE(%ABC%) this should be
impossible and slow down the request a lot.

To make it even harder, we put some text into blob fields and parsed
20,000 of those with a LIKE statement. IB always came back in less then
a second. We even restarted the machine to be sure we do not have
everything in RAM.

From an architectural point of view I'd like to know how that works. Did
we trick ourselves somehow? Of course the database was generated, so
large parts had the same input. I know about IB's Prefix Compression of
Indexes and how it's indexes are structured, but all that should be
useless with a (%ABC%) Parameter.