diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2014-09-09 13:46:33 +0400 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2014-09-09 13:46:33 +0400 |
commit | 8707f172e1d999f87cde6d3d85a320bc569f3618 (patch) | |
tree | 6f94925211d6dbbfc828a682daf6b539c53330b6 /mysql-test/r/order_by.result | |
parent | c945233a192d559695b83a252b61168e7611ea03 (diff) | |
parent | bf30585eaf29139ee471a348fc394162ca3333bd (diff) | |
download | mariadb-git-8707f172e1d999f87cde6d3d85a320bc569f3618.tar.gz |
Merge 10.1 into bb-10.1-mdev6657
Diffstat (limited to 'mysql-test/r/order_by.result')
-rw-r--r-- | mysql-test/r/order_by.result | 36 |
1 files changed, 36 insertions, 0 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index aba8d6c201c..d5b25534de0 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2949,3 +2949,39 @@ explain update t1 set key1=key1+1 where key1 between 10 and 110 order by key1 li id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range key1 key1 5 NULL 2 Using where; Using buffer drop table t1,t2; +# +# MDEV-465: Optimizer : wrong index choice, leading to strong performances issues +# +CREATE TABLE t1 ( +id1 int(10) unsigned NOT NULL auto_increment, +id2 tinyint(3) unsigned NOT NULL default '0', +id3 tinyint(3) unsigned NOT NULL default '0', +id4 int(10) unsigned NOT NULL default '0', +date timestamp NOT NULL default CURRENT_TIMESTAMP, +PRIMARY KEY (id1), +KEY id_234_date (id2,id3,id4,date), +KEY id_23_date (id2,id3,date) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# t1 has "bad" index declaration order.. +CREATE TABLE t2 ( +id1 int(10) unsigned NOT NULL auto_increment, +id2 tinyint(3) unsigned NOT NULL default '0', +id3 tinyint(3) unsigned NOT NULL default '0', +id4 int(10) unsigned NOT NULL default '0', +date timestamp NOT NULL default CURRENT_TIMESTAMP, +PRIMARY KEY (id1), +KEY id_23_date (id2,id3,date), +KEY id_234_date (id2,id3,id4,date) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +# t2 has a "good" index declaration order +INSERT INTO t1 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); +INSERT INTO t2 (id2,id3,id4) VALUES (1,1,1),(1,1,1),(1,1,1),(1,1,1),(1,0,1),(1,2,1),(1,3,1); +# The following two must both use id_23_date and no "using filesort": +EXPLAIN SELECT id1 FROM t1 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range id_234_date,id_23_date id_23_date 2 NULL 3 Using where +# See above query +EXPLAIN SELECT id1 FROM t2 WHERE id2=1 AND id3=1 ORDER BY date DESC LIMIT 0,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref id_23_date,id_234_date id_23_date 2 const,const 3 Using where +drop table t1,t2; |