How ClickHouse Cut MySQL Query Time 200× – A Practical Migration Guide

What is ClickHouse?

ClickHouse is a column‑oriented DBMS designed for online analytical processing (OLAP).

It was open‑sourced by Yandex and can process billions of rows per second, far faster than traditional row‑based databases.

Business Problem

Our application stored a 50 million‑row table plus two auxiliary tables in MySQL. A single join query took over 3 minutes. After indexing, sharding, and logical optimizations the performance was still unsatisfactory, so we migrated to ClickHouse.

After migration the query time dropped to under 1 second, a 200‑fold improvement.

ClickHouse Practice

1. Installing ClickHouse on macOS

We used the Docker image for quick setup; building from source is also possible.

2. Data migration from MySQL to ClickHouse

ClickHouse supports most MySQL syntax, and five migration approaches are available:

create table engine mysql – keep data in MySQL

insert‑into‑select – create table first, then import

create‑table‑as‑select – create and import in one step

offline CSV import

streamsets – real‑time sync via binlog

We chose the third method (CREATE TABLE AS SELECT) and executed the following statement:

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

3. Performance comparison

On a 50 million‑row dataset (≈10 GB in MySQL, 600 MB in ClickHouse) the MySQL query took 205 seconds, while ClickHouse returned the result in less than one second.

4. Data synchronization strategies

1) Temporary table

Load the full MySQL dataset into a temporary ClickHouse table, then swap it with the production table. Suitable for moderate data volumes and frequent incremental changes.

2) synch

Use the open‑source synch tool, which reads MySQL binlog events, converts them to SQL, and pushes them to ClickHouse via a message queue.

5. Why ClickHouse is fast

Only the columns required for a query are read, reducing I/O.

Columnar storage enables high compression (up to ten‑fold), further lowering I/O.

Specialized storage engines apply adaptive indexing and search algorithms.

Pitfalls

1. Data type differences

Some MySQL queries fail in ClickHouse; casting to unsigned integers (e.g., toUInt32) resolves the issue.

2. Asynchronous deletes/updates

ClickHouse’s MergeTree engine guarantees eventual consistency; for strict consistency a full data reload is recommended.

Conclusion

By migrating the heavy‑query workload to ClickHouse we eliminated the MySQL bottleneck: queries on sub‑billion‑row tables now finish within one second, and the system can be scaled out with ClickHouse clusters for larger volumes.