List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:August 26 2010 9:31am
Subject:bzr commit into mysql-5.1-bugteam branch (epotemkin:3491) Bug#55656
View as plain text  
#At file:///work/bzrroot/55656-bug-5.1-bugteam/ based on revid:bjorn.munch@stripped

 3491 Evgeny Potemkin	2010-08-26
      Bug #55656: mysqldump can be slower after bug 39653 fix.
      After fix for bug 39653 the shortest available secondary index was used for
      full table scan. Primary clustered key was used only if no secondary index
      can be used. However, when chosen secondary index includes all fields of the
      table being scanned it's better to use primary index since the amount of
      data to scan is the same but the primary index is clustered.
      Now the find_shortest_key function takes this into account.
     @ mysql-test/suite/innodb/r/innodb_mysql.result
        Added a test case for the bug#55656.
     @ mysql-test/suite/innodb/t/innodb_mysql.test
        Added a test case for the bug#55656.
     @ sql/sql_select.cc
        Bug #55656: mysqldump can be slower after bug #39653 fix.
        The find_shortest_key function now prefers clustered primary key
        if found secondary key includes all fields of the table.

    modified:
      mysql-test/suite/innodb/r/innodb_mysql.result
      mysql-test/suite/innodb/t/innodb_mysql.test
      sql/sql_select.cc
=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2010-08-20 09:09:17 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2010-08-26 09:31:04 +0000
@@ -2529,4 +2529,62 @@ SELECT * FROM t1 FOR UPDATE;
 SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a;
 ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
 DROP TABLE t1,t2;
+#
+# Bug#55656: mysqldump can be slower after bug #39653 fix
+#
+CREATE TABLE t1 (a INT , b INT, c INT, d INT,
+KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	4
+ref	NULL
+rows	3
+Extra	Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	8
+ref	NULL
+rows	3
+Extra	Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	b
+key_len	13
+ref	NULL
+rows	3
+Extra	Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c,d);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	PRIMARY
+key_len	8
+ref	NULL
+rows	3
+Extra	Using index
+DROP TABLE t1;
+#
 End of 5.1 tests

=== modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
--- a/mysql-test/suite/innodb/t/innodb_mysql.test	2010-08-20 09:09:17 +0000
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test	2010-08-26 09:31:04 +0000
@@ -781,5 +781,30 @@ disconnect con2;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug#55656: mysqldump can be slower after bug #39653 fix
+--echo #
+
+CREATE TABLE t1 (a INT , b INT, c INT, d INT,
+  KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
+--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b);
+--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c);
+--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c,d);
+--query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+
+DROP TABLE t1;
+
+--echo #
+
 
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-08-13 08:07:39 +0000
+++ b/sql/sql_select.cc	2010-08-26 09:31:04 +0000
@@ -13017,6 +13017,34 @@ static int test_if_order_by_key(ORDER *o
 }
 
 
+/**
+  Find shortest key suitable for full table scan.
+
+  @param table                 Table to scan
+  @param usable_keys           Allowed keys
+
+  @note
+     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.
+     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 secondary keys and clustered PK.
+     So, try secondary keys first, and choose PK only if there are no
+     usable secondary covering keys or found best secondary key include
+     all table fields (i.e. same as PK):
+
+  @return
+    MAX_KEY     no suitable key found
+    key index   otherwise
+*/
+
 uint find_shortest_key(TABLE *table, const key_map *usable_keys)
 {
   uint best= MAX_KEY;
@@ -13029,23 +13057,6 @@ uint find_shortest_key(TABLE *table, con
     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))
@@ -13058,7 +13069,20 @@ uint find_shortest_key(TABLE *table, con
       }
     }
   }
-  return best != MAX_KEY ? best : usable_clustered_pk;
+  if (usable_clustered_pk != MAX_KEY)
+  {
+    /*
+     If the primary key is clustered and found shorter key covers all table
+     fields then primary key scan normally would be faster because amount of
+     data to scan is the same but PK is clustered.
+     It's safe to compare key parts with table fields since duplicate key
+     parts aren't allowed.
+     */
+    if (best == MAX_KEY ||
+        table->key_info[best].key_parts >= table->s->fields)
+      best= usable_clustered_pk;
+  }
+  return best;
 }
 
 /**


Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20100826093104-j739e79ucq8qsqdz.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (epotemkin:3491) Bug#55656Evgeny Potemkin26 Aug