Subchapter 4.29

Schema Pk Filter On Orderby

rules/schema-pk-filter-on-orderby.mdMarkdown2 KBView on GitHub

Filter on ORDER BY Columns in Queries

Impact: CRITICAL

Even with good schema design, queries must use ORDER BY columns to benefit. Skipping prefix columns or filtering on non-ORDER BY columns prevents index usage.

Schema Pk Filter On Orderby · Clickhouse Best Practices · ClickHouse/agent-skills · Skills Docs

-- Given: ORDER BY (tenant_id, event_type, timestamp)

-- Skips prefix columns - can't use index effectively
SELECT * FROM events WHERE event_type = 'click';

-- Filter on column not in ORDER BY - full table scan
SELECT * FROM events WHERE user_agent LIKE '%Chrome%';

-- Given: ORDER BY (tenant_id, event_type, timestamp)

-- Full prefix match - best performance
SELECT * FROM events
WHERE tenant_id = 123 AND event_type = 'click';

-- Partial prefix - still uses index
SELECT * FROM events WHERE tenant_id = 123;

-- Range on later column after equality on earlier
SELECT * FROM events
WHERE tenant_id = 123 AND event_type = 'click' AND timestamp >= '2024-01-01';

Filter	Index Used?
`WHERE tenant_id = 123`	Full
`WHERE tenant_id = 123 AND event_type = 'click'`	Full
`WHERE event_type = 'click'`	None (skipped prefix)
`WHERE timestamp > '2024-01-01'`	None (skipped both)