Skip to content

Instantly share code, notes, and snippets.

@den-crane

den-crane/ch_vs_sr.md

Last active July 20, 2023 21:54
Show Gist options
  • Save den-crane/1af949d136b86b8a1cc35585f3a5846c to your computer and use it in GitHub Desktop.
Save den-crane/1af949d136b86b8a1cc35585f3a5846c to your computer and use it in GitHub Desktop.
Download ZIP
ch vs sr
Raw
ch_vs_sr.md

CH 1-node

SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(ind),
    max(ind)
FROM data_table
┌───count()─┬────────min(paramkey)─┬───────max(paramkey)─┬─min(ind)─┬──max(ind)─┐
│ 150000000 │ -9223371870530199340 │ 9223371905719799907 │        0 │ 149999999 │
└───────────┴──────────────────────┴─────────────────────┴──────────┴───────────┘
1 row in set. Elapsed: 0.221 sec. Processed 150.00 million rows, 2.40 GB (679.12 million rows/s., 10.87 GB/s.)

CH 3-nodes

set optimize_distributed_group_by_sharding_key=1;
SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(ind),
    max(ind)
FROM data_table;
┌───count()─┬────────min(paramkey)─┬───────max(paramkey)─┬─min(ind)─┬──max(ind)─┐
│ 150000000 │ -9223371989640639498 │ 9223372002126874435 │        0 │ 149999999 │
└───────────┴──────────────────────┴─────────────────────┴──────────┴───────────┘
1 row in set. Elapsed: 0.059 sec. Processed 150.00 million rows, 2.40 GB (2.54 billion rows/s., 40.71 GB/s.)

SR 3-nodes

MySQL [dw]> SELECT
    ->     count(),
    ->     min(paramkey),
    ->     max(paramkey),
    ->     min(ind),
    ->     max(ind)
    -> FROM data_table;
+-----------+----------------------+---------------------+----------+-----------+
| count()   | min(paramkey)        | max(paramkey)       | min(ind) | max(ind)  |
+-----------+----------------------+---------------------+----------+-----------+
| 150000000 | -9223371870530199340 | 9223371905719799907 |        0 | 149999999 |
+-----------+----------------------+---------------------+----------+-----------+
1 row in set (0.120 sec)

CH 1-node

SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(externalData),
    max(externalData)
FROM data_table1
┌──count()─┬────────min(paramkey)─┬───────max(paramkey)─┬────min(externalData)─┬───max(externalData)─┐
│ 10000000 │ -9223370735592488448 │ 9223371885882326744 │ -9223371118814716114 │ 9223368946794662920 │
└──────────┴──────────────────────┴─────────────────────┴──────────────────────┴─────────────────────┘
1 row in set. Elapsed: 0.020 sec. Processed 10.00 million rows, 160.00 MB (501.27 million rows/s., 8.02 GB/s.)

CH 3-nodes

set optimize_distributed_group_by_sharding_key=1;
SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(externalData),
    max(externalData)
FROM data_table1;
┌──count()─┬────────min(paramkey)─┬───────max(paramkey)─┬────min(externalData)─┬───max(externalData)─┐
│ 10000000 │ -9223371164868333963 │ 9223370554589235599 │ -9223370204256175622 │ 9223366164827200113 │
└──────────┴──────────────────────┴─────────────────────┴──────────────────────┴─────────────────────┘
1 row in set. Elapsed: 0.034 sec. Processed 10.00 million rows, 160.00 MB (293.85 million rows/s., 4.70 GB/s.)

SR 3-nodes

MySQL [dw]> SELECT
    ->     count(),
    ->     min(paramkey),
    ->     max(paramkey),
    ->     min(externalData),
    ->     max(externalData)
    -> FROM data_table1;
+----------+----------------------+---------------------+----------------------+---------------------+
| count()  | min(paramkey)        | max(paramkey)       | min(externalData)    | max(externalData)   |
+----------+----------------------+---------------------+----------------------+---------------------+
| 10000000 | -9223370735592488448 | 9223371885882326744 | -9223371118814716114 | 9223368946794662920 |
+----------+----------------------+---------------------+----------------------+---------------------+
1 row in set (0.016 sec)

CH 1-node

SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(paramname),
    max(paramname)
FROM
(
    SELECT
        paramkey,
        min(1) AS paramname
    FROM data_table
    WHERE ind < 150000000
    GROUP BY paramkey
) AS t

┌───count()─┬────────min(paramkey)─┬───────max(paramkey)─┬─min(paramname)─┬─max(paramname)─┐
│ 150000000 │ -9223371870530199340 │ 9223371905719799907 │              1 │              1 │
└───────────┴──────────────────────┴─────────────────────┴────────────────┴────────────────┘
1 row in set. Elapsed: 6.286 sec. Processed 150.00 million rows, 2.40 GB (23.86 million rows/s., 381.82 MB/s.)

