ClickHouse ORDER BY Is a Storage Decision, Not a Query Clause

The most common ClickHouse mistake I see in schema reviews is treating ORDER BY in a CREATE TABLE statement like the SQL clause everyone already knows. It isn't. In a MergeTree table, ORDER BY decides the physical layout of your data on disk for the lifetime of the table. A query clause sorts results; this one decides which bytes ClickHouse ever has to read.

The takeaway up front: choose the sorting key to match your most common selective filters, and you'll read a small fraction of your data per query. Choose it to match nothing, and every dashboard query becomes a near-full scan no matter how much hardware you throw at it.

What the sorting key actually does

MergeTree stores data in immutable parts, and rows inside each part are physically sorted by the key you declare. On top of that layout ClickHouse builds a sparse primary index: instead of one entry per row like a B-tree in a transactional database, it stores one entry per granule — a block of rows, 8192 by default (index_granularity).

That sparseness is the whole trick. When a query filters on a prefix of the sorting key, ClickHouse can compare the filter against granule boundary values and skip entire ranges of rows without reading them. A filter that matches the key prefix might read a few megabytes; the same filter on a column outside the key reads the whole part, because the index tells the engine nothing about where those values live.

This is why ClickHouse can scan billions of rows per second on one machine and still feel slow on a badly keyed table: the engine is fast, but only a good key lets it avoid scanning in the first place.

A worked example: tenant dashboards

Say you run a multi-tenant events pipeline. Almost every query is a dashboard scoped to one tenant over a recent time window. Two plausible schemas:

-- Key matches the workload
CREATE TABLE events_good (
    tenant_id  UInt64,
    ts         DateTime,
    event_type LowCardinality(String),
    payload    String
) ENGINE = MergeTree
ORDER BY (tenant_id, toDate(ts), event_type);

-- Key matches nothing anyone filters on
CREATE TABLE events_bad (
    tenant_id  UInt64,
    ts         DateTime,
    event_type LowCardinality(String),
    payload    String
) ENGINE = MergeTree
ORDER BY (event_type);

For WHERE tenant_id = 42 AND ts >= now() - INTERVAL 1 DAY, the first table prunes aggressively: tenant_id is the leading key column, so granules belonging to other tenants are skipped entirely, and the date component narrows it further. The second table has data sorted by event_type, so rows for tenant 42 are scattered across every granule. ClickHouse reads essentially all of them.

You can verify the difference yourself without trusting anyone's benchmarks. Insert identical sample data into both tables, then run the same query with:

EXPLAIN indexes = 1
SELECT * FROM events_good
WHERE tenant_id = 42 AND ts >= now() - INTERVAL 1 DAY;

Run this in clickhouse-client or your usual SQL console against your own instance; no special permissions needed beyond read access. The output shows how many parts, granules, and rows were selected versus skipped. Compare read_rows in system.query_log for both variants — the gap is the cost of the wrong key, measured in your own data. (I haven't run this exact example against a specific version; treat it as a template to execute, not as reported results.)

Choosing the key: a heuristic, not a law

The standard advice is: put your most common selective filter columns first, ordered roughly from lower to higher cardinality — tenant_id before toDate(ts) before event_type, for example. Pruning works on key prefixes, so a column only helps if the columns before it are also constrained in the query.

But cardinality ordering is a heuristic. If your dominant query pattern filters on a high-cardinality column like user_id, that column belongs early in the key even though it has millions of distinct values. The real question is always "what does the WHERE clause of my top three queries look like?" — the key is a prefix index over those predicates.

Two practical constraints keep keys honest:

• Don't make the key too wide. Every key column adds to the primary index size and slows merges and inserts. A ten-column key that mirrors "columns people might filter on someday" costs write performance and prunes no better than a three-column key that matches real queries.
• Use LowCardinality(String) for low-distinct-count string columns. It shrinks both storage and the index, and dictionary encoding makes comparisons cheap.

The trade-off: this decision is durable

Here's the uncomfortable part. Changing the sorting key later means rewriting data. Depending on your ClickHouse version, ALTER TABLE ... MODIFY ORDER BY may only apply to newly written parts, leaving old data in the old layout — so the practical path is usually creating a new table and re-inserting, which on a large table is an offline migration with real downtime planning. Exact semantics here are version-sensitive; check the docs for the release you actually run before relying on the ALTER behavior.

Secondary skip indexes (minmax, bloom filters) can rescue filters on non-key columns, but they add write overhead and are a patch, not a substitute. If half your queries filter on a column that isn't in the key, the fix is a better key — or a second table (or materialized view) keyed for that access pattern, which is a legitimate and common ClickHouse pattern.

What to do on Monday

List your three most frequent queries. Write their filter columns in order of selectivity. That ordered list, trimmed to three or four columns, is your candidate sorting key. Then prove it: build the table, load a realistic slice of data, and check EXPLAIN indexes = 1 and system.query_log's read_rows against your expectations. ClickHouse's performance is not magic — it's layout. The sorting key is where you get to choose the layout, and it's much cheaper to choose it before the first terabyte than after.