Why Column Order in ORDER BY Matters for ClickHouse Performance

In ClickHouse, the ORDER BY clause is not just about sorting data—it's the foundation of query performance. Unlike traditional databases where primary keys enforce uniqueness, ClickHouse's MergeTree engine uses the ORDER BY clause to physically sort data on disk. Get this wrong, and queries that should run in milliseconds can crawl for minutes because the engine can't skip irrelevant data blocks.

ClickHouse employs a sparse primary index. Instead of pointing to every row (like a B-Tree), it marks the start of data blocks—typically every 8,192 rows. When you filter with a WHERE clause, ClickHouse uses this index to skip entire blocks that don't contain your values. But this only works if the data is sorted in a way that allows effective skipping.

The Logic of Physical Sorting

The MergeTree engine stores data in a way that mirrors how you query it. If you define a table as ORDER BY (event_type, event_date), and you frequently query WHERE event_type = 'error' AND event_date BETWEEN '2023-10-01' AND '2023-10-31', ClickHouse can quickly jump to the relevant blocks. But reverse the order to ORDER BY (event_date, event_type), and a query filtering by event_type becomes inefficient because event types are scattered across every date block.

The Golden Rules of Column Ordering

To maximize data skipping, follow these principles:

• High Selectivity First: Place columns used frequently in WHERE clauses at the beginning.
• Low Cardinality Before High: If a low-cardinality column helps prune large chunks early, it should come before high-cardinality columns.
• Prefix Matching: ClickHouse uses the index from left to right. A key of (A, B, C) supports filtering by A, A AND B, or all three—but not just B.

Practical Example: Schema Comparison

Consider a log table tracking user events. Two possible schemas:

Configuration	Table Definition	Query Impact
Optimized	ORDER BY (user_id, timestamp)	Fast for: WHERE user_id = 123
Sub-optimal	ORDER BY (timestamp, user_id)	Slow for: WHERE user_id = 123 (scans all timestamps)

To verify your table's performance, use EXPLAIN to see how ClickHouse plans to use the index:

# Run in your ClickHouse client
EXPLAIN SELECT * FROM events WHERE user_id = 5501 AND event_date = '2023-10-01';

Check the output for 'Primary marks'—a low number indicates effective data skipping. If you're reading thousands of marks, your index isn't helping.

Trade-offs and Limitations

While correct column ordering is crucial, it comes with constraints. You cannot alter the primary key of an existing table—doing so requires a full table rewrite, which is resource-intensive for large datasets. Additionally, avoid over-partitioning with PARTITION BY. While ORDER BY optimizes query speed, PARTITION BY manages data lifecycle. Too many partitions (thousands) can slow down merges and queries.

The key is balancing query patterns with storage efficiency. Start with your most common filters, prioritize low-cardinality columns that prune data early, and always validate with EXPLAIN before scaling.