bzr push into mysql-pe branch (gshchepa:3950 to 3951)

[Gleb Shchepa]

 3951 Gleb Shchepa	2010-03-06 [merge]
      auto merge mysql-5.1-bugteam --> mysql-pe (bug 39653)

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/sql_select.cc
 3950 Tatiana A. Nurnberg	2010-03-05 [merge]
      auto-merge, NULL

=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-02-26 15:12:49 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-03-05 20:10:12 +0000
@@ -2326,6 +2326,28 @@ 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
 #
 # Test for bug #39932 "create table fails if column for FK is in different

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-02-26 15:12:49 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-03-05 20:10:12 +0000
@@ -558,6 +558,24 @@ 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 16:19:26 +0000
+++ b/sql/sql_select.cc	2010-03-05 20:10:12 +0000
@@ -18584,12 +18584,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)
@@ -18600,7 +18623,7 @@ uint find_shortest_key(TABLE *table, con
       }
     }
   }
-  return best;
+  return best != MAX_KEY ? best : usable_clustered_pk;
 }
 
 /**


Attachment: [text/bzr-bundle] bzr/[e-mail]