List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:August 25 2010 3:46pm
Subject:bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181) Bug#39653
Bug#55656
View as plain text  
#At file:///work/bzrroot/55204-bug-5.5-bugfixing/ based on revid:alik@stripped

 3181 Evgeny Potemkin	2010-08-25
      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
        Aadded a test case for the bug#55656.
     @ mysql-test/suite/innodb/t/innodb_mysql.test
        Aadded 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-07-07 12:18:20 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2010-08-25 15:46:17 +0000
@@ -2592,3 +2592,61 @@ COMMIT;
 COMMIT;
 DROP TABLE t1;
 DROP FUNCTION f1;
+#
+# 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;
+#

=== modified file 'mysql-test/suite/innodb/t/innodb_mysql.test'
--- a/mysql-test/suite/innodb/t/innodb_mysql.test	2010-08-05 12:41:07 +0000
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test	2010-08-25 15:46:17 +0000
@@ -805,3 +805,29 @@ connection default;
 COMMIT;
 DROP TABLE t1;
 DROP FUNCTION f1;
+
+--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 #
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-08-05 12:53:09 +0000
+++ b/sql/sql_select.cc	2010-08-25 15:46:17 +0000
@@ -13256,14 +13256,15 @@ uint find_shortest_key(TABLE *table, con
           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.
+       then secondary index entry data is always a subset of or same as
+       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 seconday keys and clustered PK.
        So, try secondary keys first, and choose PK only if there are no
-       usable secondary covering keys:
+       usable secondary covering keys or found best secondary key include
+       all table fields (i.e. same as PK):
       */
       if (nr == usable_clustered_pk)
         continue;
@@ -13277,7 +13278,19 @@ 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.
+     This check assumes that key parts aren't duplicated.
+     */
+    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-20100825154617-j1w7do6pl4jcx1dg.bundle
Thread
bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181) Bug#39653Bug#55656Evgeny Potemkin25 Aug