Explain Statements Using Aggregation

The aggregation may be used in SQL, and the execution plan of the aggregate query can be interpreted through EXPLAIN statements.

Example

We have prepared a simple example to help you understand the execution plan for interpreting the Aggregation using EXPLAIN.

  1. > drop table if exists t1;
  2. > create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  3. > insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  4. > insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  5. > insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  6. > insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  7. > insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  8. > insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  9. > insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  10. > insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  11. > insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');

Hash Aggregation

The Hash Aggregation algorithm uses a hash table to store intermediate results while performing aggregation. It executes in parallel using multiple threads but consumes more memory than Stream Aggregation.

The following is an example of the Hash Aggregation operator:

  1. > SELECT /*+ HASH_AGG() */ count(*) FROM t1;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |        9 |
  6. +----------+
  7. 1 row in set (0.01 sec)
  9. mysql> EXPLAIN SELECT /*+ HASH_AGG() */ count(*) FROM t1;
  10. +-------------------------------------------+
  11. | QUERY PLAN                                |
  12. +-------------------------------------------+
  13. | Project                                   |
  14. |   ->  Aggregate                           |
  15. |         Aggregate Functions: starcount(1) |
  16. |         ->  Table Scan on db1.t1          |
  17. +-------------------------------------------+
  18. 4 rows in set (0.01 sec)