diff options
author | Sergey Petrunya <psergey@askmonty.org> | 2012-02-16 20:15:57 +0400 |
---|---|---|
committer | Sergey Petrunya <psergey@askmonty.org> | 2012-02-16 20:15:57 +0400 |
commit | 541334c5ac11efae4918116b152d9085bd82caee (patch) | |
tree | e8f949734d99b996d12e1b3c27b7e25574d3e50b | |
parent | e98e05da7a0f817849529dfaf89e9b8010b16f4e (diff) | |
download | mariadb-git-541334c5ac11efae4918116b152d9085bd82caee.tar.gz |
Backport of:
timestamp: Thu 2011-12-01 15:12:10 +0100
Fix for Bug#13430436 PERFORMANCE DEGRADATION IN SYSBENCH ON INNODB DUE TO ICP
When running sysbench on InnoDB there is a performance degradation due
to index condition pushdown (ICP). Several of the queries in sysbench
have a WHERE condition that the optimizer uses for executing these
queries as range scans. The upper and lower limit of the range scan
will ensure that the WHERE condition is fulfilled. Still, the WHERE
condition is part of the queries' condition and if ICP is enabled the
condition will be pushed down to InnoDB as an index condition.
Due to the range scan's upper and lower limits ensure that the WHERE
condition is fulfilled, the pushed index condition will not filter out
any records. As a result the use of ICP for these queries results in a
performance overhead for sysbench. This overhead comes from using
resources for determining the part of the condition that can be pushed
down to InnoDB and overhead in InnoDB for executing the pushed index
condition.
With the default configuration for sysbench the range scans will use
the primary key. This is a clustered index in InnoDB. Using ICP on a
clustered index provides the lowest performance benefit since the
entire record is part of the clustered index and in InnoDB it has the
highest relative overhead for executing the pushed index condition.
The fix for removing the overhead ICP introduces when running sysbench
is to disable use of ICP when the index used by the query is a
clustered index.
When WL#6061 is implemented this change should be re-evaluated.
-rw-r--r-- | mysql-test/r/index_merge_innodb.result | 2 | ||||
-rw-r--r-- | mysql-test/r/innodb.result | 2 | ||||
-rw-r--r-- | mysql-test/r/innodb_icp.result | 14 | ||||
-rw-r--r-- | mysql-test/r/innodb_mrr_cpk.result | 6 | ||||
-rw-r--r-- | mysql-test/r/range_vs_index_merge_innodb.result | 28 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_mysql.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_select_innodb.result | 6 | ||||
-rw-r--r-- | sql/opt_index_cond_pushdown.cc | 14 |
10 files changed, 45 insertions, 35 deletions
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index 0f9da2ea3b6..50b0147b6ad 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -549,7 +549,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using index condition; Using where +1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 3b69791d373..9ab7ed4eec7 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -783,7 +783,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); diff --git a/mysql-test/r/innodb_icp.result b/mysql-test/r/innodb_icp.result index ccbae98d137..d8e41113f63 100644 --- a/mysql-test/r/innodb_icp.result +++ b/mysql-test/r/innodb_icp.result @@ -167,7 +167,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where DROP TABLE t1; # @@ -431,7 +431,7 @@ SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where -2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using index condition +2 DEPENDENT SUBQUERY it eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY t2 index NULL PRIMARY 4 NULL 3 Using where; Using index; Using join buffer (flat, BNL join) SELECT * FROM t1 WHERE pk IN (SELECT it.pk FROM t2 JOIN t2 AS it ON it.i=it.i WHERE it.pk-t1.i<10); @@ -452,7 +452,7 @@ PRIMARY KEY (pk) INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; SELECT pk, c1 FROM t1 WHERE pk <> 3; pk c1 @@ -507,8 +507,8 @@ SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where -1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where +1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 2 Using where; Using join buffer (flat, BNL join) SELECT c2 FROM t1 JOIN t2 ON t1.c1 = t2.c1 WHERE (t2.pk <= 4 AND t1.pk IN (2,1)) OR (t1.pk > 1 AND t2.pk BETWEEN 6 AND 6); @@ -680,7 +680,7 @@ EXPLAIN SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using where; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 HAVING t1.c != 5 ORDER BY t1.c; @@ -793,7 +793,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL PRIMARY,c NULL NULL NULL 64 Using where 1 PRIMARY t2 ref g g 5 test.t.c 9 Using where 2 DEPENDENT SUBQUERY t1 index PRIMARY d 3 NULL 64 Using where; Using index -2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where SELECT COUNT(*) FROM t1 AS t, t2 WHERE c = g AND (EXISTS (SELECT * FROM t1, t2 WHERE a = f AND h <= t.e AND a > t.b) diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 81536f2a43b..90f59b96e61 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -82,7 +82,7 @@ insert into t2 values ('a-1010=A', 1010), ('a-1030=A', 1030), ('a-1020=A', 1020) explain select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 30 test.t2.a,test.t2.b 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t1.b=t2.b; a b filler a b a-1010=A 1010 filler a-1010=A 1010 @@ -91,7 +91,7 @@ a-1030=A 1030 filler a-1030=A 1030 explain select * from t1, t2 where t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 ref PRIMARY PRIMARY 26 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a; a b filler a b a-1010=A 1010 filler a-1010=A 1010 @@ -133,7 +133,7 @@ set join_cache_level=6; explain select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where -1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using index condition(BKA); Using join buffer (flat, BKA join); Key-ordered scan +1 SIMPLE t1 ref PRIMARY PRIMARY 4 test.t2.a 1 Using where; Using join buffer (flat, BKA join); Key-ordered scan select * from t1, t2 where t1.a=t2.a and t2.b + t1.b > 100; a b c filler a b set optimizer_switch='index_condition_pushdown=off'; diff --git a/mysql-test/r/range_vs_index_merge_innodb.result b/mysql-test/r/range_vs_index_merge_innodb.result index 57862ded4f2..c42f80f0e85 100644 --- a/mysql-test/r/range_vs_index_merge_innodb.result +++ b/mysql-test/r/range_vs_index_merge_innodb.result @@ -328,15 +328,15 @@ ID Name Country Population EXPLAIN SELECT * FROM City WHERE (ID < 10) OR (ID BETWEEN 100 AND 110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 20 Using where EXPLAIN SELECT * FROM City WHERE (ID < 200) OR (ID BETWEEN 100 AND 200); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 200 Using where EXPLAIN SELECT * FROM City WHERE (ID < 600) OR (ID BETWEEN 900 AND 1500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using index condition; Using where +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 1198 Using where EXPLAIN SELECT * FROM City WHERE Country > 'A' AND Country < 'ARG'; id select_type table type possible_keys key key_len ref rows Extra @@ -355,7 +355,7 @@ WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND (Name LIKE 'P%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using index condition; Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 20 Using where EXPLAIN SELECT * FROM City WHERE ((ID < 800) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) @@ -369,7 +369,7 @@ WHERE ((ID < 200) AND (Name LIKE 'Ha%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 200) AND (Name LIKE 'Pa%' OR (Population > 103000 AND Population < 104000))); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using index condition; Using where +1 SIMPLE City range PRIMARY,Population,Country,Name PRIMARY 4 NULL 200 Using where SELECT * FROM City USE INDEX () WHERE ((ID < 10) AND (Name LIKE 'H%' OR (Country > 'A' AND Country < 'ARG'))) OR ((ID BETWEEN 100 AND 110) AND @@ -601,11 +601,11 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3400 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 400 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE Name LIKE 'P%'; id select_type table type possible_keys key key_len ref rows Extra @@ -765,27 +765,27 @@ id select_type table type possible_keys key key_len ref rows Extra EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3790 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4025 AND 4035; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4028 AND 4032; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 5 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 3500 AND 3800; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 300 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 4000 AND 4300; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 80 Using where EXPLAIN SELECT * FROM City WHERE ID BETWEEN 250 and 260 ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using index condition +1 SIMPLE City range PRIMARY PRIMARY 4 NULL 11 Using where EXPLAIN SELECT * FROM City WHERE (Population > 101000 AND Population < 102000); id select_type table type possible_keys key key_len ref rows Extra @@ -1422,7 +1422,7 @@ SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using index condition; Using where +1 SIMPLE t1 range PRIMARY,idx PRIMARY 4 NULL 1 Using where SELECT * FROM t1 WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND (t1.c=0 OR t1.a=500); diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index e98d3931020..80ff21230fb 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -778,7 +778,7 @@ create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1); explain select * from t1 where a > 0 and a < 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using index condition +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where drop table t1; create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=innodb; insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL'); diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index 30e58aacff5..2cb761c6ea1 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -121,7 +121,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using index condition; Using where +Extra Using where id 2 select_type DERIVED table NULL @@ -328,7 +328,7 @@ key PRIMARY key_len 4 ref t2.a rows 1 -Extra Using index condition; Using where +Extra Using where id 2 select_type DERIVED table NULL diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 4dcce4d0373..62dbc849241 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2839,7 +2839,7 @@ SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using index condition; Using filesort +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where; Using filesort 1 SIMPLE t2 ref a a 5 test.t1.pk 1 Using index SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11 diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index be84fa74c2e..14cf325feb1 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -152,7 +152,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -176,7 +176,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -184,7 +184,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc index 5240267b4ac..c2b49d863a1 100644 --- a/sql/opt_index_cond_pushdown.cc +++ b/sql/opt_index_cond_pushdown.cc @@ -329,13 +329,23 @@ void push_index_cond(JOIN_TAB *tab, uint keyno) { DBUG_ENTER("push_index_cond"); Item *idx_cond; - + + /* + Backported the following from MySQL 5.6: + 6. The index is not a clustered index. The performance improvement + of pushing an index condition on a clustered key is much lower + than on a non-clustered key. This restriction should be + re-evaluated when WL#6061 is implemented. + */ if ((tab->table->file->index_flags(keyno, 0, 1) & HA_DO_INDEX_COND_PUSHDOWN) && optimizer_flag(tab->join->thd, OPTIMIZER_SWITCH_INDEX_COND_PUSHDOWN) && tab->join->thd->lex->sql_command != SQLCOM_UPDATE_MULTI && tab->join->thd->lex->sql_command != SQLCOM_DELETE_MULTI && - tab->type != JT_CONST && tab->type != JT_SYSTEM) + tab->type != JT_CONST && tab->type != JT_SYSTEM && + !(keyno == tab->table->s->primary_key && // (6) + tab->table->file->primary_key_is_clustered())) // (6) + { DBUG_EXECUTE("where", print_where(tab->select_cond, "full cond", QT_ORDINARY);); |