A practical introduction to primary indexes in ClickHouse

10 rows in set. Elapsed: 0.022 sec.
# highlight-next-line
Processed 8.87 million rows,
70.45 MB (398.53 million rows/s., 3.17 GB/s.)
```

ClickHouse client's result output indicates that ClickHouse executed a full table scan! Each single row of the 8.87 million rows of our table was streamed into ClickHouse. That doesn't scale.

To make this (way) more efficient and (much) faster, we need to use a table with a appropriate primary key. This will allow ClickHouse to automatically (based on the primary key's columns) create a sparse primary index which can then be used to significantly speed up the execution of our example query.

## ClickHouse index design[​](#clickhouse-index-design "Direct link to ClickHouse index design")

### An index design for massive data scales[​](#an-index-design-for-massive-data-scales "Direct link to An index design for massive data scales")

In traditional relational database management systems, the primary index would contain one entry per table row. This would result in the primary index containing 8.87 million entries for our data set. Such an index allows the fast location of specific rows, resulting in high efficiency for lookup queries and point updates. Searching an entry in a `B(+)-Tree` data structure has an average time complexity of `O(log n)`; more precisely, `log_b n = log_2 n / log_2 b` where `b` is the branching factor of the `B(+)-Tree` and `n` is the number of indexed rows. Because `b` is typically between several hundred and several thousand, `B(+)-Trees` are very shallow structures, and few disk-seeks are required to locate records. With 8.87 million rows and a branching factor of 1000, 2.3 disk seeks are needed on average. This capability comes at a cost: additional disk and memory overheads, higher insertion costs when adding new rows to the table and entries to the index, and sometimes rebalancing of the B-Tree.

Considering the challenges associated with B-Tree indexes, table engines in ClickHouse utilise a different approach. The ClickHouse [MergeTree Engine Family](/docs/engines/table-engines/mergetree-family) has been designed and optimized to handle massive data volumes. These tables are designed to receive millions of row inserts per second and store very large (100s of Petabytes) volumes of data. Data is quickly written to a table [part by part](/docs/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage), with rules applied for merging the parts in the background. In ClickHouse each part has its own primary index. When parts are merged, then the merged part's primary indexes are also merged. At the very large scale that ClickHouse is designed for, it is paramount to be very disk and memory efficient. Therefore, instead of indexing every row, the primary index for a part has one index entry (known as a 'mark') per group of rows (called 'granule') - this technique is called **sparse index**.

Sparse indexing is possible because ClickHouse is storing the rows for a part on disk ordered by the primary key columns. Instead of directly locating single rows (like a B-Tree based index), the sparse primary index allows it to quickly (via a binary search over index entries) identify groups of rows that could possibly match the query. The located groups of potentially matching rows (granules) are then in parallel streamed into the ClickHouse engine in order to find the matches. This index design allows for the primary index to be small (it can, and must, completely fit into the main memory), whilst still significantly speeding up query execution times: especially for range queries that are typical in data analytics use cases.

The following illustrates in detail how ClickHouse is building and using its sparse primary index. Later on in the article, we will discuss some best practices for choosing, removing, and ordering the table columns that are used to build the index (primary key columns).

### A table with a primary key[​](#a-table-with-a-primary-key "Direct link to A table with a primary key")

Create a table that has a compound primary key with key columns UserID and URL:

```
CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
-- highlight-next-line
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity_bytes = 0, compress_primary_key = 0;
```

DDL Statement Details

In order to simplify the discussions later on in this guide, as well as make the diagrams and results reproducible, the DDL statement:

- Specifies a compound sorting key for the table via an `ORDER BY` clause.- Explicitly controls how many index entries the primary index will have through the settings:
    - `index_granularity`: explicitly set to its default value of 8192. This means that for each group of 8192 rows, the primary index will have one index entry. For example, if the table contains 16384 rows, the index will have two index entries.- `index_granularity_bytes`: set to 0 in order to disable [adaptive index granularity](https://clickhouse.com/docs/whats-new/changelog/2019/#experimental-features-1). Adaptive index granularity means that ClickHouse automatically creates one index entry for a group of n rows if either of these are true:
        - If `n` is less than 8192 and the size of the combined row data for that `n` rows is larger than or equal to 10 MB (the default value for `index_granularity_bytes`).- If the combined row data size for `n` rows is less than 10 MB but `n` is 8192.- `compress_primary_key`: set to 0 to disable [compression of the primary index](https://github.com/ClickHouse/ClickHouse/issues/34437). This will allow us to optionally inspect its contents later.

The primary key in the DDL statement above causes the creation of the primary index based on the two specified key columns.

  

Next insert the data: