Text / Document search in clickhouse

Question

I have a table with approx. 10 billion distinct rows, which has a String column with log messages. The problem is to search this column with 'like' operator to match part of text. Once result is received, then select and display the entire row. What I am trying is:

1. Create projection on the source table with message column as sorting key.
2. Create a table from materialized view named messages_mv with message column.
3. Create inverted index on the message column (messages_mv table) to use with 'like' query.
4. Once a record or multiple records have been selected from the query in step 3, run query on source table using projection with message in where clause.

This takes 4-5 seconds (together) which is a bit too much in my case. Has anyone tried this or faced similar issue and what can be a better solution. Thanks in advance.

Answer 1

1. Create projection on the source table with message column as sorting key.
2. Create a table from materialized view named messages_mv with message column.
3. Create inverted index on the message column (messages_mv table) to use with 'like' query.
4. Once a record or multiple records have been selected from the query in step 3, run query on source table using projection with message in where clause.

This works better if I use "hasToken()" instead of "like" for searching in messages. It reduces the search time to just under 1 sec. in most cases which is acceptable.