TopolearnMergeTree is the engine behind almost every real ClickHouse table, and its ORDER BY decides query speed.
Every ClickHouse table has an engine that decides how data is stored. For real data you almost always use MergeTree or one of its relatives. Its required clause is ORDER BY: rows are stored sorted by these columns, and that sort key doubles as the (sparse) primary index.
What happens on insert: each INSERT writes a new immutable part, a directory with one compressed file per column, sorted by the key. In the background ClickHouse merges small parts into bigger ones, like a log-structured merge tree. That is why the engine is called MergeTree, and why one insert of 100,000 rows is far better than 100,000 inserts of one row: each insert makes a part, and too many parts cause the "Too many parts" error.
Choosing ORDER BY:
- Put the columns you filter on most at the front, usually lower-cardinality ones first, e.g.
(tenant_id, event_type, timestamp). - It does not enforce uniqueness. Two rows with the same key are both kept.
PRIMARY KEYcan be given separately as a prefix ofORDER BY; by default it equals it.
Other engines exist for special cases: Memory (tests), Log (tiny tables), and integration engines such as Kafka, S3 or PostgreSQL that read external systems.
Example
CREATE TABLE events
(
ts DateTime,
user_id UInt64,
event_type LowCardinality(String),
duration UInt32
)
ENGINE = MergeTree
ORDER BY (event_type, ts);
INSERT INTO events VALUES (now(), 1, 'click', 30), (now(), 2, 'view', 12);
INSERT INTO events VALUES (now(), 1, 'click', 45);
-- Each INSERT created one part (until a merge combines them)
SELECT count() FROM system.parts WHERE table = 'events' AND active; -- 2
OPTIMIZE TABLE events FINAL; -- force a merge (don't do this routinely)
SELECT count() FROM system.parts WHERE table = 'events' AND active; -- 1Remember
- MergeTree + ORDER BY is the default table design
- Each INSERT writes an immutable part; background merges combine parts
- The sort key is not a uniqueness constraint
Watch out for
- Inserting rows one at a time from an app loop, creating thousands of tiny parts.