TopolearnA table has one sort order; these are the tools for the second and third most common query shapes.
The primary index helps only filters on the sort key prefix. For other columns you have two tools.
Data-skipping indexes store a small summary per block of granules (GRANULARITY n granules) and let ClickHouse skip blocks whose summary rules out a match:
minmax: min and max per block, good when values correlate with the sort order (e.g. a timestamp in a table sorted by tenant, ts).set(N): the distinct values per block, for low-cardinality columns.bloom_filter,tokenbf_v1,ngrambf_v1: probabilistic membership, for equality on high-cardinality ids or token search in text.
They help only when matching values are clustered in a few blocks. If a value appears in every granule, the index costs time and skips nothing. Add one with ALTER TABLE ... ADD INDEX, then MATERIALIZE INDEX to build it for existing parts, and confirm with EXPLAIN indexes = 1.
Projections store a second copy of the table's data, re-sorted (SELECT * ORDER BY user_id) or pre-aggregated (SELECT day, sum(x) GROUP BY day), inside the same parts. The optimizer picks a projection automatically when it can answer a query with fewer reads, and inserts keep it consistent. The cost is extra storage and slower inserts and merges. Compared with a materialized view, a projection needs no separate table and no query rewrite, but it's limited to one source table.
Example
CREATE TABLE requests
(tenant UInt16, ts DateTime, trace_id String, user_id UInt64, ms UInt32,
INDEX trace_bf trace_id TYPE bloom_filter(0.01) GRANULARITY 4)
ENGINE = MergeTree ORDER BY (tenant, ts);
INSERT INTO requests SELECT number % 10, now() - number, toString(cityHash64(number)),
number % 5000, rand() % 900 FROM numbers(1000000);
-- Bloom filter skips blocks for a needle-in-haystack lookup
EXPLAIN indexes = 1 SELECT * FROM requests WHERE trace_id = toString(cityHash64(42));
-- Projection: a second sort order for per-user queries
ALTER TABLE requests ADD PROJECTION by_user (SELECT * ORDER BY user_id);
ALTER TABLE requests MATERIALIZE PROJECTION by_user SETTINGS mutations_sync = 1;
SELECT count(), avg(ms) FROM requests WHERE user_id = 42; -- 200 (uses by_user)Remember
- Skipping indexes summarize blocks of granules: minmax, set, bloom_filter
- They only pay off when matching values are clustered
- Projections add a re-sorted or pre-aggregated copy chosen automatically
Watch out for
- Adding a bloom_filter index on a column whose values occur in every granule, gaining nothing.