ORDER BY Is the Primary Key (Mostly)

The rule, stated precisely: a prefix, not a set

You already know the sparse primary index is built from ORDER BY, one entry per granule, and that it can only rule out granules whose values fall outside a query's filter. What wasn't yet spelled out is exactly which filters that applies to when ORDER BY has more than two columns.

Think of it the way a phone book sorted by (last name, first name) works. You can jump straight to every "Ramirez," and within that, straight to every "Ramirez, John." But you cannot jump straight to every "John" regardless of last name — the book just isn't organized that way. Once you fix "Ramirez," the "John"s inside it are grouped together; until you fix a last name, the first names are scattered across the entire book in no useful order at all.

That's the whole rule, generalized: the primary index reliably narrows a scan using a left-anchored, contiguous prefix of ORDER BY's columns, starting from the first. Not "any of the columns in ORDER BY," not "as many as show up in the WHERE clause." Once a column in that sequence is left unconstrained, don't count on any column after it to add further narrowing — ClickHouse does have a fallback search for that case, but its effectiveness depends unpredictably on the data (specifically, how many distinct values the skipped column has), so it isn't something to design a table around. For the purposes of designing an ORDER BY, treat a gap in the prefix as "no dependable narrowing beyond it."

CREATE TABLE requests
(
    org_id   UInt64,
    endpoint String,
    ts       DateTime,
    status   UInt16
)
ENGINE = MergeTree
ORDER BY (org_id, endpoint, ts);

Four queries against that table, and four different outcomes:

-- Q1
SELECT count() FROM requests WHERE org_id = 501;

-- Q2
SELECT count() FROM requests WHERE org_id = 501 AND endpoint = '/checkout';

-- Q3
SELECT count() FROM requests WHERE org_id = 501 AND ts > '2024-06-01';

-- Q4
SELECT count() FROM requests WHERE endpoint = '/checkout';

Q1  org_id = 501                          → narrows on org_id (1-column prefix)
Q2  org_id = 501 AND endpoint = '/checkout' → narrows on (org_id, endpoint) (2-column prefix)
Q3  org_id = 501 AND ts > '2024-06-01'      → reliably narrows on org_id only —
                                              ts is skipped over endpoint, so it
                                              can't be counted on to narrow further
Q4  endpoint = '/checkout'                  → doesn't narrow at all — endpoint
                                              isn't the leading column

Q3 is the one that catches people off guard. It feels like it should narrow further than Q1, since it's a strictly more specific filter with a real condition on a real ORDER BY column. But ts is only sorted within each (org_id, endpoint) group, and this query leaves endpoint completely unconstrained — so within the matched org_id, rows are scattered across every endpoint value in whatever ts order each one happens to have. ts sits behind a gap in the prefix, so — reliably — it doesn't buy you anything beyond what org_id already narrowed.

Choosing column order is choosing which queries win

Because only a prefix counts, the order you list columns in ORDER BY is a real design decision, not a style preference. Put the column your queries filter on most often — and most selectively — first. Everything after it only helps queries that also pin down every column ahead of it.

This is also why "just list every column I ever filter on, in ORDER BY" isn't a strategy. A five-column ORDER BY doesn't give you five independent filter fast-paths — it gives you one fast-path per contiguous prefix, and most real queries will only ever supply the first one or two.

PRIMARY KEY: a prefix of ORDER BY, and sometimes a shorter one

Every example so far has left PRIMARY KEY out of the CREATE TABLE statement, because — as the previous module's lesson mentioned — when you don't specify one, ClickHouse uses the full ORDER BY expression as the primary key. That's the "mostly" in this lesson's title: the primary key must be a prefix of the sorting key, but it doesn't have to be the whole sorting key. You can declare one explicitly, shorter than ORDER BY:

CREATE TABLE requests
(
    org_id   UInt64,
    endpoint String,
    ts       DateTime,
    status   UInt16
)
ENGINE = MergeTree
ORDER BY (org_id, endpoint, ts)
PRIMARY KEY (org_id, endpoint);

Rows are still physically sorted by the full three-column ORDER BY — that part doesn't change, and it's what a merge produces regardless of PRIMARY KEY. What changes is what the sparse index stores: with PRIMARY KEY (org_id, endpoint), the index only ever holds (org_id, endpoint) values per granule, not ts. Two consequences follow directly:

• The index itself is smaller and cheaper to hold in memory, since it's storing fewer columns' worth of values per entry across potentially millions of granules.
• ts can never narrow a granule scan on its own, even for queries like Q3 that constrain org_id and ts together — the index has no ts entries to check, at any prefix depth. Notice this doesn't actually lose you anything Q3 already had: ts was already not reliable for narrowing past the endpoint gap. What you're giving up by shortening the primary key is exactly the narrowing ts would have added on top of a full (org_id, endpoint) match — i.e., a query like org_id = 501 AND endpoint = '/checkout' AND ts > '2024-06-01' would have narrowed to a tighter range of granules with the full three-column primary key than it does with the two-column one.

So why give up any narrowing at all? Because ts doesn't stop mattering just because it's outside the primary key — it's still the column deciding physical row order within each (org_id, endpoint) group, which keeps similar ts values adjacent on disk. That adjacency is what compression algorithms exploit: physically neighboring values that are close to each other compress better than the same values scattered at random. A shorter primary key is a trade: less memory spent on an index, in exchange for one narrowing step you'd otherwise get on queries that happen to constrain every column ahead of the dropped one too.

ORDER BY (customer_id, region, event_time)
PRIMARY KEY (customer_id)