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

Sergei Petrunia psergey at askmonty.org
Tue Aug 20 15:57:05 EEST 2013


Hi Timour,

I'm sorry it took me so long to review this. 

The patch is ok, except one issue:

Please change the testcase to follow the t1,t2,t3,... table naming convention 
instead of using 't1_dist'. 

Ok to push (to 10.0) when this is addressed.

On Mon, Jun 03, 2013 at 08:35:20AM +0000, timour at askmonty.org wrote:
> At file:///home/tsk/mprog/src/10.0-md4120-no-unique-loosescan/
> 
> ------------------------------------------------------------
> revno: 3640
> revision-id: timour at askmonty.org-20130603083506-c7rl4luz6tp16hj8
> parent: knielsen at knielsen-hq.org-20130529122340-kadsw91xbmnmsg37
> fixes bug: https://mariadb.atlassian.net/browse/MDEV-4120
> committer: timour at askmonty.org
> branch nick: 10.0-md4120-no-unique-loosescan
> timestamp: Mon 2013-06-03 11:35:06 +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.

> === 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-06-03 08:35:06 +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-06-03 08:35:06 +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-02-28 21:47:29 +0000
> +++ b/mysql-test/r/group_min_max.result	2013-06-03 08:35:06 +0000
> @@ -2126,6 +2126,12 @@ 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 t1_dist as select distinct a1, a2, b, c from t1;
> +alter table t1_dist add unique index idx_dist (a1, a2, b, c);
> +explain select a1, a2, b, min(c) from t1_dist group by a1, a2, b;
> +id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
> +1       SIMPLE  t1_dist index   NULL    idx_dist        163     NULL    64      Using index
> +drop table t1_dist;
>  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 +2255,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 +2320,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 +2335,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 +2531,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 +2603,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 +3553,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,6 +3564,6 @@ 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.
> 
> === modified file 'mysql-test/t/group_min_max.test'
> --- a/mysql-test/t/group_min_max.test	2013-02-28 21:47:29 +0000
> +++ b/mysql-test/t/group_min_max.test	2013-06-03 08:35:06 +0000
> @@ -697,6 +697,16 @@ 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 t1_dist as select distinct a1, a2, b, c from t1;
> +alter table t1_dist add unique index idx_dist (a1, a2, b, c);
> +
> +explain select a1, a2, b, min(c) from t1_dist group by a1, a2, b;
> +
> +drop table t1_dist;
>  
>  #
>  # Bug #16710: select distinct doesn't return all it should
> @@ -816,7 +826,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 +889,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 +1013,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 +1400,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-05-05 18:39:31 +0000
> +++ b/sql/opt_range.cc	2013-06-03 08:35:06 +0000
> @@ -12365,8 +12365,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;
>  
>      /*
> 

> _______________________________________________
> commits mailing list
> commits at mariadb.org
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- 
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog


More information about the commits mailing list