DEVHIDE
Login Or Sign up

Mariadb MySQL count distinct group by performance

130 Views Asked by At

Mariadb verion

select version();
version()                                |
-----------------------------------------+
10.4.24-MariaDB-1:10.4.24+maria~focal-log|

I have a table like the following sql. col a mean version. col b,c,d,e seems like database,schema,table,field.

CREATE TABLE tt (a int, b varchar(32), c varchar(64), d varchar(64), e varchar(64), f int) 
CREATE INDEX tt_a_IDX USING BTREE ON tt (a,b,c,d,e);

table count is 510114

The first question is about the difference between 2 querys when getting col e count in the specific version.

sql1. select count(DISTINCT c,d,e),b from tt where a = 1 group by b;
sql2. select sum(count), b from (
                select b,COUNT(DISTINCT e) as count from tt 
                where a = 1
                GROUP BY  b,c,d) tt group by b;

id|select_type|table|type|possible_keys|key     |key_len|ref  |rows  |Extra                   |
--+-----------+-----+----+-------------+--------+-------+-----+------+------------------------+
 1|SIMPLE     |tt   |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using index|

id|select_type|table     |type|possible_keys|key     |key_len|ref  |rows  |Extra                          |
--+-----------+----------+----+-------------+--------+-------+-----+------+-------------------------------+
 1|PRIMARY    |<derived2>|ALL |             |        |       |     |253768|Using temporary; Using filesort|
 2|DERIVED    |tt        |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using index       |

1 cost 4s on average, 2 cost only hundreds of ms;

q1. Why second sql is faster? So does this mean count(distinct mutil cols...) can replace by "group by mutil cols and sum" for the permance?

The second question is when i add column a in the group by condition, the plan show a range type select. but in fact, its cost more time.

sql3. select sum(count), b from (
                select b,COUNT(DISTINCT e) as count from tt 
                where a = 1
                GROUP BY  a,b,c,d) temp group by b;

id|select_type|table     |type |possible_keys|key     |key_len|ref|rows  |Extra                                           |
--+-----------+----------+-----+-------------+--------+-------+---+------+------------------------------------------------+
 1|PRIMARY    |<derived2>|ALL  |             |        |       |   |253768|Using temporary; Using filesort                 |
 2|DERIVED    |tt        |range|tt_a_IDX     |tt_a_IDX|689    |   |253768|Using where; Using index for group-by (scanning)|

cost about 3s on average;

q2. What happens?

since the key_len show in the plan, i drop the index and make a new index only in col a

explain select count(DISTINCT c,d,e),b from tt where a = 1 group by b  ;
explain select sum(count), b from (
                select b,COUNT(DISTINCT e) as count from tt 
                where a = 1
                GROUP BY  b,c,d) temp group by b ;
id|select_type|table|type|possible_keys|key     |key_len|ref  |rows  |Extra                      |
--+-----------+-----+----+-------------+--------+-------+-----+------+---------------------------+
 1|SIMPLE     |tt   |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using filesort|
id|select_type|table     |type|possible_keys|key     |key_len|ref  |rows  |Extra                          |
--+-----------+----------+----+-------------+--------+-------+-----+------+-------------------------------+
 1|PRIMARY    |<derived2>|ALL |             |        |       |     |253768|Using temporary; Using filesort|
 2|DERIVED    |tt        |ref |tt_a_IDX     |tt_a_IDX|5      |const|253768|Using where; Using filesort    |

in the explain,there is little difference, but two query cost more, about 5s, 4s on average

q3. Does this mean index (a,b,c,d,e) make effect in fact, not like the plan show(key_len)?

analyze result:

ANALYZE FORMAT=JSON select count(DISTINCT c,d,e),b 
from tt where a = 1 group by b; 

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 3222.2,
    "table": {
      "table_name": "tt",
      "access_type": "ref",
      "possible_keys": ["tt_a_IDX"],
      "key": "tt_a_IDX",
      "key_length": "5",
      "used_key_parts": ["a"],
      "ref": ["const"],
      "r_loops": 1,
      "rows": 253768,
      "r_rows": 510114,
      "r_total_time_ms": 395.73,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "tt.a <=> 1",
      "using_index": true
    }
  }
}

ANALYZE FORMAT=JSON select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY b, c, d
    ) temp
group by b;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 662.82,
    "filesort": {
      "sort_key": "tt.b",
      "r_loops": 1,
      "r_total_time_ms": 0.0086,
      "r_limit": 200,
      "r_used_priority_queue": false,
      "r_output_rows": 16,
      "r_buffer_size": "2Kb",
      "temporary_table": {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 253768,
          "r_rows": 2652,
          "r_total_time_ms": 0.1939,
          "filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "r_loops": 1,
              "r_total_time_ms": 661.61,
              "table": {
                "table_name": "tt",
                "access_type": "ref",
                "possible_keys": ["tt_a_IDX"],
                "key": "tt_a_IDX",
                "key_length": "5",
                "used_key_parts": ["a"],
                "ref": ["const"],
                "r_loops": 1,
                "rows": 253768,
                "r_rows": 510114,
                "r_total_time_ms": 245.18,
                "filtered": 100,
                "r_filtered": 100,
                "attached_condition": "tt.a <=> 1",
                "using_index": true
              }
            }
          }
        }
      }
    }
  }
}

ANALYZE FORMAT=JSON select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY a,b, c, d
    ) temp
