ClickHouse vs MySQL

mmedojevic~March 9, 2020 /Uncategorized

In this article, we are going to benchmark ClickHouse and MySQL databases. Sample database table contains over 10,000,000 records. It has composite primary key (as_on_date, customer_number, collector_number, business_unit_id and country). There is a trxn_amount field besides composite primary key. It contains transaction amount.

Toral number of records: 11,091,713
Average records per month: 231,077
Number of months: 48

MySQL create table statement:

ClickHouse create table statement:

Data:

We are going to execute query which generates report based on as_on_date and business_unit_id. Query should return sum of transaction amount, average transaction amount and count of transactions per unique as_on_date and business_unit_id combination.

Following query is used for data extraction:

ClickHouse:

MySQL:

ClickHouse execution time: 0.119s
MySQL executin time: 31.765s
ClickHouse is 267 times faster than MySQL. Even though MySQL query uses index it still has to iterate trough hundreds of thousands records in order to apply aggregate functions. Columnar databases are significantly faster for grouping and aggregating large amount of data.

Please follow and like us: