A practical introduction to primary indexes in ClickHouse

10 rows in set. Elapsed: 0.026 sec.
# highlight-next-line
Processed 335.87 thousand rows,
13.54 MB (12.91 million rows/s., 520.38 MB/s.)
```

Because effectively the implicitly created table (and its primary index) backing the materialized view is identical to the [secondary table that we created explicitly](/docs/guides/best-practices/sparse-primary-indexes#option-1-secondary-tables), the query is executed in the same effective way as with the explicitly created table.

The corresponding trace log in the ClickHouse server log file confirms that ClickHouse is running binary search over the index marks:

```
...Executor): Key condition: (column 0 in ['http://public_search',
                                           'http://public_search'])
# highlight-next-line
...Executor): Running binary search on index range ...
...
...Executor): Selected 4/4 parts by partition key, 4 parts by primary key,
# highlight-next-line
              41/1083 marks by primary key, 41 marks to read from 4 ranges
...Executor): Reading approx. 335872 rows with 4 streams
```

### Option 3: Projections[​](#option-3-projections "Direct link to Option 3: Projections")

Create a projection on our existing table:

```
ALTER TABLE hits_UserID_URL
    ADD PROJECTION prj_url_userid
    (
        SELECT *
        ORDER BY (URL, UserID)
    );
```

And materialize the projection:

```
ALTER TABLE hits_UserID_URL
    MATERIALIZE PROJECTION prj_url_userid;
```

Note

- the projection is creating a **hidden table** whose row order and primary index is based on the given `ORDER BY` clause of the projection
- the hidden table isn't listed by the `SHOW TABLES` query
- we use the `MATERIALIZE` keyword in order to immediately populate the hidden table with all 8.87 million rows from the source table [hits\_UserID\_URL](#a-table-with-a-primary-key)
- if new rows are inserted into the source table hits\_UserID\_URL, then that rows are automatically also inserted into the hidden table
- a query is always (syntactically) targeting the source table hits\_UserID\_URL, but if the row order and primary index of the hidden table allows a more effective query execution, then that hidden table will be used instead
- please note that projections don't make queries that use ORDER BY more efficient, even if the ORDER BY matches the projection's ORDER BY statement (see <https://github.com/ClickHouse/ClickHouse/issues/47333>)
- Effectively the implicitly created hidden table has the same row order and primary index as the [secondary table that we created explicitly](/docs/guides/best-practices/sparse-primary-indexes#option-1-secondary-tables):

ClickHouse is storing the [column data files](#data-is-stored-on-disk-ordered-by-primary-key-columns) (*.bin), the [mark files](#mark-files-are-used-for-locating-granules) (*.mrk2) and the [primary index](#the-primary-index-has-one-entry-per-granule) (primary.idx) of the hidden table in a special folder (marked in orange in the screenshot below) next to the source table's data files, mark files, and primary index files:

The hidden table (and its primary index) created by the projection can now be (implicitly) used to significantly speed up the execution of our example query filtering on the URL column. Note that the query is syntactically targeting the source table of the projection.

```
SELECT UserID, count(UserID) AS Count
-- highlight-next-line
FROM hits_UserID_URL
WHERE URL = 'http://public_search'
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;
```

The response is:

```
┌─────UserID─┬─Count─┐
│ 2459550954 │  3741 │
│ 1084649151 │  2484 │
│  723361875 │   729 │
│ 3087145896 │   695 │
│ 2754931092 │   672 │
│ 1509037307 │   582 │
│ 3085460200 │   573 │
│ 2454360090 │   556 │
│ 3884990840 │   539 │
│  765730816 │   536 │
└────────────┴───────┘

10 rows in set. Elapsed: 0.029 sec.
# highlight-next-line
Processed 319.49 thousand rows, 1
1.38 MB (11.05 million rows/s., 393.58 MB/s.)
```

Because effectively the hidden table (and its primary index) created by the projection is identical to the [secondary table that we created explicitly](/docs/guides/best-practices/sparse-primary-indexes#option-1-secondary-tables), the query is executed in the same effective way as with the explicitly created table.

The corresponding trace log in the ClickHouse server log file confirms that ClickHouse is running binary search over the index marks:

```
...Executor): Key condition: (column 0 in ['http://public_search',
                                           'http://public_search'])
# highlight-next-line
...Executor): Running binary search on index range for part prj_url_userid (1083 marks)
...Executor): ...
# highlight-next-line
...Executor): Choose complete Normal projection prj_url_userid
...Executor): projection required columns: URL, UserID
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,
# highlight-next-line
              39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 streams
```

### Summary[​](#summary "Direct link to Summary")

The primary index of our [table with compound primary key (UserID, URL)](#a-table-with-a-primary-key) was very useful for speeding up a [query filtering on UserID](#the-primary-index-is-used-for-selecting-granules). But that index isn't providing significant help with speeding up a [query filtering on URL](/docs/guides/best-practices/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient), despite the URL column being part of the compound primary key.

And vice versa:
The primary index of our [table with compound primary key (URL, UserID)](/docs/guides/best-practices/sparse-primary-indexes#option-1-secondary-tables) was speeding up a [query filtering on URL](/docs/guides/best-practices/sparse-primary-indexes#secondary-key-columns-can-not-be-inefficient), but didn't provide much support for a [query filtering on UserID](#the-primary-index-is-used-for-selecting-granules).

Because of the similarly high cardinality of the primary key columns UserID and URL, a query that filters on the second key column [doesn't benefit much from the second key column being in the index](#generic-exclusion-search-algorithm).

Therefore it makes sense to remove the second key column from the primary index (resulting in less memory consumption of the index) and to [use multiple primary indexes](/docs/guides/best-practices/sparse-primary-indexes#using-multiple-primary-indexes) instead.

However if the key columns in a compound primary key have big differences in cardinality, then it is [beneficial for queries](/docs/guides/best-practices/sparse-primary-indexes#generic-exclusion-search-algorithm) to order the primary key columns by cardinality in ascending order.

The higher the cardinality difference between the key columns is, the more the order of those columns in the key matters. We will demonstrate that in the next section.

## Ordering key columns efficiently[​](#ordering-key-columns-efficiently "Direct link to Ordering key columns efficiently")

In a compound primary key the order of the key columns can significantly influence both:

- the efficiency of the filtering on secondary key columns in queries, and
- the compression ratio for the table's data files.

In order to demonstrate that, we will use a version of our [web traffic sample data set](#data-set)
where each row contains three columns that indicate whether or not the access by an internet 'user' (`UserID` column) to a URL (`URL` column) got marked as bot traffic (`IsRobot` column).

We will use a compound primary key containing all three aforementioned columns that could be used to speed up typical web analytics queries that calculate

- how much (percentage of) traffic to a specific URL is from bots or
- how confident we're that a specific user is (not) a bot (what percentage of traffic from that user is (not) assumed to be bot traffic)