Typorithm · hard

Data Skipping Index

data-skipping-indexclickhouseskip-indexminmaxbloom-filter

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Data Skipping Index

Indexing & Partitioning

A data skipping index like minmax (or bloom_filter) on a non-key column lets ClickHouse skip granule blocks where the value provably cannot match.

Secondary, or data skipping, indexes summarize a non-ORDER-BY column over blocks of GRANULARITY granules, so the engine can skip blocks that cannot contain a match.

A minmax index stores the min and max of the column per block; a bloom_filter index answers set-membership for equality and IN filters.

Skip indexes are added with ALTER TABLE ... ADD INDEX and built over existing rows with MATERIALIZE INDEX; they help only when the column correlates with the sort order.

Skip index summarizes a non-key column.

minmax skips by range; bloom_filter by membership.

GRANULARITY groups granules per index block.

ALTER TABLE logs
    ADD INDEX idx_status status TYPE minmax GRANULARITY 4;

ALTER TABLE logs MATERIALIZE INDEX idx_status;

SELECT count()
FROM logs
WHERE status = 500;

Read the implementation

Connect each part of the code to the concept before you type.

1. Add a skip index

ADD INDEX attaches a secondary skip index on a non-key column.

ADD INDEX idx_status status TYPE minmax GRANULARITY 4

2. Skip-index type

minmax stores the min and max of each block so non-matching blocks are skipped.

TYPE minmax

3. Build for existing data

MATERIALIZE builds the skip index over rows already in the table.

MATERIALIZE INDEX idx_status

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.