MySQL 3 minutes vs ClickHouse 0.3 seconds, the same query

ClickHouse stores data by column rather than by row as MySQL does, so it reads only the columns needed for aggregation. This reduces unnecessary I/O for analytical queries like SUM, COUNT, and AVG and boosts performance on large-scale lookups.

The design philosophy also differs. MySQL suits OLTP, where single-row lookups, INSERT/UPDATE/DELETE, and transactional consistency matter, while ClickHouse suits OLAP, where you need to quickly read billions to trillions of rows, as in logs, statistics, and reports.

Suitable patterns are as follows.

• Date range + aggregation, such as summing daily or monthly revenue
• Conditional COUNT over tens of millions of event log entries
• Multiple GROUP BY by service, date, and type

Conversely, MySQL is better suited for single-row lookups, frequent UPDATE/DELETE, and transactions such as payments and orders. In ClickHouse, UPDATE is based on ReplacingMergeTree and DELETE is handled via Mutation, which is disadvantageous for immediacy or small-scale edits.

The performance test was conducted on 50 million event log entries (6 columns). The comparison used the same data, the same query, and the same server specs, with the premise adjusted for the fact that MySQL's PK and ClickHouse's ORDER BY sort key play fundamentally different roles. The results were as follows.

• Monthly aggregation: MySQL 49 seconds, ClickHouse 0.2 seconds
• Triple GROUP BY: MySQL 208 seconds, ClickHouse 0.3 seconds
• COUNT/DISTINCT: MySQL 67 seconds, ClickHouse 0.2 seconds
• View lookup: MySQL 94 seconds, ClickHouse 0.398 seconds
• Materialized View: 0.1 seconds
• 10 concurrent requests: MySQL average 48 seconds, ClickHouse 0.9 seconds
• Storage capacity: from 5.6GB to 2.6GB
• Bulk loading: from 849 seconds to 49 seconds

MySQL syntax compatibility was also confirmed. DATE_FORMAT, IF, CASE WHEN, LIKE, IN, BETWEEN, HAVING, and subqueries all worked, and connections were also possible via the MySQL Client (port 7004).

Within NHN Cloud, ClickHouse has been applied to Notification Hub, Cab-Verify, and Resource Watcher. Notification Hub loads Kakao Biz Center statistics into ClickHouse to provide aggregation under various conditions, Cab-Verify manages up to 100 million token authentication history records while preparing to scale beyond 1 billion, and Resource Watcher separated out batch lookups to reduce MySQL slow queries and failover risk. The conclusion is clear: the most effective setup has MySQL handle transactions where consistency matters and ClickHouse handle large-scale aggregation and analysis.