Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 35 ยท Tables and MergeTree Engines

MergeTree: ORDER BY and the Primary Key

Master the MergeTree engine: how ORDER BY becomes the sorting key and sparse primary index, how to choose key columns, and how to check that a query uses it.

MergeTree is the table engine behind almost every ClickHouse table, and the ORDER BY clause is its most important design decision. It decides how rows are sorted inside each part, which sparse index is built, how well data compresses and how many granules a query has to read. Get it right and queries touch a sliver of the data; get it wrong and every query scans everything.

ClickHouse cannot run on this site. The CREATE TABLE and EXPLAIN snippets are read-only. The runnable blocks simulate sorted storage and granule skipping with SQLite, and say so.

The syntax

SQL
CREATE TABLE events
(
    tenant_id  UInt32,
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)      -- optional, see the partitioning lesson
ORDER BY (tenant_id, event_type, event_time)
PRIMARY KEY (tenant_id, event_type)    -- optional: a prefix of ORDER BY
SETTINGS index_granularity = 8192;     -- default
  • ORDER BY (required) is the sorting key: rows in each part are physically sorted by these columns.
  • PRIMARY KEY (optional) is the sparse index. If omitted it is the same as ORDER BY. If given, it must be a prefix of ORDER BY. You would shorten it to keep the in-memory index small when the sort key has many columns.
  • This is not a relational primary key: it does not enforce uniqueness. Two rows may share all key values.

A sparse index, not a B-tree

A traditional database indexes every row in a B-tree. ClickHouse stores one index entry per granule (8192 rows) holding the key values of that granule's first row. To answer WHERE tenant_id = 7, it binary-searches the index to find granules whose key range could contain 7 and reads only those. The index is tiny (it stays in RAM) and costs nothing to maintain on insert, because the data is already sorted.

Because rows are sorted by the first key column, then the second inside each first-column value and so on, a filter helps in proportion to how far it constrains the leading columns:

Filter Index use
tenant_id = 7 Very good: leading column
tenant_id = 7 AND event_type = 'click' Excellent
tenant_id = 7 AND event_time > ... Good for tenant_id; event_time helps only inside matching ranges
event_type = 'click' (no tenant) Partial: skipping works only if there are long runs
user_id = 123 None: not in the key, so a full scan of that column

Even a full scan reads just the columns needed, so it is still fast; the key's job is to avoid it for your most common filters.

Rules for choosing the key

Start with filters Columns in most WHERE clauses
Low cardinality first Few distinct values lead
Time near the end Or last, after the group keys
Keep it short 3 to 5 columns is typical

Put the columns you filter by most, from fewest to most distinct values.

1 / 2

Why low cardinality first

Sorting by (event_type, user_id) places all click rows together, sorted by user inside. Sorting by (user_id, event_type) makes the first column almost unique, so each user's rows are a handful and the second column rarely helps. Low-cardinality leading columns produce long runs of equal values: better compression (runs compress well), and a filter on the second column can still skip granules inside each run.

Let us simulate. The table below is sorted by the key and cut into granules of 4 rows. We count how many granules a filter on each column must read.

SQL Stand-in: how a sort order changes granules read (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Filtering on the leading column tenant needs about a quarter of the granules; filtering on kind, which only comes second, needs more, because buy rows are scattered across every tenant block. That scatter is exactly what a poor key causes. Now compare with the other order.

SQL Stand-in: the same filter, a different key (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

When the key starts with kind, the buy rows are contiguous and only 4 of the 10 granules are touched, against 6 of 10 before. The right order depends on your queries.

ORDER BY also drives compression and grouping

Sorted data compresses better because similar values sit together. Sorting also makes GROUP BY on a key prefix cheaper: ClickHouse can aggregate in key order (optimize_aggregation_in_order). And ORDER BY queries on the sort key can stream results without a full sort.

Check that the key works

SQL
EXPLAIN indexes = 1
SELECT count() FROM events WHERE tenant_id = 7 AND event_type = 'click';

The plan lists PrimaryKey with Parts: x/y and Granules: a/b. Seeing Granules: 40/120000 means the key skipped almost everything. Granules: 120000/120000 means it did nothing. You can also look at read_rows in system.query_log after the query.

Choosing a key: a worked example

An analytics product stores page views for many customers and queries are almost always "for one customer, over a time range, optionally by page":

SQL
ORDER BY (customer_id, toDate(event_time), page)

customer_id leads because every query filters it. A date comes next: it has moderate cardinality, and ranges on time then select a contiguous stretch. page lets per-page filters skip inside a day. Using toDate(event_time) instead of the full timestamp keeps runs long; the exact time can still be a column and be filtered in WHERE.

If one customer is huge and others tiny, the leading column still works, because granules are about rows, not customers.

Changing the key

You cannot change ORDER BY on an existing table, except to append newly added columns. To re-sort data, create a new table with the new key and copy rows with INSERT INTO new SELECT * FROM old, then swap names with EXCHANGE TABLES. Plan the key before you load billions of rows.

Skipping indexes (preview)

When you need to filter on a column not in the key, data-skipping indexes (minmax, set, bloom_filter, ngrambf_v1) can let ClickHouse skip granules. They help only if matching values cluster; see the performance chapter.

Common beginner mistakes

  • Putting a unique id (or a high-resolution timestamp) first.
  • Using the same order as the OLTP primary key without thinking about queries.
  • Expecting PRIMARY KEY to reject duplicates.
  • Creating a key with 10 columns, which bloats the index.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the ORDER BY clause of a MergeTree table define?

    ORDER BY sets the physical sort order and the sparse primary index; it does not guarantee query result order.
  2. 2. How is the ClickHouse primary key different from a relational primary key?

    The index points at granules; duplicate key values are allowed.
  3. 3. Which column should usually lead the sorting key?

    Frequently filtered, lower-cardinality columns first give long runs, good compression and effective skipping.
  4. 4. How can you check how many granules the key skipped?

    EXPLAIN indexes = 1 reports parts and granules selected by the primary key.

Summary

  • ORDER BY is the sorting key; the sparse index has one entry per 8192-row granule.
  • Filters on the leading key columns skip the most data; columns outside the key need a scan.
  • Lead with frequently filtered, lower-cardinality columns; keep the key short; avoid unique ids first.
  • The key does not enforce uniqueness and cannot be changed later (except by appending columns).
  • Verify with EXPLAIN indexes = 1.

Next: Partitioning and parts