List:Commits« Previous MessageNext Message »
From:Evgeny Potemkin Date:August 17 2010 10:52am
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-17
      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-17 10:52:26 +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-17 10:52:26 +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-17 10:52:26 +0000
@@ -13277,7 +13277,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 will 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 == MAX_KEY)
+      best= usable_clustered_pk;
+  }
+  return best;
 }
 
 /**


Attachment: [text/bzr-bundle] bzr/epotemkin@mysql.com-20100817105226-6pab2mau8aoxsohd.bundle
Thread
bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181) Bug#39653Bug#55656Evgeny Potemkin17 Aug
  • Re: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181) Bug#39653Bug#55656Øystein Grøvlen25 Aug
    • Re: bzr commit into mysql-5.5-bugfixing branch (epotemkin:3181)Bug#39653 Bug#55656Evgeny Potemkin25 Aug