1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS>,
c20 CHAR(20) <CUSTOM_COL_OPTIONS>,
v16 VARCHAR(16) <CUSTOM_COL_OPTIONS>,
v128 VARCHAR(128) <CUSTOM_COL_OPTIONS>,
<CUSTOM_INDEX> c20 (c20)
) 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 c20 # # NULL NULL # #
INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
SELECT c20 FROM t1 ORDER BY c20;
c20
char1
char2
char3
char4
DROP TABLE t1;
CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS>,
c20 CHAR(20) <CUSTOM_COL_OPTIONS>,
v16 VARCHAR(16) <CUSTOM_COL_OPTIONS>,
v128 VARCHAR(128) <CUSTOM_COL_OPTIONS>,
<CUSTOM_INDEX> v16 (v16)
) 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 v16 # # NULL NULL # #
INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char1','varchar3a','varchar1b'),('c','char4','varchar4a','varchar4b');
SELECT v16 FROM t1 ORDER BY v16;
v16
varchar1a
varchar2a
varchar3a
varchar4a
DROP TABLE t1;
CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS>,
c20 CHAR(20) <CUSTOM_COL_OPTIONS> PRIMARY KEY,
v16 VARCHAR(16) <CUSTOM_COL_OPTIONS>,
v128 VARCHAR(128) <CUSTOM_COL_OPTIONS>
) 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 0 PRIMARY 1 c20 # # NULL NULL # #
INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
EXPLAIN SELECT c20 FROM t1 ORDER BY c20;
id select_type table type possible_keys key key_len ref rows Extra
# # # # # PRIMARY # # # #
SELECT c20 FROM t1 ORDER BY c20;
c20
char1
char2
char3
char4
EXPLAIN SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
id select_type table type possible_keys key key_len ref rows Extra
# # # # # PRIMARY # # # #
SELECT c20 FROM t1 FORCE INDEX FOR ORDER BY (PRIMARY) ORDER BY c20;
c20
char1
char2
char3
char4
DROP TABLE t1;
CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS>,
c20 CHAR(20) <CUSTOM_COL_OPTIONS>,
v16 VARCHAR(16) <CUSTOM_COL_OPTIONS>,
v128 VARCHAR(128) <CUSTOM_COL_OPTIONS>,
UNIQUE INDEX c_v (c,v128)
) 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 0 c_v 1 c # # NULL NULL # #
t1 0 c_v 2 v128 # # NULL NULL # #
INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
EXPLAIN SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar';
id select_type table type possible_keys key key_len ref rows Extra
# # # # # c_v # # # #
SELECT c, v128 FROM t1 WHERE c != 'a' AND v128 > 'varchar';
c v128
b varchar1b
c varchar3b
EXPLAIN SELECT v128, COUNT(*) FROM t1 GROUP BY v128;
id select_type table type possible_keys key key_len ref rows Extra
# # # # # c_v # # # #
SELECT v128, COUNT(*) FROM t1 GROUP BY v128;
v128 COUNT(*)
varchar1b 2
varchar2b 1
varchar3b 1
EXPLAIN SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128;
id select_type table type possible_keys key key_len ref rows Extra
# # # # # c_v # # # #
SELECT v128, COUNT(*) FROM t1 USE INDEX FOR GROUP BY (c_v) GROUP BY v128;
v128 COUNT(*)
varchar1b 2
varchar2b 1
varchar3b 1
SET SESSION optimizer_switch = 'engine_condition_pushdown=on';
Warnings:
Warning 1681 'engine_condition_pushdown=on' is deprecated and will be removed in a future release
EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a';
id select_type table type possible_keys key key_len ref rows Extra
# # # range c_v c_v # # # Using index condition
SELECT c,c20,v16,v128 FROM t1 WHERE c > 'a';
c c20 v16 v128
b char3 varchar1a varchar1b
c char4 varchar3a varchar3b
SET SESSION optimizer_switch = @@global.optimizer_switch;
DROP TABLE t1;
CREATE TABLE t1 (c CHAR <CUSTOM_COL_OPTIONS>,
c20 CHAR(20) <CUSTOM_COL_OPTIONS>,
v16 VARCHAR(16) <CUSTOM_COL_OPTIONS>,
v128 VARCHAR(128) <CUSTOM_COL_OPTIONS>,
INDEX (v16)
) 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 v16 1 v16 # # NULL NULL # #
INSERT INTO t1 (c,c20,v16,v128) VALUES ('a','char1','varchar1a','varchar1b'),('a','char2','varchar2a','varchar2b'),('b','char3','varchar1a','varchar1b'),('c','char4','varchar3a','varchar3b');
EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 WHERE v16 LIKE 'varchar%';
id select_type table type possible_keys key key_len ref rows Extra
# # # # # v16 # # # #
SELECT SUBSTRING(v16,7,3) FROM t1 WHERE v16 LIKE 'varchar%';
SUBSTRING(v16,7,3)
r1a
r1a
r2a
r3a
EXPLAIN SELECT SUBSTRING(v16,0,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
id select_type table type possible_keys key key_len ref rows Extra
# # # # # NULL # # # #
SELECT SUBSTRING(v16,7,3) FROM t1 IGNORE INDEX (v16) WHERE v16 LIKE 'varchar%';
SUBSTRING(v16,7,3)
r1a
r1a
r2a
r3a
EXPLAIN SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
id select_type table type possible_keys key key_len ref rows Extra
# # # range # v16 # # # #
SELECT c,c20,v16,v128 FROM t1 WHERE v16 = 'varchar1a' OR v16 = 'varchar3a' ORDER BY v16;
c c20 v16 v128
a char1 varchar1a varchar1b
b char3 varchar1a varchar1b
c char4 varchar3a varchar3b
DROP TABLE t1;
|
