From: Sergey Glukhov
Date: October 27 2011 8:41am
Subject: bzr push into mysql-trunk branch (sergey.glukhov:3533 to 3534) Bug#11747423
List-Archive: http://lists.mysql.com/commits/141609
X-Bug: 11747423
Message-Id: <201110270843.p9R8hZO8001350@acsmt356.oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3534 Sergey Glukhov	2011-10-27
      Bug#11747423 32254: INDEX MERGE USED UNNECESSARILY
      There was an assumption that quick select is always faster
      than ref access for InnoDB tables. But some test cases show
      that it's not true for index merge access. In some cases
      ref access by compound single key is better than
      index merge access by several keys. The fix is to
      add a check that INDEX MERGE access is not more expensive
      than ref access.
     @ mysql-test/r/index_merge_innodb.result
        test case
     @ mysql-test/t/index_merge_innodb.test
        test case
     @ sql/sql_select.cc
        added a check that INDEX MERGE access is not more expensive
        than ref access.

    modified:
      mysql-test/r/index_merge_innodb.result
      mysql-test/t/index_merge_innodb.test
      sql/sql_select.cc
 3533 Jimmy Yang	2011-10-27
      Fix Bug #11755924 - 47764: PARTITIONED INNODB TABLES CAN'T BE MOVED DIRECTLY
      FROM WINDOWS TO LINUX
                              
      rb://786 approved by Sunny Bains

    modified:
      storage/innobase/handler/ha_innodb.cc
=== modified file 'mysql-test/r/index_merge_innodb.result'
--- a/mysql-test/r/index_merge_innodb.result	2010-12-02 13:11:15 +0000
+++ b/mysql-test/r/index_merge_innodb.result	2011-10-27 08:40:40 +0000
@@ -273,3 +273,21 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
 2	DEPENDENT SUBQUERY	t2	index_merge	f2,f3	f3,f2	2,5	NULL	1	Using intersect(f3,f2); Using where; Using index
 DROP TABLE t1,t2;
+#
+# Bug#11747423 32254: INDEX MERGE USED UNNECESSARILY
+#
+CREATE TABLE t1 (
+id INT NOT NULL PRIMARY KEY,
+id2 INT NOT NULL,
+id3 INT NOT NULL,
+KEY (id2),
+KEY (id3),
+KEY covering_index (id2,id3)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7);
+INSERT INTO t1 SELECT id + 8, id2 + 8, id3 +8 FROM t1;
+INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1;
+EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	id2,id3,covering_index	covering_index	8	const,const	16	Using index
+DROP TABLE t1;

=== modified file 'mysql-test/t/index_merge_innodb.test'
--- a/mysql-test/t/index_merge_innodb.test	2009-10-07 15:37:36 +0000
+++ b/mysql-test/t/index_merge_innodb.test	2011-10-27 08:40:40 +0000
@@ -28,3 +28,24 @@ let $merge_table_support= 0;
 
 --source include/index_merge_2sweeps.inc
 --source include/index_merge_ror_cpk.inc
+
+
+--echo #
+--echo # Bug#11747423 32254: INDEX MERGE USED UNNECESSARILY
+--echo #
+CREATE TABLE t1 (
+  id INT NOT NULL PRIMARY KEY,
+  id2 INT NOT NULL,
+  id3 INT NOT NULL,
+  KEY (id2),
+  KEY (id3),
+  KEY covering_index (id2,id3)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (0, 0, 0), (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4), (5, 5, 5), (6, 6, 6), (7, 7, 7);
+INSERT INTO t1 SELECT id + 8, id2 + 8, id3 +8 FROM t1;
+INSERT INTO t1 SELECT id + 16, 7, 0 FROM t1;
+
+EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id2=7 AND id3=0;
+
+DROP TABLE t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-10-27 08:08:46 +0000
+++ b/sql/sql_select.cc	2011-10-27 08:40:40 +0000
@@ -8018,7 +8018,10 @@ best_access_path(JOIN      *join,
     Don't do a table scan on InnoDB tables, if we can read the used
     parts of the row from any of the used index.
     This is because table scans uses index and we would not win
-    anything by using a table scan.
+    anything by using a table scan. The only exception is INDEX_MERGE
+    quick select. We can not say for sure that INDEX_MERGE quick select
+    is always faster than ref access. So it's necessary to check if
+    ref access is more expensive.
 
     A word for word translation of the below if-statement in sergefp's
     understanding: we check if we should use table scan if:
@@ -8058,8 +8061,11 @@ best_access_path(JOIN      *join,
     goto skip_table_scan;
   }
 
-  if (((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&    //(3)
-       !s->table->covering_keys.is_clear_all() && best_key && !s->quick))//(3)
+  if ((s->table->file->ha_table_flags() & HA_TABLE_SCAN_ON_INDEX) &&    //(3)
+      !s->table->covering_keys.is_clear_all() && best_key &&            //(3)
+      (!s->quick ||                                                     //(3)
+       !(s->quick->get_type() == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE && //(3)
+         best > s->quick->read_time)))                                  //(3)
   {
     trace_access_scan.add_alnum("access_type", s->quick ? "range" : "scan").
       add_alnum("cause", "covering_index_better_than_full_scan");

No bundle (reason: useless for push emails).