Optimization of key usage (ORDER BY) (SCRUM)

1 files changed, 34 insertions, 0 deletions

diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 5645961a178..a2b463ab361 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -506,3 +506,37 @@ SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,des
 titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
 test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
 drop table t1,t2;
+CREATE TABLE t1 (
+FieldKey varchar(36) NOT NULL default '',
+LongVal bigint(20) default NULL,
+StringVal mediumtext,
+KEY FieldKey (FieldKey),
+KEY LongField (FieldKey,LongVal),
+KEY StringField (FieldKey,StringVal(32))
+);
+INSERT INTO t1 VALUES ('0',3,'0'),('1',2,'1'),('1',1,'3'), ('1',0,'2');
+EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	FieldKey,LongField,StringField	LongField	36	const	2	Using where
+SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
+FieldKey	LongVal	StringVal
+1	0	2
+1	1	3
+1	2	1
+EXPLAIN SELECT * FROM t1 WHERE FieldKey > '0' ORDER BY LongVal;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	FieldKey,LongField,StringField	FieldKey	36	NULL	3	Using where; Using filesort
+SELECT * FROM t1 WHERE FieldKey > '0' ORDER BY LongVal;
+FieldKey	LongVal	StringVal
+1	0	2
+1	1	3
+1	2	1
+EXPLAIN SELECT * FROM t1 WHERE FieldKey > '0' ORDER BY FieldKey, LongVal;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	FieldKey,LongField,StringField	LongField	36	NULL	3	Using where
+SELECT * FROM t1 WHERE FieldKey > '0' ORDER BY FieldKey, LongVal;
+FieldKey	LongVal	StringVal
+1	0	2
+1	1	3
+1	2	1
+DROP TABLE t1;