diff options
Diffstat (limited to 'mysql-test/main/order_by_optimizer_innodb.result')
| -rw-r--r-- | mysql-test/main/order_by_optimizer_innodb.result | 100 |
1 files changed, 100 insertions, 0 deletions
diff --git a/mysql-test/main/order_by_optimizer_innodb.result b/mysql-test/main/order_by_optimizer_innodb.result new file mode 100644 index 00000000..cbb5551b --- /dev/null +++ b/mysql-test/main/order_by_optimizer_innodb.result @@ -0,0 +1,100 @@ +SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent=OFF; +# +# MDEV-6402: Optimizer doesn't choose best execution plan when composite key is used +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C; +CREATE TABLE t2 ( +pk1 int(11) NOT NULL, +pk2 int(11) NOT NULL, +fd5 bigint(20) DEFAULT NULL, +filler1 char(200), +filler2 char(200), +PRIMARY KEY (pk1,pk2), +UNIQUE KEY ux_pk1_fd5 (pk1,fd5) +) ENGINE=InnoDB; +insert into t2 +select +round(log(2,t1.a+1)), +t1.a, +t1.a, +REPEAT('filler-data-', 10), +REPEAT('filler-data-', 10) +from +t1; +select pk1, count(*) from t2 group by pk1; +pk1 count(*) +0 1 +1 1 +2 3 +3 6 +4 11 +5 23 +6 45 +7 91 +8 181 +9 362 +10 276 +# The following should use range(ux_pk1_fd5), two key parts (key_len=5+8=13) +EXPLAIN SELECT * FROM t2 USE INDEX(ux_pk1_fd5) WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using where +# This also must use range, not ref. key_len must be 13 +EXPLAIN SELECT * FROM t2 WHERE pk1=9 AND fd5 < 500 ORDER BY fd5 DESC LIMIT 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY,ux_pk1_fd5 ux_pk1_fd5 13 NULL 138 Using where +drop table t0,t1, t2; +# +# MDEV-6814: Server crashes in calculate_key_len on query with ORDER BY +# +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, KEY(f2),KEY(f2,f1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,5,0),(2,6,0); +SELECT * FROM t1 WHERE f1 < 3 AND f2 IS NULL ORDER BY f1; +f1 f2 f3 +DROP TABLE t1; +# +# MDEV-6796: Unable to skip filesort when using implicit extended key +# +CREATE TABLE t1 ( +pk1 int(11) NOT NULL, +pk2 varchar(64) NOT NULL, +col1 varchar(16) DEFAULT NULL, +PRIMARY KEY (pk1,pk2), +KEY key1 (pk1,col1) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( +pk1 int(11) NOT NULL, +pk2 varchar(64) NOT NULL, +col1 varchar(16) DEFAULT NULL, +PRIMARY KEY (pk1,pk2), +KEY key1 (pk1,col1,pk2) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO `t1` VALUES +(12321321,'a8f5f167f44f4964e6c998dee827110c','video'), +(12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'), +(12321321,'wwafdsafdsafads','video'), +(12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'), +(12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'), +(12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'), +(12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd'); +insert into t2 select * from t1; +# this must not use filesort: +explain SELECT pk2 +FROM t1 USE INDEX(key1) +WHERE pk1 = 123 +AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref key1 key1 55 const,const 1 Using where; Using index +# this must not use filesort, either: +explain SELECT pk2 +FROM t2 USE INDEX(key1) +WHERE pk1 = 123 AND col1 = 'video' +ORDER BY pk2 DESC LIMIT 21; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref key1 key1 55 const,const 1 Using where; Using index +drop table t1, t2; +SET GLOBAL innodb_stats_persistent=@save_stats_persistent; |