ORDER BY mistakes in ClickHouse (and what they cost you)

In ClickHouse the table's ORDER BY clause defines the sorting key — the physical order rows are stored in, and the basis of the sparse primary index. Get it right and queries skip most of the data and columns compress far better. Get it wrong and you pay for it on every read. Here are the five mistakes we see most often.

Mistake 1: putting a high-cardinality column first

The first column in ORDER BY matters most — it drives index granularity and how well later columns sort. Leading with something unique (a UUID, an event id, a precise timestamp) gives the index almost nothing to skip on. Lead with the columns you filter by, lowest cardinality first.

-- Bad: event_id is unique, the index can't prune
ORDER BY (event_id, user_id, toDate(ts))

-- Better: filter columns first, low cardinality leading
ORDER BY (toDate(ts), tenant_id, user_id)

Mistake 2: ordering by a column you never filter on

The sorting key only helps queries whose WHERE/PREWHERE use its leading columns. If your dashboards always filter by tenant_id and date, but the table is ordered by created_by, the index is dead weight. Match the sorting key to your real query patterns — look at system.query_log to see what you actually filter on.

Mistake 3: confusing table ORDER BY with query ORDER BY

These are two different things. The ORDER BY in CREATE TABLE (the sorting key) controls storage and index. The ORDER BY at the end of a SELECT only sorts the result set and does nothing for scan cost. Adding a query-level ORDER BY never fixes a bad sorting key — it just sorts the rows you already paid to read.

Mistake 4: ignoring ORDER BY for compression

Sorting groups similar values next to each other, and ClickHouse codecs love runs of similar data. A good sorting key often shrinks a table 2–3× on disk on top of its query benefit. If a big table compresses poorly, its ORDER BY is one of the first things to revisit.

Mistake 5: trying to fix it in place

You can't freely ALTER the sorting key of an existing MergeTree table — ClickHouse only allows adding columns to the end of the key under strict conditions. Reordering or changing the leading columns means creating a new table with the right ORDER BY and back-filling with INSERT … SELECT, or adding a projection for the alternate access pattern.

Get the sorting key checked for you

OptiHouse compares your table ORDER BY against the filters in query_log, flags high-cardinality leading columns and redundant keys, and estimates the storage and scan impact of fixing them. Try the free optimizer or start a 14-day trial.