mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 04:46:15 +01:00
7a77b221f1
Condition can be pushed from the HAVING clause into the WHERE clause if it depends only on the fields that are used in the GROUP BY list or depends on the fields that are equal to grouping fields. Aggregate functions can't be pushed down. How the pushdown is performed on the example: SELECT t1.a,MAX(t1.b) FROM t1 GROUP BY t1.a HAVING (t1.a>2) AND (MAX(c)>12); => SELECT t1.a,MAX(t1.b) FROM t1 WHERE (t1.a>2) GROUP BY t1.a HAVING (MAX(c)>12); The implementation scheme: 1. Extract the most restrictive condition cond from the HAVING clause of the select that depends only on the fields that are used in the GROUP BY list of the select (directly or indirectly through equalities) 2. Save cond as a condition that can be pushed into the WHERE clause of the select 3. Remove cond from the HAVING clause if it is possible The optimization is implemented in the function st_select_lex::pushdown_from_having_into_where(). New test file having_cond_pushdown.test is created.
83 lines
2.8 KiB
Text
83 lines
2.8 KiB
Text
create table t2 (a int);
|
|
insert into t2 values (1),(2),(3);
|
|
create view v2 as select a from t2;
|
|
flush status;
|
|
select * from v2;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
show status like '%Created_tmp%';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
Created_tmp_files 0
|
|
Created_tmp_tables 0
|
|
explain select * from v2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
|
|
select * from (select * from t2) T1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
show status like '%Created_tmp%';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
Created_tmp_files 0
|
|
Created_tmp_tables 0
|
|
explain select * from (select * from t2) T1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
|
|
drop view v2;
|
|
drop table t2;
|
|
CREATE TABLE t1(a int);
|
|
INSERT INTO t1 values(1),(2);
|
|
CREATE TABLE t2(a int);
|
|
INSERT INTO t2 values(1),(2);
|
|
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 test.t1.a 1
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary
|
|
truncate table performance_schema.events_statements_history_long;
|
|
flush status;
|
|
CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
|
|
# Performance schema should be the same as "Created_tmp_tables" variable below
|
|
select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
|
|
sum(created_tmp_tables)
|
|
2
|
|
show status like '%Created_tmp%';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
Created_tmp_files 0
|
|
Created_tmp_tables 2
|
|
drop table t3;
|
|
EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
|
|
truncate table performance_schema.events_statements_history_long;
|
|
flush status;
|
|
CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
|
|
# Performance schema should be the same as "Created_tmp_tables" variable below
|
|
select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
|
|
sum(created_tmp_tables)
|
|
1
|
|
show status like '%Created_tmp%';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
Created_tmp_files 0
|
|
Created_tmp_tables 1
|
|
drop table t1,t2,t3;
|
|
truncate table performance_schema.events_statements_history_long;
|
|
flush status;
|
|
# Performance schema should be the same as "Created_tmp_tables" variable below
|
|
select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
|
|
sum(created_tmp_tables)
|
|
0
|
|
show status like '%Created_tmp%';
|
|
Variable_name Value
|
|
Created_tmp_disk_tables 0
|
|
Created_tmp_files 0
|
|
Created_tmp_tables 0
|