List:Commits« Previous MessageNext Message »
From:kgeorge Date:July 19 2007 9:30am
Subject:bk commit into 5.0 tree (gkodinov:1.2530) BUG#28591
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-07-19 12:30:29+03:00, gkodinov@stripped +4 -0
  Bug #28591: MySQL need not sort the records in case of 
  ORDER BY primary_key on InnoDB table
  
  Queries that use an InnoDB secondary index to retrieve
  data don't need to sort in case of ORDER BY primary key
  if the secondary index is compared to constant(s).
  This is because InnoDB returns the rows in order of the
  primary key.
  Fixed by preventing temp table sort for queries that use
  secondary index to access the qualifying table data and 
  are ordered on the primary key.

  mysql-test/r/innodb_mysql.result@stripped, 2007-07-19 12:30:28+03:00, gkodinov@stripped +183 -0
    Bug #28591: test case

  mysql-test/t/innodb_mysql.test@stripped, 2007-07-19 12:30:28+03:00, gkodinov@stripped +30 -0
    Bug #28591: test case

  sql/sql_select.cc@stripped, 2007-07-19 12:30:28+03:00, gkodinov@stripped +31 -4
    Bug #28591: try primary key if it's contained in the 
    key used.

  sql/table.cc@stripped, 2007-07-19 12:30:28+03:00, gkodinov@stripped +4 -0
    Bug #28591: can use the primary key
    to sort as well.

diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-07-07 22:17:02 +03:00
+++ b/mysql-test/r/innodb_mysql.result	2007-07-19 12:30:28 +03:00
@@ -735,4 +735,187 @@ COUNT(*)
 3072
 set @@sort_buffer_size=default;
 DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1),(3,1),(2,1),(4,1),(5,1),(6,1),(7,1),(8,1);
+INSERT INTO t1 SELECT a + 8, 1 FROM t1;
+EXPLAIN SELECT 1 FROM t1 WHERE b=10 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t1
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	const
+rows	1
+Extra	Using where; Using index
+SELECT * FROM t1 WHERE b=1 ORDER BY a;
+a	b
+1	1
+2	1
+3	1
+4	1
+5	1
+6	1
+7	1
+8	1
+9	1
+10	1
+11	1
+12	1
+13	1
+14	1
+15	1
+16	1
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+EXPLAIN SELECT 1 FROM t2 WHERE b=1 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	const
+rows	8
+Extra	Using where; Using index; Using filesort
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT 1 FROM t2 WHERE b=1 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	5
+ref	const
+rows	8
+Extra	Using where; Using index; Using filesort
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT 1 FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+id	1
+select_type	SIMPLE
+table	t2
+type	ref
+possible_keys	bkey
+key	bkey
+key_len	10
+ref	const,const
+rows	8
+Extra	Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+a	b	c
+1	1	1
+2	1	1
+3	1	1
+4	1	1
+5	1	1
+6	1	1
+7	1	1
+8	1	1
+9	1	1
+10	1	1
+11	1	1
+12	1	1
+13	1	1
+14	1	1
+15	1	1
+16	1	1
+DROP TABLE t1,t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
--- a/mysql-test/t/innodb_mysql.test	2007-07-07 22:19:29 +03:00
+++ b/mysql-test/t/innodb_mysql.test	2007-07-19 12:30:28 +03:00
@@ -741,4 +741,34 @@ set @@sort_buffer_size=default;
 
 DROP TABLE t1,t2;
 
+#
+# Bug #28591: MySQL need not sort the records in case of ORDER BY
+# primary_key on InnoDB table
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1),(3,1),(2,1),(4,1),(5,1),(6,1),(7,1),(8,1);
+INSERT INTO t1 SELECT a + 8, 1 FROM t1;
+query_vertical EXPLAIN SELECT 1 FROM t1 WHERE b=10 ORDER BY a;
+SELECT * FROM t1 WHERE b=1 ORDER BY a;
+
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+  ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+
+query_vertical EXPLAIN SELECT 1 FROM t2 WHERE b=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+query_vertical EXPLAIN SELECT 1 FROM t2 WHERE b=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+query_vertical EXPLAIN SELECT 1 FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-07-12 04:45:22 +03:00
+++ b/sql/sql_select.cc	2007-07-19 12:30:28 +03:00
@@ -12341,6 +12341,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
       We come here when there is a REF key.
     */
     int order_direction;
+    int order_key;
     uint used_key_parts;
     if (!usable_keys.is_set(ref_key))
     {
@@ -12400,10 +12401,36 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
         ref_key= new_ref_key;
       }
     }
+    order_key= ref_key;
     /* Check if we get the rows in requested sorted order by using the key */
-    if (usable_keys.is_set(ref_key) &&
-	(order_direction = test_if_order_by_key(order,table,ref_key,
-						&used_key_parts)))
+    if ((usable_keys.is_set(order_key) &&
+	(order_direction= test_if_order_by_key(order,table,order_key,
+						&used_key_parts))) ||
+        (
+         /*
+           For storage engine that support it (InnoDB) check if we can skip
+           sort queries like :
+            SELECT ... FROM ... WHERE secondary_key = <const>
+              ORDER BY <primary_key>
+           We can skip sorting if :
+           (1) The storage engine supports it
+           (2) there is a primary key
+           (3) all the subparts of the secondary key are equal to const
+           (4) primary key is usable
+           (5) we have an ORDER BY on the primary key
+           (6) In case we have yes on (1)-(5), use the primary key instead of
+           sorting. The condition is always true : it's the assignment
+           that is important.
+         */
+         (table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) && // (1)
+         table->s->primary_key != MAX_KEY &&                            // (2)
+         ref_key_parts == table->key_info[order_key].key_parts &&       // (3)
+         usable_keys.is_set(table->s->primary_key) &&                   // (4)
+         (order_direction= test_if_order_by_key(order,table,
+                                                table->s->primary_key,
+                                                &used_key_parts)) &&    // (5)
+         (order_key= table->s->primary_key) >= 0)                       // (6)
+       )
     {
       if (order_direction == -1)		// If ORDER BY ... DESC
       {
@@ -12434,7 +12461,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 	  }
 	  DBUG_RETURN(1);
 	}
-	if (tab->ref.key_parts < used_key_parts)
+	if (order_key == ref_key && tab->ref.key_parts < used_key_parts)
 	{
 	  /*
 	    SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
diff -Nrup a/sql/table.cc b/sql/table.cc
--- a/sql/table.cc	2007-06-27 14:35:48 +03:00
+++ b/sql/table.cc	2007-07-19 12:30:28 +03:00
@@ -780,7 +780,11 @@ int openfrm(THD *thd, const char *name, 
 	      the primary key, then we can use any key to find this column
 	    */
 	    if (ha_option & HA_PRIMARY_KEY_IN_READ_INDEX)
+            {
 	      field->part_of_key= share->keys_in_use;
+              if (field->part_of_sortkey.is_set(key))
+                field->part_of_sortkey= share->keys_in_use;
+            }
 	  }
 	  if (field->key_length() != key_part->length)
 	  {
Thread
bk commit into 5.0 tree (gkodinov:1.2530) BUG#28591kgeorge19 Jul