summaryrefslogtreecommitdiff
path: root/mysql-test/r/order_by.result
diff options
context:
space:
mode:
authorSergei Petrunia <psergey@askmonty.org>2014-09-09 13:46:33 +0400
committerSergei Petrunia <psergey@askmonty.org>2014-09-09 13:46:33 +0400
commit8707f172e1d999f87cde6d3d85a320bc569f3618 (patch)
tree6f94925211d6dbbfc828a682daf6b539c53330b6 /mysql-test/r/order_by.result
parentc945233a192d559695b83a252b61168e7611ea03 (diff)
parentbf30585eaf29139ee471a348fc394162ca3333bd (diff)
downloadmariadb-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.result36
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;