How does ORDER BY affect the ClickHouse table and what keys are better to choose?

In ClickHouse, the ORDER BY directive sets the sorting order of data within each component (part) of the table. This affects query efficiency, especially when using data merging and filtering.

How ORDER BY affects:

• Determines the data sorting key on disk.
• Improves query performance with filters on fields from ORDER BY through efficient data skipping.
• Influences the speed of data insertion and merging.

Choosing keys for ORDER BY:

• Select fields that are most frequently used for filtering or grouping data.
• Usually, these are fields with high selectivity (many unique values).
• It is good if the keys reflect the data access logic (e.g., timestamp, user ID).

Example:

CREATE TABLE events (
    event_date Date,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
ORDER BY (event_date, user_id)

Here, sorting by date and user allows quick retrieval of events for a specific day and user.

It is important to remember that a very long or complex ORDER BY key can slow down inserts and increase index size.