Order Columns by Cardinality (Low to High)

Order Columns by Cardinality (Low to High)

Impact: CRITICAL

Since the sparse primary index operates on data blocks (granules) rather than individual rows, low-cardinality leading columns create more useful index entries that can skip entire blocks. Place lower-cardinality columns before higher-cardinality ones in the ordering key.

Incorrect (high cardinality first):

-- UUID first means no pruning benefit
CREATE TABLE events (...)
ENGINE = MergeTree()
ORDER BY (event_id, event_type, timestamp);
-- Every granule has different event_id values, index can't skip anything

Correct (low cardinality first):

-- Low cardinality first enables pruning
CREATE TABLE events (...)
ENGINE = MergeTree()
ORDER BY (event_type, event_date, event_id);
-- Index can skip entire event_type groups

Column Order Guidelines:

Tip: Use toDate(timestamp) instead of raw DateTime columns when day-level filtering suffices - this reduces index size from 32-bit to 16-bit representations.

Reference: Choosing a Primary Key