[Commits] Rev 3681: MDEV-4120: UNIQUE indexes should not be considered for loose index scan in file:///home/tsk/mprog/src/10.0-md4120-no-unique-loosescan/

timour at askmonty.org timour at askmonty.org
Wed Aug 21 10:51:17 EEST 2013


At file:///home/tsk/mprog/src/10.0-md4120-no-unique-loosescan/

------------------------------------------------------------
revno: 3681
revision-id: timour at askmonty.org-20130821075108-33rptvhha6vfjzd8
parent: sanja at montyprogram.com-20130820135351-6wjeq3u31waunp7c
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4120
committer: timour at askmonty.org
branch nick: 10.0-md4120-no-unique-loosescan
timestamp: Wed 2013-08-21 10:51:08 +0300
message:
  MDEV-4120: UNIQUE indexes should not be considered for loose index scan
    
  Currently the loose scan code in opt_range.cc considers all indexes as
  possible for the access method. Due to inexact statistics it may happen
  that a loose scan is selected over a unique index.
    
  This is clearly wrong since a "loose scan" over a unique index will read
  the same keys as a direct index scan, but the loose scan has more overhead.
    
  This task adds a rule to skip unique indexes for loose scan.
-------------- next part --------------
=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2013-01-28 12:36:05 +0000
+++ b/mysql-test/r/distinct.result	2013-08-21 07:51:08 +0000
@@ -756,7 +756,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1)
 (1, 2, 3);
 EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  t1      range   NULL    PRIMARY 16      NULL    7       Using index for group-by; Using temporary
+1       SIMPLE  t1      range   NULL    a       16      NULL    7       Using index for group-by
 SELECT DISTINCT a, b, d, c FROM t1;
 a       b       d       c
 1       1       0       1

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/group_by.result	2013-08-21 07:51:08 +0000
@@ -2080,7 +2080,7 @@ f1	f2
 explain
 select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  t1      range   NULL    idx     5       NULL    7       Using index for group-by; Using temporary; Using filesort
+1       SIMPLE  t1      index   NULL    idx     5       NULL    20      Using index; Using temporary; Using filesort
 select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
 f1      f2
 1       1

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2013-07-17 13:42:13 +0000
+++ b/mysql-test/r/group_min_max.result	2013-08-21 07:51:08 +0000
@@ -2126,6 +2126,31 @@ id	select_type	table	type	possible_keys
 1       SIMPLE  t1      index   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_2        147     NULL    128     75.00   Using where; Using index
 Warnings:
 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);
+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
+select a1, a2, b, min(c) from t4 group by a1, a2, b;
+a1      a2      b       min(c)
+a       a       a       a111
+a       a       b       e112
+a       b       a       i121
+a       b       b       m122
+b       a       a       a211
+b       a       b       e212
+b       b       a       i221
+b       b       b       m222
+c       a       a       a311
+c       a       b       e312
+c       b       a       i321
+c       b       b       m322
+d       a       a       a411
+d       a       b       e412
+d       b       a       i421
+d       b       b       m422
+drop table t4;
 explain select distinct(a1) from t1 where ord(a2) = 98;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  t1      index   NULL    idx_t1_2        147     NULL    128     Using where; Using index
@@ -2249,14 +2274,14 @@ COUNT(DISTINCT a)
 1
 DROP TABLE t1;
 DROP PROCEDURE a;
-CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
+CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a));
 INSERT INTO t1 (a) VALUES 
 (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
 ('NORTH CENTRAL'), ('NORTH EAST'), ('NORTH WEST'), ('SCOTLAND'),
 ('SOUTH EAST'), ('SOUTH WEST'), ('WESTERN');
 EXPLAIN SELECT DISTINCT a,a FROM t1 ORDER BY a;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  t1      range   NULL    PRIMARY 66      NULL    12      Using index for group-by
+1       SIMPLE  t1      range   NULL    a       66      NULL    6       Using index for group-by
 SELECT DISTINCT a,a FROM t1 ORDER BY a;
 a       a
         
@@ -2314,11 +2339,11 @@ t1;
 id2     id3     id5     id4     id3     id6     id5     id1
 1       1       1       1       1       1       1       1
 DROP TABLE t1,t2,t3,t4,t5,t6;
-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b));
 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
 explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  t1      range   PRIMARY,b       PRIMARY 8       NULL    1       Using where; Using index for group-by
+1       SIMPLE  t1      range   a,b     a       10      NULL    1       Using where; Using index for group-by
 SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
 MAX(b)  a
 1       1
@@ -2329,7 +2354,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      range   PRIMARY PRIMARY 12      NULL    1       Using where; Using index for group-by
+1       SIMPLE  t2      ref     PRIMARY PRIMARY 8       const,const     1       Using where; Using index
 SELECT MIN(c) FROM t2 WHERE b = 2 and a = 1 and c > 1 GROUP BY a;
 MIN(c)
 2
@@ -2525,7 +2550,7 @@ a	MIN(b)	MAX(b)	AVG(b)
 2       1       3       2.0000
 1       1       3       2.0000
 DROP TABLE t1;