CH 3-nodes

set optimize_distributed_group_by_sharding_key=1;
SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(paramname),
    max(paramname)
FROM
(
    SELECT
        paramkey,
        min(1) AS paramname
    FROM data_table
    WHERE ind < 150000000
    GROUP BY paramkey
) AS t
┌───count()─┬────────min(paramkey)─┬───────max(paramkey)─┬─min(paramname)─┬─max(paramname)─┐
│ 150000000 │ -9223371989640639498 │ 9223372002126874435 │              1 │              1 │
└───────────┴──────────────────────┴─────────────────────┴────────────────┴────────────────┘
1 row in set. Elapsed: 9.252 sec. Processed 150.00 million rows, 2.40 GB (16.21 million rows/s., 259.39 MB/s.)

SR 3-nodes

MySQL [dw]> SELECT
    ->     count(),
    ->     min(paramkey),
    ->     max(paramkey),
    ->     min(paramname),
    ->     max(paramname)
    -> FROM
    -> (
    ->     SELECT
    ->         paramkey,
    ->         min(1) AS paramname
    ->     FROM data_table
    ->     WHERE ind < 150000000
    ->     GROUP BY paramkey
    -> ) AS t
    -> ;
+-----------+----------------------+---------------------+----------------+----------------+
| count()   | min(paramkey)        | max(paramkey)       | min(paramname) | max(paramname) |
+-----------+----------------------+---------------------+----------------+----------------+
| 150000000 | -9223371870530199340 | 9223371905719799907 |              1 |              1 |
+-----------+----------------------+---------------------+----------------+----------------+
1 row in set (0.989 sec)

CH 1-node

SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(t.externalData),
    max(t.externalData)
FROM
(
    SELECT
        leftSide.paramkey,
        leftSide.paramname,
        rightSide.externalData
    FROM
    (
        SELECT
            paramkey,
            min(1) AS paramname
        FROM data_table
        GROUP BY paramkey
    ) AS leftSide
    INNER JOIN data_table1 AS rightSide ON leftSide.paramkey = rightSide.paramkey
) AS t

┌─count()─┬─min(paramkey)─┬─max(paramkey)─┬─min(externalData)─┬─max(externalData)─┐
│       0 │             0 │             0 │                 0 │                 0 │
└─────────┴───────────────┴───────────────┴───────────────────┴───────────────────┘
1 row in set. Elapsed: 6.828 sec. Processed 160.00 million rows, 1.36 GB (23.43 million rows/s., 199.19 MB/s.)

CH 3-nodes

set optimize_distributed_group_by_sharding_key=1;
SELECT
    count(),
    min(paramkey),
    max(paramkey),
    min(t.externalData),
    max(t.externalData)
FROM
(
    SELECT
        leftSide.paramkey,
        leftSide.paramname,
        rightSide.externalData
    FROM
    (
        SELECT
            paramkey,
            min(1) AS paramname
        FROM data_table
        GROUP BY paramkey
    ) AS leftSide
    INNER JOIN data_table1 AS rightSide ON leftSide.paramkey = rightSide.paramkey
) AS t;
┌─count()─┬─min(paramkey)─┬─max(paramkey)─┬─min(externalData)─┬─max(externalData)─┐
│       0 │             0 │             0 │                 0 │                 0 │
└─────────┴───────────────┴───────────────┴───────────────────┴───────────────────┘
1 row in set. Elapsed: 9.819 sec. Processed 160.00 million rows, 1.36 GB (16.30 million rows/s., 138.51 MB/s.)

SR 3-nodes

MySQL [dw]> SELECT
    ->     count(),
    ->     min(paramkey),
    ->     max(paramkey),
    ->     min(t.externalData),
    ->     max(t.externalData)
    -> FROM
    -> (
    ->     SELECT
    ->         leftSide.paramkey,
    ->         leftSide.paramname,
    ->         rightSide.externalData
    ->     FROM
    ->     (
    ->         SELECT
    ->             paramkey,
    ->             min(1) AS paramname
    ->         FROM data_table
    ->         GROUP BY paramkey
    ->     ) AS leftSide
    ->     INNER JOIN data_table1 AS rightSide ON leftSide.paramkey = rightSide.paramkey
    -> ) AS t;
+---------+---------------+---------------+---------------------+---------------------+
| count() | min(paramkey) | max(paramkey) | min(t.externalData) | max(t.externalData) |
+---------+---------------+---------------+---------------------+---------------------+
|       0 |          NULL |          NULL |                NULL |                NULL |
+---------+---------------+---------------+---------------------+---------------------+
1 row in set (0.142 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment