diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/maria_icp.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/myisam_icp.result | 19 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_mat.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_opts.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_scache.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 2 |
7 files changed, 24 insertions, 7 deletions
diff --git a/mysql-test/r/maria_icp.result b/mysql-test/r/maria_icp.result index 97b10fe496c..e8dcdfda4a2 100644 --- a/mysql-test/r/maria_icp.result +++ b/mysql-test/r/maria_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; # diff --git a/mysql-test/r/myisam_icp.result b/mysql-test/r/myisam_icp.result index 188347c3324..fdb8018dab6 100644 --- a/mysql-test/r/myisam_icp.result +++ b/mysql-test/r/myisam_icp.result @@ -165,7 +165,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; # @@ -909,4 +909,21 @@ c NULL NULL c NULL NULL SET optimizer_switch=@save_optimizer_switch; DROP TABLE t1,t2,t3,t4; +# +# BUG#1000051: Query with simple join and ORDER BY takes thousands times longer when run with ICP +# +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, col int, filler char(100), key(a, col)); +insert into t1 select A.a + 10*B.a +100*C.a, 1234, 'filler' from t0 A, t0 B, t0 C; +# Must not use ICP: +explain select * from t1 where a between 10 and 20 order by a, col desc; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 11 Using index condition; Rowid-ordered scan; Using filesort +# Must not use ICP: +explain select * from t0,t1 where t1.a=t0.a and t1.col<123; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ref a a 5 test.t0.a 1 Using index condition +drop table t0, t1; set optimizer_switch=@myisam_icp_tmp; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index b01b616ad9a..8f44d29df28 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -3100,7 +3100,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index d93e47ec381..7efda485e18 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -3106,7 +3106,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 98dab5c6506..ff8ab114770 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -3102,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index b8fb9cad049..6bbd8085731 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -3106,7 +3106,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 9141b1c9ef9..f2787a85292 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -3102,7 +3102,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 1 PRIMARY r eq_ref PRIMARY PRIMARY 4 const 1 Using where -2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using index condition +2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; |