-create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
+create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM;
 insert into  t1 (a,b) values 
 (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
 (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
@@ -2597,7 +2622,7 @@ a	b
 3       13
 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
-1       SIMPLE  t1      ref     PRIMARY,index   PRIMARY 4       const   15      100.00  Using index; Using temporary
+1       SIMPLE  t1      ref     a,index a       5       const   15      100.00  Using index; Using temporary
 Warnings:
 Note    1003    select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
 drop table t1;
@@ -3547,7 +3572,7 @@ DROP TABLE t1,t2;
 CREATE TABLE t1 (
 f1 int(11) NOT NULL DEFAULT '0',
 f2 char(1) NOT NULL DEFAULT '',
-PRIMARY KEY (f1,f2)
+KEY (f1,f2)
 ) ;
 insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 
 (3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');
@@ -3558,7 +3583,7 @@ f1	COUNT(DISTINCT f2)
 3       4
 explain SELECT f1, COUNT(DISTINCT f2) FROM t1 GROUP BY f1;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  t1      range   NULL    PRIMARY 5       NULL    9       Using index for group-by (scanning)
+1       SIMPLE  t1      range   NULL    f1      5       NULL    9       Using index for group-by (scanning)
 drop table t1;
 # End of test#50539.
 #
@@ -3582,7 +3607,7 @@ INSERT INTO faulty (b, c) VALUES
 EXPLAIN
 SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       SIMPLE  faulty  range   b_and_c b_and_c 12      NULL    2       Using where; Using index for group-by; Using filesort
+1       SIMPLE  faulty  ref     b_and_c b_and_c 4       const   2       Using where; Using index
 SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
 b       c
 1802    2013-02-28 09:00:00

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2013-07-17 13:42:13 +0000
+++ b/mysql-test/t/group_min_max.test	2013-08-21 07:51:08 +0000
@@ -697,6 +697,18 @@ explain select a1,a2,count(a2) from t1 g
 explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b;
 explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b;
 
+#
+# MDEV-4120 UNIQUE indexes should not be considered for loose index scan
+#
+
+create table t4 as select distinct a1, a2, b, c from t1;
+alter table t4 add unique index idxt4 (a1, a2, b, c);
+
+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;
+
+drop table t4;
 
 #
 # Bug #16710: select distinct doesn't return all it should
@@ -816,7 +828,7 @@ DROP PROCEDURE a;
 # Bug #18068: SELECT DISTINCT
 #
 
-CREATE TABLE t1 (a varchar(64) NOT NULL default '', PRIMARY KEY(a));
+CREATE TABLE t1 (a varchar(64) NOT NULL default '', KEY(a));
 
 INSERT INTO t1 (a) VALUES 
   (''), ('CENTRAL'), ('EASTERN'), ('GREATER LONDON'),
@@ -879,7 +891,7 @@ DROP TABLE t1,t2,t3,t4,t5,t6;
 #
 # Bug#22342: No results returned for query using max and group by
 #
-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a,b), KEY b (b));
+CREATE TABLE t1 (a int, b int, KEY (a,b), KEY b (b));
 INSERT INTO t1 VALUES (1,1),(1,2),(1,0),(1,3);
 
 explain SELECT MAX(b), a FROM t1 WHERE b < 2 AND a = 1 GROUP BY a;
@@ -1003,7 +1015,7 @@ DROP TABLE t1;
 # Bug#38195: Incorrect handling of aggregate functions when loose index scan is
 #            used causes server crash.
 #
-create table t1 (a int, b int, primary key (a,b), key `index` (a,b)) engine=MyISAM;
+create table t1 (a int, b int, key (a,b), key `index` (a,b)) engine=MyISAM;
 insert into  t1 (a,b) values 
 (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6),
   (0,7),(0,8),(0,9),(0,10),(0,11),(0,12),(0,13),
@@ -1390,7 +1402,7 @@ DROP TABLE t1,t2;
 CREATE TABLE t1 (
   f1 int(11) NOT NULL DEFAULT '0',
   f2 char(1) NOT NULL DEFAULT '',
-  PRIMARY KEY (f1,f2)
+  KEY (f1,f2)
 ) ;
 insert into t1 values(1,'A'),(1 , 'B'), (1, 'C'), (2, 'A'), 
 (3, 'A'), (3, 'B'), (3, 'C'), (3, 'D');

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2013-07-17 19:24:29 +0000
+++ b/sql/opt_range.cc	2013-08-21 07:51:08 +0000
@@ -12411,8 +12411,12 @@ get_best_group_min_max(PARAM *param, SEL
     uchar cur_key_infix[MAX_KEY_LENGTH];
     uint cur_used_key_parts;
     
-    /* Check (B1) - if current index is covering. */
-    if (!table->covering_keys.is_set(cur_index))
+    /*
+      Check (B1) - if current index is covering. Exclude UNIQUE indexes, because
+      loose scan may still be chosen for them due to imperfect cost calculations.
+    */
+    if (!table->covering_keys.is_set(cur_index) ||
+        cur_index_info->flags & HA_NOSAME)
       goto next_index;
 
     /*



More information about the commits mailing list