[Commits] Rev 4494: MDEV-7118: Anemometer stop working after upgrade to from... in file:///home/psergey/dev2/10.0-cp/

Sergey Petrunya psergey at askmonty.org
Wed Nov 19 16:14:51 EET 2014


At file:///home/psergey/dev2/10.0-cp/

------------------------------------------------------------
revno: 4494
revision-id: psergey at askmonty.org-20141119141449-qls0u7g3bfxbzlrs
parent: jplindst at mariadb.org-20141114071313-fcdbmb02ei72d16y
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0-cp
timestamp: Wed 2014-11-19 17:14:49 +0300
message:
  MDEV-7118: Anemometer stop working after upgrade to from...
  
  When the optimizer considers an option to use Loose Scan, it should 
  still consider UNIQUE keys (Previously, MDEV-4120 disabled loose scan
  for all kinds of unique indexes. That was wrong)
  
  However, we should not use Loose Scan when trying to satisfy 
   "SELECT DISTINCT col1, col2, .. colN"
  when using an index defined as UNIQU(col1, col2, ... colN).
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2014-08-07 16:06:56 +0000
+++ b/mysql-test/r/group_min_max.result	2014-11-19 14:14:49 +0000
@@ -2129,10 +2129,11 @@ id	select_type	table	type	possible_keys
 Note	1003	select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b`
 create table t4 as select distinct a1, a2, b, c from t1;
 alter table t4 add unique index idxt4 (a1, a2, b, c);
+# This is "superceded" by MDEV-7118, and Loose Index Scan is again an option:
 explain
 select a1, a2, b, min(c) from t4 group by a1, a2, b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	idxt4	163	NULL	64	Using index
+1	SIMPLE	t4	range	NULL	idxt4	147	NULL	10	Using index for group-by
 select a1, a2, b, min(c) from t4 group by a1, a2, b;
 a1	a2	b	min(c)
 a	a	a	a111
@@ -2355,7 +2356,7 @@ CREATE TABLE t2 (a int, b int, c int, PR
 INSERT INTO t2 SELECT a,b,b FROM t1;
 explain SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	const,const	1	Using where; Using index
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	12	NULL	1	Using where; Using index for group-by
 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 MIN(c)
 2

=== modified file 'mysql-test/r/group_min_max_innodb.result'
--- a/mysql-test/r/group_min_max_innodb.result	2014-08-07 16:06:56 +0000
+++ b/mysql-test/r/group_min_max_innodb.result	2014-11-19 14:14:49 +0000
@@ -174,7 +174,7 @@ F	17
 EXPLAIN SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 )
 GROUP BY c1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	k1	k1	5	NULL	2	Using where; Using index
+1	SIMPLE	t1	range	k1	k1	5	NULL	1	Using where; Using index for group-by
 SELECT c1, max(i2) FROM t1 WHERE (c1 = 'C' OR c1 = 'F' ) AND ( i2 = 17 )
 GROUP BY c1;
 c1	max(i2)

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2014-08-07 16:06:56 +0000
+++ b/mysql-test/t/group_min_max.test	2014-11-19 14:14:49 +0000
@@ -704,6 +704,7 @@ explain extended select sum(ord(a1)) fro
 create table t4 as select distinct a1, a2, b, c from t1;
 alter table t4 add unique index idxt4 (a1, a2, b, c);
 
+--echo # This is "superceded" by MDEV-7118, and Loose Index Scan is again an option:
 explain
 select a1, a2, b, min(c) from t4 group by a1, a2, b;
 select a1, a2, b, min(c) from t4 group by a1, a2, b;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2014-10-28 21:33:31 +0000
+++ b/sql/opt_range.cc	2014-11-19 14:14:49 +0000
@@ -12799,11 +12799,11 @@ get_best_group_min_max(PARAM *param, SEL
     uint cur_used_key_parts;
     
     /*
-      Check (B1) - if current index is covering. Exclude UNIQUE indexes, because
-      loose scan may still be chosen for them due to imperfect cost calculations.
+      Check (B1) - if current index is covering.
+      (was also: "Exclude UNIQUE indexes ..." but this was removed because 
+      there are cases Loose Scan over a multi-part index is useful).
     */
-    if (!table->covering_keys.is_set(cur_index) ||
-        cur_index_info->flags & HA_NOSAME)
+    if (!table->covering_keys.is_set(cur_index))
       goto next_index;
 
     /*
@@ -12942,6 +12942,16 @@ get_best_group_min_max(PARAM *param, SEL
     }
 
     /*
+      Aplly a heuristic: there is no point to use loose index scan when we're
+      using the whole unique index.
+    */
+    if (cur_index_info->flags & HA_NOSAME && 
+        cur_group_key_parts == cur_index_info->user_defined_key_parts)
+    {
+      goto next_index;
+    }
+
+    /*
       Check (NGA1, NGA2) and extract a sequence of constants to be used as part
       of all search keys.
     */



More information about the commits mailing list