ClickHouse ORDER BY Explorer

Understand how the ORDER BY (primary sort key) affects query performance. This is ClickHouse's most important design decision.

❌ Wrong ORDER BY for the query pattern

Setup: A SaaS analytics table ordered by (region, timestamp). The most common query is: "show me all events for customer X in the last hour."

-- Table definition: BAD ORDER BY for the query pattern CREATE TABLE events ( timestamp DateTime, customer_id UInt64, user_id UInt64, region String, endpoint String, latency_ms Float64 ) ENGINE = MergeTree() ORDER BY (region, timestamp); -- sorted by region first -- Most common query: filter by customer_id SELECT * FROM events WHERE customer_id = 42 AND timestamp >= now() - INTERVAL 1 HOUR;

What happens during query execution

Data is sorted by (region, timestamp). The WHERE clause filters on customer_id — which is NOT in the sort key. ClickHouse cannot skip any granules based on customer_id.

Granule	Region	Timestamp range	Has customer 42?	Action
0	ap-south-1	2024-01-15 00:00 → 00:15	Unknown	⚠ Must read (can't skip)
1	ap-south-1	2024-01-15 00:15 → 00:30	Unknown	⚠ Must read
2	eu-west-1	2024-01-15 00:00 → 00:20	Unknown	⚠ Must read
3	eu-west-1	2024-01-15 00:20 → 00:40	Unknown	⚠ Must read
4	eu-west-1	2024-01-15 00:40 → 01:00	YES	✓ Read (matched)
5	us-east-1	2024-01-15 00:00 → 00:25	Unknown	⚠ Must read
...	...	...	Unknown	⚠ Must read all

Granules read: ~1000 of 1000

Data scanned: ~100%

Query time: slow (full scan)

Scanned: 100% of data

✓ Right ORDER BY for the query pattern

Same table, different ORDER BY. Now ordered by (customer_id, timestamp) — matching the most common query pattern.

-- Table definition: GOOD ORDER BY for the query pattern CREATE TABLE events ( timestamp DateTime, customer_id UInt64, user_id UInt64, region String, endpoint String, latency_ms Float64 ) ENGINE = MergeTree() ORDER BY (customer_id, timestamp); -- sorted by customer first -- Most common query: filter by customer_id SELECT * FROM events WHERE customer_id = 42 AND timestamp >= now() - INTERVAL 1 HOUR;

What happens during query execution

Data is sorted by (customer_id, timestamp). All rows for customer 42 are physically co-located. ClickHouse reads only the granules that could contain customer 42.

Granule	customer_id range	Timestamp range	Could have cust 42?	Action
0–35	1 → 41	all dates	No	✓ Skip all
36	42 → 42	last week	YES	⚠ Read → filter timestamp
37	42 → 42	last week	YES	⚠ Read → filter timestamp
38	42 → 42	last hour	YES	✓ Read (matched)
39–1000	43 → ...	all dates	No	✓ Skip all

Granules read: ~3 of 1000

Data scanned: ~0.3%

Query time: fast (<10ms)

Scanned: 0.3% of data

Trade-offs: Cardinality in ORDER BY

The first columns in ORDER BY should match the predicates used by the queries that must be fast. Selectivity, range filters, compression, and tenant isolation all matter; cardinality alone does not choose the key. Subsequent columns help primarily within groups of preceding columns.

Rule: Put the most important query prefix first

ORDER BY	Query: WHERE customer_id=42	Query: WHERE region='eu'	Both?
`(customer_id, timestamp)`	✓ Fast	✗ Full scan	customer fast, region slow
`(region, timestamp)`	✗ Full scan	✓ Fast	region fast, customer slow
`(customer_id, region, timestamp)`	✓ Fast	✗ Full scan	customer fast, region still slow

Key insight: ORDER BY (customer_id, region) means data is sorted by customer_id first. Within the same customer, it's sorted by region. Queries filtering ONLY on region still scan everything because customers are interleaved across regions in the sort order.

When you need both

-- If you need fast queries on BOTH customer_id and region: -- Option 1: Two tables with different ORDER BY keys (materialised views) CREATE MATERIALIZED VIEW events_by_region ENGINE = MergeTree() ORDER BY (region, timestamp) AS SELECT * FROM events; -- Option 2: Skipping index on the secondary column ALTER TABLE events ADD INDEX idx_region (region) TYPE set(100) GRANULARITY 4; -- Skipping index is approximate — reduces but doesn't eliminate scans