Is there good way to support English fulltext index

[wubx]

Why we need fulltest index ：

1. In APM a lot of query like ： where url like "%/user/%"
2. The Game system of the service a lot query like : where action like "%win%"
3. Sample fulltext search replace Es

Clickhouse support fulltext index use :

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

Stores a Bloom filter that contains all ngrams from a block of data. Works only with datatypes: String, FixedString and Map. Can be used for optimization of EQUALS, LIKE and IN expressions.

n — ngram size,
size_of_bloom_filter_in_bytes — Bloom filter size in bytes (you can use large values here, for example, 256 or 512, because it can be compressed well).
number_of_hash_functions — The number of hash functions used in the Bloom filter.
random_seed — The seed for Bloom filter hash functions.

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

The same as ngrambf_v1, but stores tokens instead of ngrams. Tokens are sequences separated by non-alphanumeric characters.

[wubx]

@junli1026 @PsiACE @zhyass
Do you have a good way to support it ?

[junli1026]

Yes, it is doable. For example, with n — ngram size is 3, a string of "hello" will be spilted into ["hel", "ell", "llo"], and all the three splits will have its own bloom filter.
When we search with query name like 'abcdefg", we do the same splitting and check every bloom filter. If any of sub-string not exist in bloom filter, then we skip the data scan.

Correct me if I am wrong, didn't read the source code. The info is from this:
https://www.instana.com/blog/improve-query-performance-with-clickhouse-data-skipping-index/

[wubx]

I think this a good way .

[sunisdown]

the skipping index don't support queries like "%/user/%", a search engine should be a better choice for APM usage(search log)
my mistake, seems that the skipping index can optimize queries like %/user/%