ORDER BY and PRIMARY KEY in ClickHouse: index selection

ORDER BY and PRIMARY KEY in ClickHouse: How to Get the Index Right

1. Why ORDER BY Is the Most Important Thing You'll Specify in a Table

In conventional databases (PostgreSQL, MySQL), there are two concepts: a clustered index (the primary key that physically orders data on disk) and secondary indexes (separate B-trees). You can add or drop an index at any time without recreating the table.

In ClickHouse, it's different. Here, there is only one physical order of data on disk — what you specified in ORDER BY. And you cannot change it without recreating the table. At all. Period. It's like pouring concrete and realizing you placed the rebar wrong. You have to break everything out and start over.

Why so strict? Because ClickHouse stores data in a columnar format, heavily compressed. To change the row order, you'd have to rewrite all columns from scratch. Nobody wants to wait hours or days to reorganize a terabyte-sized table.

Therefore, choosing ORDER BY is a strategic decision. You must predict which queries will be most frequent and design the key so they execute lightning fast. A mistake will be costly.

Real-life analogy: Imagine you're a librarian and you need to arrange all books on shelves in a specific order. You can choose an order, say by genre, and within that by author's last name. Then you can find books quickly if you search by those criteria. But if you decide that ordering by publication date would be more convenient, you'll have to move all books from the shelves again. For hours.

2. PRIMARY KEY ⊆ ORDER BY — A Rare Rule

In ClickHouse, you have two parameters:

• ORDER BY — defines the physical order of rows on disk (mandatory).
• PRIMARY KEY — defines the index (optional).

And there's a hard rule: the columns listed in PRIMARY KEY must be the first columns in ORDER BY. In other words, PRIMARY KEY is a prefix of ORDER BY.

-- ✅ Correct: PRIMARY KEY is the first two columns of ORDER BY
CREATE TABLE bets
(
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at, amount)    -- full order
PRIMARY KEY (user_id, created_at);         -- prefix: first two

-- ❌ Error: PRIMARY KEY is not a prefix
ORDER BY (user_id, created_at, amount)
PRIMARY KEY (created_at, user_id);   -- different order — ClickHouse will throw an error

-- ⚠️ You can omit PRIMARY KEY entirely
-- Then it automatically matches ORDER BY
CREATE TABLE bets
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- PRIMARY KEY = (user_id, created_at)

So why do you need PRIMARY KEY if it's just a prefix? Here's why: the ClickHouse index (sparse index) is built only on the columns in PRIMARY KEY. If you specify a shorter PRIMARY KEY than ORDER BY, you save memory on the index, but the row order is still full (by all ORDER BY columns). This is useful when columns affecting physical order are not needed in the index.

Example: In ORDER BY (user_id, created_at, amount) — rows are first by user_id, then by created_at, then by amount. But you don't need to search by amount, so PRIMARY KEY (user_id, created_at) is shorter, the index is smaller, and the physical layout helps with compression (identical amounts are stored together).

3. Sparse Index: One Entry per 8192 Rows (Granule)

The index in ClickHouse is called a sparse index. It does not store a pointer to each row like a B-tree in PostgreSQL. Instead, it stores one entry for every 8192 rows (this group is called a granule).

What it looks like internally:

Granule (rows 1–8192)	PRIMARY KEY value for the first row of the granule
Granule 1	user_id=100, created_at=2025-01-01 00:00:01
Granule 2	user_id=100, created_at=2025-01-01 10:15:23
Granule 3	user_id=200, created_at=2025-01-01 00:00:05
...	...

How ClickHouse searches for data:

1. You have a query WHERE user_id = 100 AND created_at >= '2025-01-01'.
2. ClickHouse looks at the sparse index and sees the granules.
3. It finds that user_id=100 appears in granules 1, 2, possibly 3 and beyond.
4. But it doesn't know exactly where within a granule the desired row is — because the index only points to the start of the granule.
5. Therefore, ClickHouse reads all granules that might contain the needed rows (sometimes more than necessary — this is called filtering by index).

