In ClickHouse, how do you choose the column order for a MergeTree ORDER BY key?

## The rule that drives everything The sparse primary index prunes only on a **leading run** of sorting-key columns. With `ORDER BY (a, b, c)`, a filter on `a` prunes well, `a` and `b` prune better, and `c` alone prunes not at all — `c` is ordered only within equal `(a, b)`, so every granule's range for `c` spans the whole domain. Column order is therefore not a stylistic choice; it is the access-path design for the table, and it is close to permanent, since changing a leading column requires rebuilding the table and swapping it in. ## Step 1 — find the universal predicate List the queries the table actually serves and find the column that appears in the `WHERE` clause of nearly all of them. In multi-tenant analytics this is almost always the tenant or account id; in event pipelines it is often a coarse time column; in device telemetry it is the device or site id. That column goes first. If no column is present in most queries, that is a signal the table is serving two workloads and may need two differently-sorted copies. A useful sanity check is that the leading column must be *selective enough to matter*. A boolean or a two-valued status column at the front splits the table into two ranges and prunes essentially nothing, while forcing every other column one position deeper. ## Step 2 — order the rest low-cardinality first After the leading column, the conventional guidance is ascending cardinality. Two independent effects push the same way: - **Pruning.** Within a granule range already narrowed by earlier columns, a low-cardinality column produces long runs of equal values, so its bounds in successive index entries stay tight and further narrow the search. A high-cardinality column such as a UUID or a raw nanosecond timestamp changes on nearly every row; placing it early makes each granule's range effectively unique and destroys the runs the later columns would have relied on. - **Compression.** Column files are compressed in blocks over physically adjacent rows. Sorting by a low-cardinality column first produces long identical runs, which the general-purpose compressor and specialised codecs (`Delta`, `DoubleDelta`, `Gorilla`, `T64`, `LowCardinality` dictionary encoding) exploit heavily. The same table with a high-cardinality leading column can occupy several times the disk and read several times the bytes for the same query. The guidance is a heuristic, not a law: if a medium-cardinality column is filtered in 90% of queries and a lower-cardinality one in 10%, the filtered-more-often column wins the earlier position. Query frequency outranks cardinality; cardinality is the tie-break. ## Step 3 — keep it short Each additional key column costs sort work on every insert, widens the in-memory primary index, and usually contributes no pruning because no query filters that deep. Three or four columns is the common shape. Where a column must be in the sort order for collapsing correctness — `ReplacingMergeTree` or `AggregatingMergeTree` identity — but is never filtered on, put it at the end of `ORDER BY` and leave it out of an explicitly declared `PRIMARY KEY`, so it costs sort work but not index size. ```sql -- serves: tenant filters, tenant + day ranges, tenant + day + user drill-down CREATE TABLE events ( tenant_id UInt32, event_date Date, user_id UInt64, request_id UUID, payload String ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (tenant_id, event_date, user_id); ``` Putting `request_id` — a UUID, effectively unique — anywhere near the front of that key would be the textbook mistake: no query filters on it alone, and it would fragment every run of equal values behind it. ## When one key cannot serve the workload This happens often enough that having a stock answer matters. Options, in escalating cost: 1. **A data-skipping index** on the secondary predicate — cheap to add, but it only helps if the values of that column are physically clustered under the existing sort order. 2. **A second table sorted the other way**, populated from the same ingest path, with queries routed by shape. Costs storage and write amplification, and makes consistency your problem, but it genuinely gives two access paths. 3. **Accepting the scan** for the rarer query shape, if it runs infrequently and the table is small enough that a full scan is within budget. This is a legitimate engineering answer and interviewers like hearing it stated as a deliberate choice rather than an oversight. ## Verifying the choice Run the representative queries behind `EXPLAIN indexes = 1` and compare surviving granules against the table total; check compressed size per column in `system.parts_columns` before and after a candidate reordering on a sample table. A key reordering that halves compressed size and cuts granules read by an order of magnitude is measurable in minutes on a copy — far cheaper than discovering the mistake after the table has grown to terabytes. ## Interview traps Answering "put the primary key first" or "the most selective column first" without qualification both miss. Highest selectivity first is the OLTP B-tree instinct, and here it is often exactly wrong: a unique column at the front prunes precisely one granule per lookup and wrecks compression and every other query. The strong answer starts from the query mix, then applies ascending cardinality, then keeps the key short.