Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
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