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

 3372 Gleb Shchepa	2010-03-05
      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-02-26 11:17:00 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-03-05 19:45:55 +0000
@@ -2295,4 +2295,26 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	1	Using index
 drop table t1,t2;
 #
+#
+# 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-02-26 11:17:00 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-03-05 19:45:55 +0000
@@ -558,4 +558,22 @@ drop table t1,t2;
 --echo #
 
 
+--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-26 13:16:46 +0000
+++ b/sql/sql_select.cc	2010-03-05 19:45:55 +0000
@@ -12912,12 +12912,35 @@ static int test_if_order_by_key(ORDER *o
 
 uint find_shortest_key(TABLE *table, const key_map *usable_keys)
 {
-  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())
   {
+    uint min_length= (uint) ~0;
     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.
+       Unfortunately, key_info[nr].key_length doesn't show the length
+       of key/pointer pair but a sum of key field lengths only, thus
+       we can't estimate index IO volume comparing only this key_length
+       value of seconday keys and clustered PK.
+       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)
@@ -12928,7 +12951,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-20100305194555-ts37kuiie7vbh9k6.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (gshchepa:3372) Bug#39653Gleb Shchepa5 Mar