Analogy: A sparse index is like a table of contents in a book where each chapter is 100 pages. The table of contents says: "Chapter 3 starts on page 201." If you need a specific phrase on page 210, you still have to read pages 201–300 entirely because you don't know the exact location. In PostgreSQL, a B-tree index would give you page 210.

Why is this fast in ClickHouse? Because:

• ClickHouse reads columns selectively — if WHERE needs user_id and SELECT needs amount, it reads only those two columns.
• Data within a granule is compressed, and reading 8192 rows at once is very efficient (minimum volume ~64 KB, granule size is configurable via index_granularity).
• For analytical queries (which read millions of rows), such granularity is fine.

4. Cardinality Rule: Low First, Then High

Cardinality is the number of unique values in a column. For example:

• sport_id (sport type: football, hockey, tennis) — cardinality 20 (low)
• market_id (bet market: outcome, total, handicap) — cardinality 1000 (medium)
• created_at (time to the second) — cardinality billions (high)

The golden rule of ClickHouse: in ORDER BY, columns with low cardinality should come before columns with high cardinality.

Why? Because the sparse index will be more effective at pruning granules.

Bad key: ORDER BY (created_at, sport_id)

• Data is sorted first by time. sport_id for adjacent rows will jump around: football, hockey, tennis, then football again...
• Query WHERE sport_id = 1 forces ClickHouse to read all granules because sport_id=1 is scattered throughout the table.

Good key: ORDER BY (sport_id, created_at)

• First, all football rows (sport_id=1) sorted by time. Then all hockey rows (sport_id=2) — compact.
• Query WHERE sport_id = 1 prunes all granules not related to football at the index level. ClickHouse reads only granules with sport_id=1.

Analogy: Imagine sorting a deck of cards. If you sort first by suit (low cardinality — 4 values) and then by rank (high — 13 values), all spades will be together. If you do it the other way — first by rank, then aces of all suits are scattered throughout the deck. Finding all spades becomes difficult.

5. Example for Betting: How to Choose the Right ORDER BY

Let's compare two options for a betting table in a bookmaker.

Option A (bad): ORDER BY (created_at, sport_id)

