Indexing in Clickhouse

[Raj Malhotra]

Hi all,

   Does Clickhouse support indexing over multiple fields, I read in the documentation here that MergeTrees support indexing by primary key which can also be a tuple with date column. 

   However is it possible to have indices, each index over some column and use appropriate index as per the query . 

   Thanks.

Raj

[Alex Zatelepin]

Hi, Raj!

MergeTree primary key can be an arbitrary tuple (presence of the date column is not mandatory, it just makes sense in the examples). Primary key index affects the physical structure of the table (the table consists of several parts each of which is sorted by primary key), thus there can be only one such index.

Most of the times one index is enough but there are legitimate cases where several indexes are desirable - e.g. in an ad serving platform there are advertisers and ad network sites where the ads are displayed. To be able to efficiently generate a report both for an advertiser or an ad network site you will need two indexes. One possible way to circumvent this limitation is to duplicate the data in two tables.

Another common case when one basic index is not enough is geospatial data. Here is how folks at Carto are tackling this problem in ClickHouse: https://carto.com/blog/inside/geospatial-processing-with-clickhouse