List:Commits« Previous MessageNext Message »
From:Gleb Shchepa Date:February 26 2010 7:18pm
Subject:bzr commit into mysql-5.1-bugteam branch (gshchepa:3358) Bug#39653
View as plain text  
#At file:///mnt/sda7/work/39653-5.1/ based on revid:gshchepa@stripped

 3358 Gleb Shchepa	2010-02-26
      Bug #39653: find_shortest_key in sql_select.cc does not
                  consider clustered primary keys
      
      Choosing a shortest index for the covering index scan,
      the optimizer ignored the fact, that the clustered primary
      key read involves whole table data.
      
      The find_shortest_key function has been modified to
      take into account that fact that a clustered PK has a
      longest key of possible covering indices.
     @ mysql-test/r/innodb_mysql.result
        Test case for bug #39653.
     @ mysql-test/t/innodb_mysql.test
        Test case for bug #39653.
     @ sql/sql_select.cc
        Bug #39653: find_shortest_key in sql_select.cc does not
                    consider clustered primary keys
        
        The find_shortest_key function has been modified to
        take into account that fact that a clustered PK has a
        longest key of possible covering indices.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-01-29 15:04:37 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-02-26 19:17:57 +0000
@@ -2281,4 +2281,26 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGI
 SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
 1
 DROP TABLE t1;
+#
+# Bug #39653: find_shortest_key in sql_select.cc does not consider
+#             clustered primary keys
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
+KEY (b,c)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
+(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
+(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
+(11,11,11,11,11,11);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	10
+ref	NULL
+rows	10
+Extra	Using index
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-01-29 15:04:37 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-02-26 19:17:57 +0000
@@ -546,4 +546,22 @@ SELECT 1 FROM t1 JOIN t1 a USING(a) GROU
 DROP TABLE t1;
 
 
+--echo #
+--echo # Bug #39653: find_shortest_key in sql_select.cc does not consider
+--echo #             clustered primary keys
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
+                 KEY (b,c)) ENGINE=INNODB;
+
+INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
+                      (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
+		      (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
+		      (11,11,11,11,11,11);
+
+--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+
+DROP TABLE t1;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-02-25 15:48:53 +0000
+++ b/sql/sql_select.cc	2010-02-26 19:17:57 +0000
@@ -12913,10 +12913,29 @@ uint find_shortest_key(TABLE *table, con
 {
   uint min_length= (uint) ~0;
   uint best= MAX_KEY;
+  uint usable_clustered_pk= (table->file->primary_key_is_clustered() &&
+                             table->s->primary_key != MAX_KEY &&
+                             usable_keys->is_set(table->s->primary_key)) ?
+                            table->s->primary_key : MAX_KEY;
   if (!usable_keys->is_clear_all())
   {
     for (uint nr=0; nr < table->s->keys ; nr++)
     {
+      /*
+       As far as 
+       1) clustered primary key entry data set is a set of all record
+          fields (key fields and not key fields) and
+       2) secondary index entry data is a union of its key fields and
+          primary key fields (at least InnoDB and its derivatives don't
+          duplicate primary key fields there, even if the primary and
+          the secondary keys have a common subset of key fields),
+       then secondary index entry data is always a subset of primary key
+       entry, and the PK is always longer.
+       So, try secondary keys first, and choose PK only if there are no
+       usable secondary covering keys:
+      */
+      if (nr == usable_clustered_pk)
+        continue;
       if (usable_keys->is_set(nr))
       {
         if (table->key_info[nr].key_length < min_length)
@@ -12927,7 +12946,7 @@ uint find_shortest_key(TABLE *table, con
       }
     }
   }
-  return best;
+  return best != MAX_KEY ? best : usable_clustered_pk;
 }
 
 /**


Attachment: [text/bzr-bundle] bzr/gshchepa@mysql.com-20100226191757-seae373759srg17a.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (gshchepa:3358) Bug#39653Gleb Shchepa26 Feb