bzr commit into mysql-5.1-bugteam branch (epotemkin:3354) Bug#50843

[Evgeny Potemkin]

#At file:///work/bzrroot/50843-bug-5.1-bugteam/ based on revid:magne.mahre@stripped

 3354 Evgeny Potemkin	2010-02-23
      Bug#50843: Filesort used instead of clustered index led to
      performance degradation.
      
      Filesort + join cache combination is preferred to full index scan because it
      is usually faster. But it's not the case when the index is clustered one.
      
      Now test_if_skip_sort_order function prefers filesort only if index isn't
      clustered.
     @ mysql-test/r/innodb_mysql.result
        Added a test case for the bug#50843.
     @ mysql-test/t/innodb_mysql.test
        Added a test case for the bug#50843.
     @ sql/sql_select.cc
        Bug#50843: Filesort used instead of clustered index led to
        performance degradation.
        Now test_if_skip_sort_order function prefers filesort only if index isn't
        clustered.

    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-01-29 15:04:37 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-02-23 19:03:41 +0000
@@ -2281,4 +2281,18 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGI
 SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
 1
 DROP TABLE t1;
+#
+# Bug#50843: Filesort used instead of clustered index led to
+#            performance degradation.
+#
+create table t1(f1 int not null primary key, f2 int) engine=innodb;
+create table t2(f1 int not null, key (f1)) engine=innodb;
+insert into t1 values (1,1),(2,2),(3,3);
+insert into t2 values (1),(2),(3);
+explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	
+1	SIMPLE	t2	ref	f1	f1	4	test.t1.f1	1	Using index
+drop table t1,t2;
+#
 End of 5.1 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-01-29 15:04:37 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-02-23 19:03:41 +0000
@@ -545,5 +545,17 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGI
 SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#50843: Filesort used instead of clustered index led to
+--echo #            performance degradation.
+--echo #
+create table t1(f1 int not null primary key, f2 int) engine=innodb;
+create table t2(f1 int not null, key (f1)) engine=innodb;
+insert into t1 values (1,1),(2,2),(3,3);
+insert into t2 values (1),(2),(3);
+explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
+drop table t1,t2;
+--echo #
+
 
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-02-16 09:13:49 +0000
+++ b/sql/sql_select.cc	2010-02-23 19:03:41 +0000
@@ -13304,12 +13304,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
     */
     if (select_limit >= table_records)
     {
-      /* 
-        filesort() and join cache are usually faster than reading in 
-        index order and not using join cache
-        */
-      if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1)
-        DBUG_RETURN(0);
       keys= *table->file->keys_to_use_for_scanning();
       keys.merge(table->covering_keys);
 
@@ -13459,6 +13453,19 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 	}      
       }
     }
+
+    /*
+      filesort() and join cache are usually faster than reading in 
+      index order and not using join cache, except in case that chosen
+      index is clustered primary key.
+    */
+    if ((select_limit >= table_records) &&
+        (tab->type == JT_ALL &&
+         tab->join->tables > tab->join->const_tables + 1) &&
+        !((unsigned) best_key == table->s->primary_key &&
+          table->file->primary_key_is_clustered()))
+      DBUG_RETURN(0);
+
     if (best_key >= 0)
     {
       bool quick_created= FALSE;


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