CREATE TABLE bets_bad
(
    sport_id    UInt8,       -- 1 = football, 2 = hockey, 3 = tennis
    market_id   UInt32,      -- bet market ID
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (created_at, sport_id, market_id);

How typical queries perform:

-- Query: all football bets in the last hour
SELECT sum(amount) FROM bets_bad
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN will show: reading almost all granules because sport_id=1 is scattered across the timeline

The index (created_at, sport_id) helps poorly because sport_id is the second column. ClickHouse can use the prefix created_at, but then filtering sport_id must be done at the granule level, reading extra data.

Option B (good): ORDER BY (sport_id, market_id, created_at)

CREATE TABLE bets_good
(
    sport_id    UInt8,
    market_id   UInt32,
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (sport_id, market_id, created_at);

Same queries:

-- Query: football bets in the last hour
SELECT sum(amount) FROM bets_good
WHERE sport_id = 1 AND created_at >= now() - interval 1 hour;

-- EXPLAIN will show: reading only granules where sport_id = 1, significantly fewer

Why it's better: ClickHouse can immediately find blocks with sport_id = 1 via the index, and within those blocks, data is sorted by market_id and created_at. The time filter created_at >= ... is applied at the granule level within those blocks.

6. Equality vs Range: Which Is More Efficient

For columns in ORDER BY, there is a hierarchy of efficiency:

1. Equality (=) — most efficient. If you search for an exact value, ClickHouse can skip entire blocks of granules.
2. Inequality (>=, <=, BETWEEN) — less efficient, but can work if it's the last column in the key.
3. LIKE or other functions — often don't use the index at all (unless they are transformed into a range).

Rule: In ORDER BY, columns with equality conditions should come before columns with range conditions.

Example for key (user_id, created_at):

-- ✅ Great: user_id = equality (first column), created_at >= range (second)
SELECT * FROM bets WHERE user_id = 123 AND created_at >= '2025-06-01';

-- ❌ Bad: created_at range (first column), user_id = equality (second)
-- The index can only prune by created_at, but user_id must be filtered within granules
SELECT * FROM bets WHERE created_at >= '2025-06-01' AND user_id = 123;

Why is that? Because data is physically sorted by (user_id, created_at). All records for one user_id are stored compactly, and within them, sorted by time. If you search by time range, it's easy. But if you search first by time, records for one user_id are scattered throughout the table — they cannot be pruned by the index.

Analogy: Imagine a phone book sorted first by last name, then by first name. Finding "all Ivanovs" is easy (last name is the first column). Finding "everyone born after 1990" requires reading the entire book.

7. Composite Key of UInt8+UInt32+DateTime vs Just DateTime

Sometimes it seems: "Why not just use ORDER BY created_at — simple and clear?" Let's analyze with a gambling example.

Queries that a dashboard actually needs:

• Bets by a specific user in the last week: WHERE user_id = 123 AND created_at >= today() - 7
• Statistics by sport for a day: WHERE sport_id = 1 AND created_at = yesterday()
• Aggregation by market for an hour: WHERE market_id = 100 AND created_at >= now() - 1 hour

Option 1: ORDER BY (created_at)

CREATE TABLE bets_simple
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY created_at;

Problems:

• Queries by user_id will be slow — you'll have to scan everything.
• Queries by sport_id — same story.

Option 2: ORDER BY (user_id, sport_id, market_id, created_at)

CREATE TABLE bets_composite
(
    user_id     UInt64,
    sport_id    UInt8,
    market_id   UInt32,
    created_at  DateTime
)
ORDER BY (user_id, sport_id, market_id, created_at);

Now:

• Query WHERE user_id = 123 AND created_at >= ... — excellent (uses prefix user_id).
• Query WHERE sport_id = 1 AND created_at = ... — poor, because sport_id is not the first column. ClickHouse cannot prune by sport_id in the index.

Compromise: Choose the most frequent filter pattern and put its columns at the beginning of ORDER BY. If you most often search by user_id, put user_id first. If more often by brand_id, put that first.

Rule of thumb: ORDER BY should have at least 2–4 columns. One column is rarely optimal.

8. How to Check Key Efficiency with EXPLAIN

ClickHouse provides powerful tools to analyze how the index is used.

EXPLAIN indexes = 1

-- Enable output of index usage information
EXPLAIN indexes = 1
SELECT sum(amount) FROM bets
WHERE user_id = 123 AND created_at >= '2025-06-01';

The result will show something like:

Expression
  ...
  ReadFromMergeTree
    Indexes:
      PrimaryKey
        Condition: (user_id = 123) AND (created_at >= '2025-06-01')
        Used keys: (user_id, created_at)
        Granules: 15 / 1280

What the numbers mean: 15 / 1280 — out of 1280 granules in the table, only 15 were read. Excellent result. If it shows 1200 / 1280, the index barely helped.

system.query_log

The system table query_log stores statistics for each query. The most useful columns for index analysis:

-- Find slow queries and see how many rows they read
SELECT 
    query,
    read_rows,          -- how many rows read
    result_rows,        -- how many rows returned
    read_rows / result_rows AS efficiency,  -- closer to 1 is better
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish' 
  AND query LIKE '%bets%'
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC;

How to interpret:

• read_rows / result_rows ≈ 1..10 — index works well
• read_rows / result_rows > 1000 — you're reading thousands of rows for one — bad index
• read_rows close to total rows in table — full scan

columns_read from system.query_log

SELECT 
    query,
    read_rows,
    written_rows,
    result_rows,
    columns_read,      -- list of columns that were read
    columns_written
FROM system.query_log
WHERE type = 'QueryFinish' AND query_duration_ms > 1000
LIMIT 10;

If in columns_read you see columns not in SELECT or WHERE, ClickHouse is reading extra data (possibly due to a poor ORDER BY).

9. Patterns for the Gambling Industry

Pattern 1: Queries by a Specific Player

If the most frequent query is "show a user's bet history", the key (user_id, created_at) is ideal.

CREATE TABLE bets_by_user
(
    user_id     UInt64,
    created_at  DateTime,
    sport_id    UInt8,
    amount      Decimal(18,2)
)
ORDER BY (user_id, created_at);   -- All bets of a user are compact and by time

Query WHERE user_id = 123 AND created_at BETWEEN ... will read only that user's granules, which are few.

Pattern 2: Multi-Brand Platform

You have several brands (casino_A, casino_B), and queries almost always include brand_id. Then:

CREATE TABLE bets_multi_brand
(
    brand_id    UInt8,        -- Low cardinality (5 brands)
    user_id     UInt64,
    created_at  DateTime,
    amount      Decimal(18,2)
)
ORDER BY (brand_id, created_at);

Query WHERE brand_id = 1 AND created_at >= ... prunes all data from other brands at the index level.

Pattern 3: Dashboard by Sport

If reports group by sport_id (football, hockey) and filter by time:

CREATE TABLE bets_by_sport
(
    sport_id    UInt8,
    created_at  DateTime,
    user_id     UInt64,
    amount      Decimal(18,2)
)
ORDER BY (sport_id, created_at);

There is no universal key. You must choose one or two most frequent query patterns and optimize for them. Other queries will be slower — that's an inevitable trade-off.

10. Changing ORDER BY After Table Creation — Not Possible

This is the saddest but most important knowledge. You cannot change ORDER BY or PRIMARY KEY on an existing table with commands like ALTER.

-- ❌ Nothing like this exists
ALTER TABLE bets MODIFY ORDER BY (new_column, created_at);   -- ERROR!

Why? Because the physical order of rows is already determined. To change it, you need to recreate the table.

What to do if you realize you made a mistake?

Method 1: Create a new table, migrate data, rename

-- 1. Create a new table with the correct ORDER BY
CREATE TABLE bets_new
(
    user_id UInt64,
    created_at DateTime,
    amount Decimal(18,2)
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at);   -- new key

-- 2. Migrate data (can be asynchronous if the table is large)
INSERT INTO bets_new SELECT * FROM bets;

-- 3. Swap tables (atomic operation)
RENAME TABLE bets TO bets_old, bets_new TO bets;

-- 4. Verify everything works, then drop the old table
DROP TABLE bets_old;

Method 2: Use a materialized view (if you can store data in two orders simultaneously)

-- Keep the old table for some queries
-- Create a materialized view with a different ORDER BY for other queries
CREATE MATERIALIZED VIEW bets_by_sport_mv
ENGINE = MergeTree() ORDER BY (sport_id, created_at)
AS SELECT * FROM bets;   -- data will be duplicated

Method 3: Accept it and live with a bad key (sometimes it's cheaper to increase resources than to migrate terabytes)

Advice: Before creating a table with large data (billions of rows), always test ORDER BY on a sample. Create a copy with 10 million rows, run EXPLAIN indexes=1, test different queries. This will save you weeks of pain later.

What's Next

Now you understand that ORDER BY in ClickHouse is not just sorting, but a strategic index. Next topics:

• How to configure index_granularity — change granule size from 8192 to another value (almost never needed).
• Partitioning vs ORDER BY — when partitions help and when the index does.
• Skip indexes (bloom filter indexes) — secondary indexes for columns not in ORDER BY.
• Analyzing slow queries via system.query_log — deep profiling.

Bottom line: The formula for the ideal ORDER BY in ClickHouse: columns with low cardinality and equality conditions go first; then columns with high cardinality and ranges. Don't try to cover everything — choose the most frequent queries and ignore the rest. And never forget EXPLAIN indexes=1 — the best friend of a ClickHouse developer.

---

← Previous: Partitioning in ClickHouse: How to Manage Data at the Folder Level
→ Next: TTL in ClickHouse: Automatic Data Lifecycle Management

— Editorial Team