|
At the moment optimizer does window function materialization even if join produces no rows:
create or replace table t1 (a int, b int, c int, index(a, b, c));
|
create or replace table t2 (a int, b int, c int, index(a, b, c));
|
|
insert into t1 select seq, seq, seq from seq_1_to_500000;
|
insert into t2 select seq, seq, seq from seq_1_to_500000;
|
|
explain
|
select * from
|
(
|
select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )
|
from t1 join t2
|
where
|
t1.a=t2.a and t1.b=t2.b
|
) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);
|
+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
|
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 15 | const,const,const | 10 | |
|
| 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
|
| 2 | DERIVED | t1 | index | a | a | 15 | NULL | 500000 | Using where; Using index; Using temporary |
|
| 2 | DERIVED | t2 | ref | a | a | 10 | test.t1.a,test.t1.b | 1 | Using index |
|
+------+-------------+------------+--------+---------------+------+---------+---------------------+--------+-------------------------------------------+
|
MariaDB [test]> select count(*) from
|
-> (
|
-> select t1.a a, t2.b b, t2.c, sum(t2.c) over ( partition by a,b,c )
|
-> from t1 join t2
|
-> where
|
-> t1.a=t2.a and t1.b=t2.b
|
-> ) v1 join ( select 1000000 as a, 1000000 as b, 1000000 as c ) v2 using (a,b,c);
|
+----------+
|
| count(*) |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (2.27 sec)
|
For good performance it is required to skip materialization at all if no rows are joined and try to push condition if only few rows are joined like in MDEV-13385
|