1BRC with MySQL

[ftisiot]

After trying with PostgreSQL and ClickHouse I also tried with MySQL. The results are quite slower (~40min in MySQL vs ~8 min on PostgreSQL and 44.4 sec on ClickHouse)... do you have any suggestions on how to improve?

WITH AGG AS(
    SELECT city,
           MIN(temperature) min_measure,
           cast(AVG(temperature) AS DECIMAL(8,1)) mean_measure,
           MAX(temperature) max_measure
    FROM test
    GROUP BY city
    )
SELECT GROUP_CONCAT(CITY, '=', CONCAT(min_measure,'/', mean_measure,'/', max_measure) ORDER BY CITY SEPARATOR ', ')
FROM AGG
;

[hubi-hubi]

Hello
get the the load into the table faster https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-parallel-table.html may help as it can read the file with more threads.

storing the data in a partitioned table (by city) so creating the report parts could run in parallel on those partitions.
storing the reports lines into a table and run a statement against it to generate the wanted output format after all partitions are done.
to get this all going in parallel events could be used
but for the partitions part we would need the list of cities beforehand to create the partitions.
We could partition by key (city) so we do not need the city names, but the count beforehand and perhaps some more work to get the results sorted. if the generated .csv varies in city count this would not work without these informations which in a real live version may be known.