[MDEV-15504] Use group by optimization, if where condition includes the whole table or partition Created: 2018-03-07  Updated: 2019-03-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer, Partitioning
Fix Version/s: None

Type: Task Priority: Major
Reporter: Richard Stracke Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hello,

this feature request based on

https://bugs.mysql.com/bug.php?id=89870

In the following both scenarios group by optimizazion will not used.

With partition:

drop table if exists t;
 
CREATE TABLE t (
 
id int NOT NULL auto_increment,
 
c int not null,
 
d int not null,
 
PRIMARY KEY (id,c),
 
key(d)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
PARTITION BY RANGE COLUMNS(c)
 
(
 
PARTITION p1 VALUES LESS THAN (1),
 
PARTITION p2 VALUES LESS THAN (2),
 
PARTITION p3 VALUES LESS THAN (3),
 
PARTITION pmax VALUES LESS THAN (MAXVALUE)
 
);
 
 
 
insert into t(c,d) values(rand()*3, rand()*4);
 
insert into t(c,d) values(rand()*3, rand()*4);
 
insert into t(c,d) values(rand()*3, rand()*4);
 
 
 
replace into t(c,d) select rand()*3, rand()*4 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10;
 
analyze table t;

Without partition:

drop table if exists t;
 
CREATE TABLE t (
 
id int NOT NULL auto_increment,
 
c int not null,
 
d int not null,
 
PRIMARY KEY (id,c),
 
key(d)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
;
 
 
 
insert into t(c,d) values(rand()*3,1);
 
insert into t(c,d) values(rand()*3,1);
 
insert into t(c,d) values(rand()*3,1);
 
 
 
replace into t(c,d) select rand()*3, rand()*4 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10;
 
analyze table t;

In both cases
group-by optimization will not used without adding c to index key(d) --> key(d,c)
and group by clause from "group by d" to "group by d,c")

Is it possible to enhance the optimizer to check, if the where condition include the whole table (or partition in the first case), so group-by optimization is feasible ?

Richard

 Comments   
Comment by Igor Babaev [ 2019-03-28 ]

Richard,
What do you mean by "group by optimization"?

Generated at Thu Feb 08 08:21:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.