diff options
Diffstat (limited to 'mysql-test/t/range.test')
| -rw-r--r-- | mysql-test/t/range.test | 38 |
1 files changed, 36 insertions, 2 deletions
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index a5ff9cc0096..dc9ca0012fe 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -570,12 +570,12 @@ INSERT INTO t1 VALUES EXPLAIN SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; SELECT s.oxid FROM t1 v, t1 s - WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND + WHERE v.oxrootid ='d8c4177d09f8b11f5.52725521' AND s.oxleft > v.oxleft AND s.oxleft < v.oxright; @@ -1481,6 +1481,40 @@ SELECT * FROM t1 IGNORE INDEX(PRIMARY) WHERE F1 BETWEEN 'A ' AND DROP TABLE t1; +--echo # +--echo # mdev-4894: Poor performance with unnecessary +--echo # (bug#70021) 'Range checked for each record' +--echo # + +create table t1( key1 int not null, INDEX i1(key1) ); +insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t1 select key1+8 from t1; +insert into t1 select key1+16 from t1; +insert into t1 select key1+32 from t1; +insert into t1 select key1+64 from t1; +insert into t1 select key1+128 from t1; +insert into t1 select key1+256 from t1; +insert into t1 select key1+512 from t1; + +alter table t1 add key2 int not null, add index i2(key2); +update t1 set key2=key1; +analyze table t1; + +create table t2 (a int); +insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t2 select a+16 from t2; +insert into t2 select a+32 from t2; +insert into t2 select a+64 from t2; + +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000; +explain +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; +select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a; + +drop table t1,t2; + --echo End of 5.1 tests --echo # |