#At file:///home/olav/mysql/develop2/opt-bug52660/ based on revid:roy.lyseng@stripped
3264 Olav.Sandstaa@stripped 2010-10-19
Fix for Bug#52660 Perf. regr. using ICP for MyISAM on range queries on an index containing TEXT
When an index condition is pushed down to MyISAM the content of (most
of) the index entry will be read into the record buffer. This will be
used for evaluating the index condition and the range check in case of
a range scan. When reading the content of the index entry all fields
will be read with the exception of BLOB fields. This is not a problem
for evaluating the index condition itself since the server will not
push down an index condition that contains a BLOB field. But it is a
problem for the range evaluation that is performed as part of the ICP
implementation in MyISAM since this might be done using a "non-read"
BLOB value.
A simplified version of the query given in the bug report:
SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
it will be done as a range scan and following happens :
1. MyISAM locates the first record satisfying the lower range and this
record will be read into the record buffer. So after this the
content of the record buffer for the BLOB field will have the value:
"c-1004=w"
2. When the server requests the next record in the range MyISAM will
due to ICP being used read in the index entry (but not including the
BLOB field), so when the end of range check is done it will evaluate
the upper range value (c1 <= 'c-1006=w') against the value of the
first record read (ie. it will evaluate to true) and since the ICP
condition (i1 > 1) evaluates to false it will skip this record and go
to the next record. This creates a loop that instead of stopping at
the upper range value will loop until it has read the complete index
(or until the ICP condition evaluates to true).
So in the example in the bug report about 10 million index entries
will be read and evaluated instead of just 3 or 4.
The fix for this is to let MyISAM reject pushed index conditions
whenever the index used for ICP contains a BLOB field.
Note that this change disables ICP from being used by MyISAM in some
cases where it actually was safe to use it. The pushed index condition
will be reject in all cases where the index contains a BLOB field also
when the BLOB field is not used in any range evaluation. The reason for
this is at the time the server pushes the index condition MyISAM does
not know whether the actually data access will be a range scan with an
upper range value or not.
The patch contains a simplified version of the original test
case. This test case only contains 100 records which is too little to
show any performance regression. The important point with the test
case is that without the fix the explain will show "Using index
condition" while with the fix it will say "Using where".
@ mysql-test/include/icp_tests.inc
Test case for Bug#52660 Perf. regr. using ICP for MyISAM on range
queries on an index containing TEXT.
@ mysql-test/r/innodb_icp.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/innodb_icp_all.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/innodb_icp_none.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/myisam_icp.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/myisam_icp_all.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/myisam_icp_none.result
Result file for test for Bug#52660 Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT.
@ mysql-test/r/null_key_all.result
Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT". With this fix ICP will no longer be
used for MyISAM if the index for the pushed index condition contains a BLOB field.
@ mysql-test/r/null_key_icp.result
Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT". With this fix ICP will no longer be
used for MyISAM if the index for the pushed index condition contains a BLOB field.
@ mysql-test/r/order_by_all.result
Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT". With this fix ICP will no longer be
used for MyISAM if the index for the pushed index condition contains a BLOB field.
@ mysql-test/r/order_by_icp_mrr.result
Change in explain output due to fix for Bug#52660 "Perf. regr. using ICP for MyISAM
on range queries on an index containing TEXT". With this fix ICP will no longer be
used for MyISAM if the index for the pushed index condition contains a BLOB field.
@ storage/myisam/ha_myisam.cc
Change the ICP implementation for MyISAM to reject any pushed
index conditions if the index contains a BLOB field.
The reason for this change is that MyISAM does not read in
BLOB fields from the index entry as part of the ICP evaluation.
If it is a range scan where the BLOB field is used as part of the
upper range limit then the range evaluation will be done against
a previously read BLOB value and evaluate to true. As a consequence
MyISAM will fail to detect that it has reached the end of a range
interval and continue to read the complete index.
modified:
mysql-test/include/icp_tests.inc
mysql-test/r/innodb_icp.result
mysql-test/r/innodb_icp_all.result
mysql-test/r/innodb_icp_none.result
mysql-test/r/myisam_icp.result
mysql-test/r/myisam_icp_all.result
mysql-test/r/myisam_icp_none.result
mysql-test/r/null_key_all.result
mysql-test/r/null_key_icp.result
mysql-test/r/order_by_all.result
mysql-test/r/order_by_icp_mrr.result
storage/myisam/ha_myisam.cc
=== modified file 'mysql-test/include/icp_tests.inc'
--- a/mysql-test/include/icp_tests.inc 2010-06-20 07:16:27 +0000
+++ b/mysql-test/include/icp_tests.inc 2010-10-19 12:55:25 +0000
@@ -402,3 +402,28 @@ ORDER BY f1;
DROP TABLE t1;
+--echo #
+--echo # Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+--echo # an index containing TEXT"
+--echo #
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+
+CREATE TABLE t3 (
+ c1 TINYTEXT NOT NULL,
+ i1 INT NOT NULL,
+ KEY (c1(6),i1)
+);
+
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+
+DROP TABLE t1, t2, t3;
=== modified file 'mysql-test/r/innodb_icp.result'
--- a/mysql-test/r/innodb_icp.result 2010-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp.result 2010-10-19 12:55:25 +0000
@@ -368,5 +368,26 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_all.result'
--- a/mysql-test/r/innodb_icp_all.result 2010-08-23 11:28:58 +0000
+++ b/mysql-test/r/innodb_icp_all.result 2010-10-19 12:55:25 +0000
@@ -368,5 +368,26 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using index condition; Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_icp_none.result'
--- a/mysql-test/r/innodb_icp_none.result 2010-09-01 13:46:08 +0000
+++ b/mysql-test/r/innodb_icp_none.result 2010-10-19 12:55:25 +0000
@@ -367,5 +367,26 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set storage_engine= @save_storage_engine;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp.result'
--- a/mysql-test/r/myisam_icp.result 2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp.result 2010-10-19 12:55:25 +0000
@@ -366,4 +366,25 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_all.result'
--- a/mysql-test/r/myisam_icp_all.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/myisam_icp_all.result 2010-10-19 12:55:25 +0000
@@ -366,4 +366,25 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_icp_none.result'
--- a/mysql-test/r/myisam_icp_none.result 2010-06-18 08:45:53 +0000
+++ b/mysql-test/r/myisam_icp_none.result 2010-10-19 12:55:25 +0000
@@ -365,4 +365,25 @@ ORDER BY f1;
f1
b
DROP TABLE t1;
+#
+# Bug#52660 - "Perf. regr. using ICP for MyISAM on range queries on
+# an index containing TEXT"
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 SELECT A.a + 10*(B.a) FROM t1 A, t1 B;
+CREATE TABLE t3 (
+c1 TINYTEXT NOT NULL,
+i1 INT NOT NULL,
+KEY (c1(6),i1)
+);
+INSERT INTO t3 SELECT CONCAT('c-',1000+t2.a,'=w'), 1 FROM t2;
+EXPLAIN
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range c1 c1 12 NULL 2 Using where
+SELECT c1 FROM t3 WHERE c1 >= 'c-1004=w' and c1 <= 'c-1006=w' and i1 > 2;
+c1
+DROP TABLE t1, t2, t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/null_key_all.result'
--- a/mysql-test/r/null_key_all.result 2010-08-26 21:32:48 +0000
+++ b/mysql-test/r/null_key_all.result 2010-10-19 12:55:25 +0000
@@ -77,13 +77,13 @@ insert into t2 select * from t1;
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
explain select * from t1 where a is null and b = 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b = 2 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b = 7 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a=2 and b = 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 1 Using where
@@ -92,25 +92,25 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where
+1 SIMPLE t1 range a,b a 5 NULL 5 Using where
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where
+1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a > 1 and a < 3 limit 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 1 Using index condition
+1 SIMPLE t1 range a a 5 NULL 1 Using where
explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 4 Using index condition; Using where
+1 SIMPLE t1 range a,b a 5 NULL 4 Using where
explain select * from t1 where a > 8 and a < 9;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 1 Using index condition
+1 SIMPLE t1 range a a 5 NULL 1 Using where
explain select * from t1 where b like "6%";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range b b 12 NULL 1 Using where
=== modified file 'mysql-test/r/null_key_icp.result'
--- a/mysql-test/r/null_key_icp.result 2010-09-01 13:46:08 +0000
+++ b/mysql-test/r/null_key_icp.result 2010-10-19 12:55:25 +0000
@@ -77,13 +77,13 @@ insert into t2 select * from t1;
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10));
explain select * from t1 where a is null and b = 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b = 2 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b = 7 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a=2 and b = 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref a,b a 5 const 1 Using where
@@ -92,25 +92,25 @@ id select_type table type possible_keys
1 SIMPLE t1 ALL NULL NULL NULL NULL 12 Using where
explain select * from t1 where (a is null or a > 0 and a < 3) and b < 5 and c=0 limit 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 5 Using index condition; Using where
+1 SIMPLE t1 range a,b a 5 NULL 5 Using where
explain select * from t1 where (a is null or a = 7) and b=7 and c=0;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using index condition; Using where
+1 SIMPLE t1 ref_or_null a,b a 5 const 4 Using where
explain select * from t1 where (a is null and b>a) or a is null and b=7 limit 2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a is null and b=9 or a is null and b=7 limit 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ref a,b a 5 const 3 Using index condition; Using where
+1 SIMPLE t1 ref a,b a 5 const 3 Using where
explain select * from t1 where a > 1 and a < 3 limit 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 1 Using index condition
+1 SIMPLE t1 range a a 5 NULL 1 Using where
explain select * from t1 where a is null and b=7 or a > 1 and a < 3 limit 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a,b a 5 NULL 4 Using index condition; Using where
+1 SIMPLE t1 range a,b a 5 NULL 4 Using where
explain select * from t1 where a > 8 and a < 9;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range a a 5 NULL 1 Using index condition
+1 SIMPLE t1 range a a 5 NULL 1 Using where
explain select * from t1 where b like "6%";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range b b 12 NULL 1 Using where
=== modified file 'mysql-test/r/order_by_all.result'
--- a/mysql-test/r/order_by_all.result 2010-09-27 13:20:24 +0000
+++ b/mysql-test/r/order_by_all.result 2010-10-19 12:55:25 +0000
@@ -610,7 +610,7 @@ FieldKey LongVal StringVal
1 2 1
EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort
+1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
FieldKey LongVal StringVal
3 1 2
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2010-09-30 14:57:06 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2010-10-19 12:55:25 +0000
@@ -610,7 +610,7 @@ FieldKey LongVal StringVal
1 2 1
EXPLAIN SELECT * FROM t1 ignore index (FieldKey, LongField) WHERE FieldKey > '2' ORDER BY LongVal;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range StringField StringField 38 NULL 4 Using index condition; Using filesort
+1 SIMPLE t1 range StringField StringField 38 NULL 4 Using where; Using filesort
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
FieldKey LongVal StringVal
3 1 2
=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc 2010-07-13 17:29:44 +0000
+++ b/storage/myisam/ha_myisam.cc 2010-10-19 12:55:25 +0000
@@ -2150,12 +2150,37 @@ ha_rows ha_myisam::multi_range_read_info
Item *ha_myisam::idx_cond_push(uint keyno_arg, Item* idx_cond_arg)
{
- pushed_idx_cond_keyno= keyno_arg;
- pushed_idx_cond= idx_cond_arg;
- in_range_check_pushed_down= TRUE;
- if (active_index == pushed_idx_cond_keyno)
- mi_set_index_cond_func(file, index_cond_func_myisam, this);
- return NULL;
+ /*
+ Check if the key contains a blob field. If it does then MyISAM
+ should not accept the pushed index condition since MyISAM will not
+ read the blob field from the index entry during evaluation of the
+ pushed index condition and the BLOB field might be part of the
+ range evaluation done by the ICP code.
+ */
+ bool key_contains_blob= false;
+ const KEY *key= &table->key_info[keyno_arg];
+
+ for (uint k= 0; k < key->key_parts; ++k)
+ {
+ const KEY_PART_INFO *key_part= &key->key_part[k];
+ if (key_part->key_part_flag & HA_BLOB_PART)
+ {
+ key_contains_blob= true;
+ break;
+ }
+ }
+
+ if (!key_contains_blob)
+ {
+ pushed_idx_cond_keyno= keyno_arg;
+ pushed_idx_cond= idx_cond_arg;
+ in_range_check_pushed_down= TRUE;
+ if (active_index == pushed_idx_cond_keyno)
+ mi_set_index_cond_func(file, index_cond_func_myisam, this);
+ return NULL;
+ }
+
+ return idx_cond_arg;
}
Attachment: [text/bzr-bundle] bzr/olav.sandstaa@sun.com-20101019125525-lqxxaalnytunyv25.bundle
