> Ugh, that kind of sucks (i.e. having to read the entire blob into
> memory). Seems (as you imply) that a better pattern matching algorithm
> is required, and one that can work on streamed data (i.e. does not rely
> on the entire blob being read into memory).

Yep. Knuth-Morris-Pratt algorithm could do much better job.
I can demonstrate you the deficiency. There is a table "document"
which contains ~35000 records with text field description varchar(255)
that contains 100-160 chars of data.

Query:
select count(*) from document where description like <pattern>

<pattern> <time>
"%q" 0,2 s
"%a%q" 0,6 s
"%a%a%q" 1,8 s
"%a%a%a%q" 5,6 s
"%a%a%a%a%q" 14,9 s
"%a%a%a%a%a%q" 35,2 s
"%a%a%a%a%a%a%q" 74,7 s
.....

I'll probably take a look at this issue.


--
Nickolay Samofatov mailto:skidder@...