Is Clickhouse secondary index similar to MySQL normal index?

user_4443784 0
Viewed 1244

I am kind of confused about when to use a secondary index. I have the following code script to define a MergeTree Table, and the table has a billion rows.

create table t_mt(
 id UInt8,
 name String,
 job String,
 birthday Date,
 salary UINT8

) engine = MergeTable
primary key id
order by (id)

I would run the following aggregation query in real-time:

select job, count(1), avg(salary) 
from t_mt 
group by job 
where salary > 20000

In the above query, I have used condition filter: salary > 20000 and group by job. I would ask whether it is a good practice to define the secondary index on the salary column.

The basic question I would ask here is whether I could think the Clickhouse secondary index as MySQL normal index. That is, if I want to filter by some column, then I can create the (secondary) index on this column for query speed up.

clickhouse
2 Answers

No, MySQL use b-tree indexes which reduce random seek to O(log(N)) complexity where N is rows in the table

Clickhouse secondary indexes used another approach, it's a data skip index

When you try to execute the query like SELECT ... WHERE field [operation] values which contain field from the secondary index and the secondary index supports the compare operation applied to field, clickhouse will read secondary index granules and try to quick check could data part skip for searched values, if not, then clickhouse will read whole column granules from the data part

so, secondary indexes don't applicable for columns with high cardinality without monotone spread between data parts inside the partition

Look to https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-data_skipping-indexes for details

user_12046650

Such behaviour in clickhouse can be achieved efficiently using a materialized view (it will be populated automatically as you write rows to original table) being sorted by (salary, id). It will be much faster to query by salary than skip index.

create materialized  view t_mt_by_salary partition by toYear(birthday) order by (salary, id)
 populate  as select  id, name , job , birthday , salary from t_mt;
select * from t_mt_by_salary where salary > 20000

There is no point to have MySQL type of secondary indexes, as columnar OLAP like clickhouse is much faster than MySQL at these types of queries. Loading secondary index and doing lookups would do for O(N log N) complexity in theory, but probably not better than a full scan in practice as you hit the bottleneck with disk lookups.

Skip indexes (clickhouse secondary indexes) help if you have some rare values in your query or extra structure in data (correlation to index). E.g. let's imagine that you filter for salary >200000 but 99.9% salaries are lower than 200000 - then skip index tells you that e.g. max salary in next block is 19400 so you don't need to read this block.

On the other hand if you need to load about 5% of data, spread randomly in 8000-row granules (blocks) then probably you would need to scan almost all the granules. But you can still do very fast queries with materialized view sorted by salary.

user_36722980
Related