How to CREATE INDEX in ClickHouse

How do I create an index in ClickHouse for faster filtering?

CREATE INDEX adds data-skipping indexes to ClickHouse tables for faster query filtering.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CREATE INDEX do in ClickHouse?

CREATE INDEX defines a data-skipping index that lets ClickHouse quickly exclude data parts not matching filter conditions. It reduces disk reads and speeds up large table scans.

When should I use a ClickHouse index?

Add an index when queries repeatedly filter on the same columns and the column’s cardinality allows effective skipping.Typical targets are date ranges, numeric amounts, and low-to-medium cardinality dimensions.

How do I create an index on one column?

Use ALTER TABLE … ADD INDEX with the column expression and an index type such as minmax, set, or bloom_filter.

Syntax breakdown

ALTER TABLE table ADD INDEX idx_name expression TYPE type(params) GRANULARITY N;

How do I create a composite index?

Wrap multiple columns in a tuple expression.Composite indexes help when filters combine the same columns in WHERE clauses.

How can I drop or replace an index?

DROP INDEX removes it, and REPLACE INDEX recreates it in one step. Both require a mutation and may take time on large tables.

Best practices for ClickHouse indexes

Pick the smallest useful GRANULARITY (usually 1–4) to balance speed and index size. Test with realistic queries and system.parts statistics. Avoid over-indexing—each index increases storage and insertion cost.

Why How to CREATE INDEX in ClickHouse is important

How to CREATE INDEX in ClickHouse Example Usage


-- Speed up queries filtering high-value orders
ALTER TABLE Orders
ADD INDEX idx_total_amount total_amount
TYPE minmax GRANULARITY 4;

-- Example usage
SELECT id, customer_id, total_amount
FROM Orders
WHERE total_amount > 1000;

How to CREATE INDEX in ClickHouse Syntax


-- Single-column index on Orders.total_amount
ALTER TABLE Orders
ADD INDEX idx_orders_total_amount total_amount
TYPE minmax GRANULARITY 4;

-- Composite index on Orders(customer_id, order_date)
ALTER TABLE Orders
ADD INDEX idx_cust_date (customer_id, order_date)
TYPE set(1000) GRANULARITY 2;

-- Drop an existing index
ALTER TABLE Orders DROP INDEX idx_orders_total_amount;

Common Mistakes

Using a high GRANULARITY like 8 or 16. Large granularity stores fewer index blocks, reducing skipping efficiency. Fix by lowering to 1–4.
Indexing high-cardinality columns such as UUIDs. Skipping indexes can’t exclude many granules, wasting space. Instead, rely on primary key order or avoid the index.