summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/group_min_max.result28
-rw-r--r--mysql-test/t/group_min_max.test17
-rw-r--r--sql/opt_range.cc13
3 files changed, 57 insertions, 1 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 25cd4a25279..34e1e256faa 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -3733,5 +3733,33 @@ id MIN(a) MAX(a)
4 2001-01-04 2001-01-04
DROP TABLE t1;
#
+# Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
+# and use_stat_tables= PREFERABLY
+#
+CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b));
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
+(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @save_use_stat_tables= @@use_stat_tables;
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables=PREFERABLY;
+explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00
+1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index
+2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by
+Warnings:
+Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`)
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 8 100.00
+1 PRIMARY t1_outer ref a a 5 <subquery2>.max(b) 2 100.00 Using index
+2 MATERIALIZED t1 range NULL a 5 NULL 8 100.00 Using index for group-by
+Warnings:
+Note 1003 select `test`.`t1_outer`.`a` AS `a` from <materialize> (select max(`test`.`t1`.`b`) from `test`.`t1` group by `test`.`t1`.`a`) join `test`.`t1` `t1_outer` where (`test`.`t1_outer`.`a` = `<subquery2>`.`max(b)`)
+drop table t1;
+#
# End of 10.0 tests
#
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index 8c9be0ca8db..72a556c9bc2 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1519,6 +1519,23 @@ ALTER TABLE t1 ADD KEY(id,a);
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
DROP TABLE t1;
+--echo #
+--echo # Query plan changes when we use GROUP BY optimization with optimizer_use_condition_selectivity=4
+--echo # and use_stat_tables= PREFERABLY
+--echo #
+
+CREATE TABLE t1 (a INT, b INT,c INT DEFAULT 0, INDEX (a,b));
+INSERT INTO t1 (a, b) VALUES (1,1), (1,2), (1,3), (1,4), (1,5),
+(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
+set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
+set @save_use_stat_tables= @@use_stat_tables;
+set @@optimizer_use_condition_selectivity=4;
+set @@use_stat_tables=PREFERABLY;
+explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a);
+set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
+set @@use_stat_tables=@save_use_stat_tables;
+explain extended SELECT a FROM t1 AS t1_outer WHERE a IN (SELECT max(b) FROM t1 GROUP BY a);
+drop table t1;
--echo #
--echo # End of 10.0 tests
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index f1d84e5c623..734adfbc9d1 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -3501,7 +3501,18 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond)
table->cond_selectivity= 1.0;
- if (!cond || table_records == 0)
+ if (table_records == 0)
+ DBUG_RETURN(FALSE);
+
+ QUICK_SELECT_I *quick;
+ if ((quick=table->reginfo.join_tab->quick) &&
+ quick->get_type() == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
+ {
+ table->cond_selectivity*= (quick->records/table_records);
+ DBUG_RETURN(FALSE);
+ }
+
+ if (!cond)
DBUG_RETURN(FALSE);
if (table->pos_in_table_list->schema_table)