group by b;

{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 3401.7,
    "filesort": {
      "sort_key": "tt.b",
      "r_loops": 1,
      "r_total_time_ms": 0.0093,
      "r_limit": 200,
      "r_used_priority_queue": false,
      "r_output_rows": 16,
      "r_buffer_size": "2Kb",
      "temporary_table": {
        "table": {
          "table_name": "<derived2>",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 253768,
          "r_rows": 2652,
          "r_total_time_ms": 0.406,
          "filtered": 100,
          "r_filtered": 100,
          "materialized": {
            "query_block": {
              "select_id": 2,
              "r_loops": 1,
              "r_total_time_ms": 3400.5,
              "table": {
                "table_name": "tt",
                "access_type": "range",
                "possible_keys": ["tt_a_IDX"],
                "key": "tt_a_IDX",
                "key_length": "689",
                "used_key_parts": ["a", "b", "c", "d", "e"],
                "r_loops": 1,
                "rows": 253768,
                "r_rows": 510071,
                "r_total_time_ms": 3091.2,
                "filtered": 100,
                "r_filtered": 100,
                "attached_condition": "tt.a = 1",
                "using_index_for_group_by": "scanning"
              }
            }
          }
        }
      }
    }
  }
}

update1 : i try to import data from mariadb to mysql8. three sql show no obvious difference.

update2 : explain analyze result in mysql8

EXPLAIN ANALYZE select count(DISTINCT c,d,e),b 
from tt where a = 1 group by b; 
-> Group aggregate: count(distinct tt.c,tt.d,tt.e)  (cost=71893.42 rows=253768) (actual time=0.882..3453.691 rows=16 loops=1)
    -> Covering index lookup on tt using tt_a_IDX (a=1)  (cost=46516.62 rows=253768) (actual time=0.030..411.063 rows=510114 loops=1)

EXPLAIN ANALYZE select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY b, c, d
    ) temp
group by b;
-> Table scan on <temporary>  (actual time=1113.183..1113.185 rows=16 loops=1)
    -> Aggregate using temporary table  (actual time=1113.182..1113.182 rows=16 loops=1)
        -> Table scan on temp  (cost=97270.23..100444.82 rows=253768) (actual time=1111.180..1111.563 rows=2652 loops=1)
            -> Materialize  (cost=97270.22..97270.22 rows=253768) (actual time=1111.177..1111.177 rows=2652 loops=1)
                -> Group aggregate: count(distinct tt.e)  (cost=71893.42 rows=253768) (actual time=0.153..1109.965 rows=2652 loops=1)
                    -> Covering index lookup on tt using tt_a_IDX (a=1)  (cost=46516.62 rows=253768) (actual time=0.043..398.208 rows=510114 loops=1)

EXPLAIN ANALYZE select sum(count), b
from
    (
    select b, COUNT(DISTINCT e) as count
    from tt where a = 1 GROUP BY a,b, c, d
    ) temp
group by b;
-> Table scan on <temporary>  (actual time=4762.307..4762.309 rows=16 loops=1)
    -> Aggregate using temporary table  (actual time=4762.306..4762.306 rows=16 loops=1)
        -> Table scan on temp  (cost=76130.41..79305.00 rows=253768) (actual time=4760.310..4760.708 rows=2652 loops=1)
            -> Materialize  (cost=76130.40..76130.40 rows=253768) (actual time=4760.308..4760.308 rows=2652 loops=1)
                -> Group aggregate: count(distinct tt.e)  (cost=50753.60 rows=253768) (actual time=0.388..4757.575 rows=2652 loops=1)
                    -> Filter: (tt.a = 1)  (cost=25376.80 rows=253768) (actual time=0.055..4593.786 rows=510071 loops=1)
                        -> Covering index skip scan for deduplication on tt using tt_a_IDX over (a = 1)  (cost=25376.80 rows=253768) (actual time=0.051..4535.554 rows=510071 loops=1)

update 3: data distribution

select count(distinct a) from tt;
count(distinct a)|
-----------------+
                1|
mark: there is only one value for col a, which is 1.

select count(distinct a,b) from tt;
count(distinct a,b)|
-------------------+
                 16|

select count(distinct a,b,c) from tt;
count(distinct a,b,c)|
---------------------+
                   28|

select count(distinct a,b,c,d) from tt;
count(distinct a,b,c,d)|
-----------------------+
                   2652|

select count(distinct a,b,c,d,e) from tt;
count(distinct a,b,c,d,e)|
-------------------------+
                   510071|

select count(distinct a,b,c,d,e,f) from tt;
count(distinct a,b,c,d,e,f)|
---------------------------+
                      49680|
sql mysql performance mariadb explain
Original Q&A
1

There are 1 best solutions below

0
danblack On

The implementation of the query COUNT(DISTINCT ..) in the q1 maintains a tree of the element encountered when walking the index and counting the result at the end. When walking such a list in order, the difference to the previous element walked only needs to be considered.

This could be implemented better and I wrote up the feature requests MDEV-32870. An existing feature request MDEV-10922 exists also that applies to a primary key (which could discard any internals and treat as COUNT(*)).

The other queries managed to generate smaller set at a time so managed to be quicker.

Related Questions in SQL

Related Questions in MYSQL

Related Questions in PERFORMANCE

Related Questions in MARIADB

Related Questions in EXPLAIN

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.