Tables and ORDER BY Keys

Creating Tables and Choosing an ORDER BY Key

ClickHouse 29,491 types are explicit and narrow: UInt8 for a book id, LowCardinality(String) to dictionary-encode a genre, Decimal(10, 2) for money. The ORDER BY key is the most important decision, because it is both the sort order on disk and the sparse primary index. BookNest's 10x sales table (Views, Partitions, Parallelism) loads from Parquet 129 :

1443-tables.sql: the sales table, sorted by genre and dateSQL

CREATE DATABASE IF NOT EXISTS booknest;
DROP TABLE IF EXISTS booknest.sales_x10;
CREATE TABLE booknest.sales_x10 (
  order_id UInt32, line_no UInt8, order_date Date, channel LowCardinality(String),
  status LowCardinality(String), coupon LowCardinality(Nullable(String)), customer_id UInt32,
  country LowCardinality(String), book_id UInt8, title LowCardinality(String),
  author LowCardinality(String), genre LowCardinality(String), qty UInt16,
  unit_price Decimal(6, 2), gross_amount Decimal(10, 2))
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (genre, order_date, order_id);
INSERT INTO booknest.sales_x10 SELECT * EXCEPT k
FROM file('booknest/files/ch/sales_x10.parquet', Parquet);
OPTIMIZE TABLE booknest.sales_x10 FINAL;                 -- one part per monthly partition
SELECT count() AS rows, sum(gross_amount) AS gross, uniqExact(_partition_id) AS partitions,
       (SELECT count() FROM system.parts WHERE table = 'sales_x10' AND active) AS parts
FROM booknest.sales_x10;
SELECT formatReadableSize(sum(data_compressed_bytes)) AS on_disk,
       formatReadableSize(sum(data_uncompressed_bytes)) AS raw
FROM system.parts WHERE database = 'booknest' AND table = 'sales_x10' AND active;

Output

   ┌────rows─┬──────gross─┬─partitions─┬─parts─┐
1. │ 1379440 │ 35099591.9 │         18 │    18 │
   └─────────┴────────────┴────────────┴───────┘
   ┌─on_disk──┬─raw───────┐
1. │ 7.75 MiB │ 43.48 MiB │
   └──────────┴───────────┘

The 1.38 million rows compress 5.6 to 1. EXPLAIN indexes = 1 (1443-orderby.sql) shows what the key buys: a filter on genre = 'Travel' and one March read 2 of 178 granules, as the partition pruned 17 of 18 parts and the primary key 8 of the remaining 10 granules, while a filter on customer_id read all 178. Put filtered, low-cardinality columns first and time after them; keep partitions monthly at most, as ClickHouse's documentation advises. A second access path needs a projection or a skip index.

MENU