How ClickHouse Boosted Query Speed 200×: A Practical Migration Guide

What is ClickHouse?

ClickHouse is a column‑oriented database management system designed for online analytical processing (OLAP).

First, let’s clarify some basic concepts:

OLTP: Traditional relational databases focused on insert, update, delete, and query operations with strong transaction consistency (e.g., banking, e‑commerce systems).

OLAP: Warehouse‑type databases mainly used for reading data and performing complex analytical queries to support decision‑making.

Next, we illustrate the difference between column‑store and row‑store databases.

In traditional row‑store systems (MySQL, PostgreSQL, MS SQL Server), data is stored row by row:

In column‑store systems (ClickHouse), data is stored column by column:

The two storage methods differ in I/O patterns and compression efficiency.

That concludes the basic introduction to ClickHouse; further details can be found in the official manual.

Business Problem

The existing MySQL database holds a 50‑million‑row main table and two auxiliary tables. A single join query takes over 3 minutes. After index tuning, sharding, and logical optimization, performance gains were limited, prompting a migration to ClickHouse.

After optimization, query time dropped to under 1 second, achieving a 200‑fold speed increase.

ClickHouse Practice

1. Installing ClickHouse on macOS

Installation was performed via Docker; alternatively, one can compile from source, which is more involved.

2. Data Migration: MySQL → ClickHouse

ClickHouse supports most MySQL syntax, making migration low‑cost. Five migration approaches are available:

CREATE TABLE ENGINE MySQL – keep data in MySQL and map it.

INSERT INTO … SELECT … – create the table first, then import.

CREATE TABLE AS SELECT … – create and import simultaneously.

CSV offline import.

StreamSets.

We chose the third method (CREATE TABLE AS SELECT) for migration:

CREATE TABLE [IF NOT EXISTS] db.table_name ENGINE = MergeTree AS SELECT * FROM mysql('host:port','db','database','user','password')

3. Performance Test Comparison

Type

Data Volume

Table Size

Query Speed

MySQL

50 million

10 GB

205 s

ClickHouse

50 million

600 MB

≤ 1 s

4. Data Synchronization Schemes

Temporary Table

Use a temporary table to fully sync MySQL data into ClickHouse, then replace the original table. Suitable for moderate data volumes with frequent incremental changes.

Synch

Open‑source sync tool synch reads MySQL binlog, extracts SQL statements, and processes tasks via a message queue.

5. Why Is ClickHouse Fast?

Only the required columns are read, reducing I/O compared to row‑wise reads.

Uniform column types enable up to ten‑fold compression, further lowering I/O.

ClickHouse applies specialized search algorithms based on storage scenarios.

Pitfalls Encountered

1. Data Type Differences Between ClickHouse and MySQL

Direct MySQL queries caused errors. The fix was to cast IDs to a common unsigned type:

LEFT JOIN B b ON toUInt32(h.id) = toUInt32(ec.post_id)

2. Delete/Update Operations Are Asynchronous

Even with the MergeTree engine, ClickHouse guarantees only eventual consistency. For strict consistency, a full data sync is recommended.

Conclusion

By adopting ClickHouse, the MySQL query bottleneck was eliminated; queries on datasets up to 2 billion rows return results within one second for 90 % of cases. ClickHouse also scales to clusters for larger volumes, making it a compelling solution for high‑performance analytics.