CODEWITHSUNDEEP
clickhouse
YNX
YNX

Reputation: 625

How to create index for clickhouse?

I have about 5e9 (15e4 records per hour) records stored in clickhouse. The records is time-sequential. When I run some query with a time_range of 10 minutes:

select * from table_name where tag1='xxx' and 'tag2'='xxx' and server_time > '2020-01-01 00:00:00' and server_time < '2020-01-01 00:10:00'

It stucked. I didn't create a index. But the clickhouse index from document seems different from B-Tree index in MongoDB. So how should I create the index? Further more can you make a easy explaination?

Upvotes: 0

Views: 3002

Answers (2)

bfday
bfday

Reputation: 155

You need Data Skipping Indices. Check this

  1. https://clickhouse.com/docs/en/guides/improving-query-performance/skipping-indexes/
  2. https://clickhouse.com/docs/en/sql-reference/statements/alter/skipping-index
  3. https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/

Upvotes: 0

Andrei Koch
Andrei Koch

Reputation: 1140

What table engine do you use? The main table engine for storing the data in Clickhouse is MergeTree, where some sort of index ("sparse index") is provided out-of-box.

In your case you can create table like this:

CREATE TABLE table_name(
  server_time DateTime,
  tag1 String,
  tag2 String,
  ...
) ENGINE = MergeTree() ORDER BY time

Part ORDER BY time will do the job for you: now all of the requests with filter by server_time will use the index.

You can read more about MergeTree and index at official documentation: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#primary-keys-and-indexes-in-queries

Upvotes: 1

Related Questions