Description:
Cost on sort iterator, does not contain sort cost
The two query only different in with rollup.
If query without rollup, the table scan cost is correct.
If query with rollup, the table scan cost displayed in sort iterator, and not contain the sort cost.
How to repeat:
create table t1(id int not null AUTO_INCREMENT primary key, a1 int, a2 int, a3 int);
insert into t1 (a1, a2, a3) values(rand() * 100, rand() * 1000, rand() * 10000);
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1;
mysql> explain format = tree select id, a1, count(*) from t1 group by id, a1 \G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on <temporary>
-> Aggregate using temporary table
-> Table scan on t1 (cost=51.95 rows=512)
1 row in set (0.00 sec)
mysql> explain format = tree select id, a1, count(*) from t1 group by id, a1 with rollup\G
*************************** 1. row ***************************
EXPLAIN: -> Group aggregate with rollup: count(0)
-> Sort: t1.id, t1.a1 (cost=51.95 rows=512)
-> Table scan on t1
1 row in set (0.00 sec)
Description: Cost on sort iterator, does not contain sort cost The two query only different in with rollup. If query without rollup, the table scan cost is correct. If query with rollup, the table scan cost displayed in sort iterator, and not contain the sort cost. How to repeat: create table t1(id int not null AUTO_INCREMENT primary key, a1 int, a2 int, a3 int); insert into t1 (a1, a2, a3) values(rand() * 100, rand() * 1000, rand() * 10000); insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; insert into t1 (a1, a2, a3) select rand() * 100, rand() * 1000, rand() * 10000 from t1; mysql> explain format = tree select id, a1, count(*) from t1 group by id, a1 \G *************************** 1. row *************************** EXPLAIN: -> Table scan on <temporary> -> Aggregate using temporary table -> Table scan on t1 (cost=51.95 rows=512) 1 row in set (0.00 sec) mysql> explain format = tree select id, a1, count(*) from t1 group by id, a1 with rollup\G *************************** 1. row *************************** EXPLAIN: -> Group aggregate with rollup: count(0) -> Sort: t1.id, t1.a1 (cost=51.95 rows=512) -> Table scan on t1 1 row in set (0.00 sec)