DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS>,
t TINYINT <CUSTOM_COL_OPTIONS>,
s SMALLINT <CUSTOM_COL_OPTIONS>,
m MEDIUMINT <CUSTOM_COL_OPTIONS>,
b BIGINT <CUSTOM_COL_OPTIONS>,
<CUSTOM_INDEX> i (i)
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW INDEX IN t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	1	#	1	i	#	#	NULL	NULL	#	#		
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
SELECT i FROM t1 ORDER BY i;
i
1
2
3
5
10
11
12
101
1000
10001
DROP TABLE t1;
CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS> PRIMARY KEY,
t TINYINT <CUSTOM_COL_OPTIONS>,
s SMALLINT <CUSTOM_COL_OPTIONS>,
m MEDIUMINT <CUSTOM_COL_OPTIONS>,
b BIGINT <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
EXPLAIN SELECT i FROM t1 ORDER BY i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
#	#	#	#	#	PRIMARY	#	#	#	Using index
SELECT i FROM t1 ORDER BY i;
i
1
2
3
5
10
11
12
101
1000
10001
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY (i,t);
INSERT INTO t1 (i,t,s,m,b) VALUES (1,3,2,4,5);
EXPLAIN SELECT i, t FROM t1 ORDER BY i, t;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
#	#	#	#	#	PRIMARY	#	#	#	Using index
SELECT i, t FROM t1 ORDER BY i, t;
i	t
1	2
1	3
2	3
3	4
5	100
10	11
11	12
12	13
101	102
1000	100
10001	103
DROP TABLE t1;
CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS>,
t TINYINT <CUSTOM_COL_OPTIONS>,
s SMALLINT <CUSTOM_COL_OPTIONS>,
m MEDIUMINT <CUSTOM_COL_OPTIONS>,
b BIGINT <CUSTOM_COL_OPTIONS>,
INDEX s_m (s,m)
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
EXPLAIN SELECT s, m FROM t1 WHERE s != 10 AND m != 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
#	#	#	#	#	s_m	#	#	#	Using where; Using index
SELECT s, m FROM t1 WHERE s != 10 AND m != 1;
s	m
10000	1000000
10000	1000000
10002	10003
103	104
12	13
13	14
14	15
3	4
4	5
5	6
DROP TABLE t1;
CREATE TABLE t1 (i INT <CUSTOM_COL_OPTIONS>,
t TINYINT <CUSTOM_COL_OPTIONS>,
s SMALLINT <CUSTOM_COL_OPTIONS>,
m MEDIUMINT <CUSTOM_COL_OPTIONS>,
b BIGINT <CUSTOM_COL_OPTIONS>,
UNIQUE KEY b_t (b,t)
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);
SELECT b+t FROM t1 WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
SELECT b+t FROM t1 FORCE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
SELECT b+t FROM t1 IGNORE INDEX (b_t) WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
b+t
9
11
25
27
29
207
10107
100000000000000100
1000000000000000100
DROP TABLE t1;