|
A reply to igor's comment: https://jira.mariadb.org/browse/MDEV-26337?focusedCommentId=209666&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-209666
Ok so I create the tables described and fill them with data:
create table t1 (a int, b int, c int);
|
create table t2 (a int, b int, c int, key (a,b));
|
# 100 groups x 1K elements
|
insert into t2
|
select
|
A.seq,
|
B.seq,
|
C.seq
|
from
|
seq_0_to_9 A,
|
seq_0_to_9 B,
|
seq_0_to_999 C;
|
|
insert into t1 select
|
seq, seq, seq
|
from
|
seq_1_to_10;
|
Then, I run the provided query (adjusted it a bit to avoid syntax errors):
explain
|
select s from t1,
|
(select a,b,sum(c) as s
|
from t2 group by a,b) dt
|
where
|
t1.a=dt.a and
|
t1.b=dt.b and
|
t1.a in (1,3,5) and
|
t1.b between 2 and 4;
|
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 10 | j2.t1.a,j2.t1.b | 2 | |
|
| 2 | LATERAL DERIVED | t2 | ref | a | a | 10 | j2.t1.a,j2.t1.b | 1 | Using index condition |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------+
|
Indeed, there's no sorting done.
But if I add WITH ROLLUP, I see that the subquery now uses "Using filesort":
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 10 | j2.t1.a,j2.t1.b | 2 | |
|
| 2 | LATERAL DERIVED | t2 | ref | a | a | 10 | j2.t1.a,j2.t1.b | 1 | Using where; Using filesort |
|
+------+-----------------+------------+------+---------------+------+---------+-----------------+------+-----------------------------+
|
|