| 1 |
SET @@session.default_storage_engine = 'InnoDB'; |
| 2 |
SET @save_stats_persistent=@@GLOBAL.innodb_stats_persistent; |
| 3 |
SET GLOBAL innodb_stats_persistent=0; |
| 4 |
create table t1 (a int, |
| 5 |
b int as (-a), |
| 6 |
c int as (-a) persistent, |
| 7 |
index (c)); |
| 8 |
insert into t1 (a) values (2), (1), (1), (3), (NULL); |
| 9 |
create table t2 like t1; |
| 10 |
insert into t2 (a) values (1); |
| 11 |
create table t3 (a int primary key, |
| 12 |
b int as (-a), |
| 13 |
c int as (-a) persistent unique); |
| 14 |
insert into t3 (a) values (2),(1),(3),(5),(4),(7); |
| 15 |
# select_type=SIMPLE, type=system |
| 16 |
select * from t2; |
| 17 |
a b c |
| 18 |
1 -1 -1 |
| 19 |
explain select * from t2; |
| 20 |
id select_type table type possible_keys key key_len ref rows Extra |
| 21 |
1 SIMPLE t2 ALL NULL NULL NULL NULL 1 |
| 22 |
select * from t2 where c=-1; |
| 23 |
a b c |
| 24 |
1 -1 -1 |
| 25 |
explain select * from t2 where c=-1; |
| 26 |
id select_type table type possible_keys key key_len ref rows Extra |
| 27 |
1 SIMPLE t2 ref c c 5 const 1 |
| 28 |
# select_type=SIMPLE, type=ALL |
| 29 |
select * from t1 where b=-1; |
| 30 |
a b c |
| 31 |
1 -1 -1 |
| 32 |
1 -1 -1 |
| 33 |
explain select * from t1 where b=-1; |
| 34 |
id select_type table type possible_keys key key_len ref rows Extra |
| 35 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where |
| 36 |
# select_type=SIMPLE, type=const |
| 37 |
select * from t3 where a=1; |
| 38 |
a b c |
| 39 |
1 -1 -1 |
| 40 |
explain select * from t3 where a=1; |
| 41 |
id select_type table type possible_keys key key_len ref rows Extra |
| 42 |
1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 |
| 43 |
# select_type=SIMPLE, type=range |
| 44 |
select * from t3 where c>=-1; |
| 45 |
a b c |
| 46 |
1 -1 -1 |
| 47 |
explain select * from t3 where c>=-1; |
| 48 |
id select_type table type possible_keys key key_len ref rows Extra |
| 49 |
1 SIMPLE t3 range c c 5 NULL 1 Using index condition |
| 50 |
# select_type=SIMPLE, type=ref |
| 51 |
select * from t1,t3 where t1.c=t3.c and t3.c=-1; |
| 52 |
a b c a b c |
| 53 |
1 -1 -1 1 -1 -1 |
| 54 |
1 -1 -1 1 -1 -1 |
| 55 |
explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; |
| 56 |
id select_type table type possible_keys key key_len ref rows Extra |
| 57 |
1 SIMPLE t3 const c c 5 const 1 |
| 58 |
1 SIMPLE t1 ref c c 5 const 2 |
| 59 |
# select_type=PRIMARY, type=index,ALL |
| 60 |
select * from t1 where b in (select c from t3); |
| 61 |
a b c |
| 62 |
2 -2 -2 |
| 63 |
1 -1 -1 |
| 64 |
1 -1 -1 |
| 65 |
3 -3 -3 |
| 66 |
explain select * from t1 where b in (select c from t3); |
| 67 |
id select_type table type possible_keys key key_len ref rows Extra |
| 68 |
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where |
| 69 |
1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index |
| 70 |
# select_type=PRIMARY, type=range,ref |
| 71 |
select * from t1 where c in (select c from t3 where c between -2 and -1); |
| 72 |
a b c |
| 73 |
2 -2 -2 |
| 74 |
1 -1 -1 |
| 75 |
1 -1 -1 |
| 76 |
explain select * from t1 where c in (select c from t3 where c between -2 and -1); |
| 77 |
id select_type table type possible_keys key key_len ref rows Extra |
| 78 |
1 PRIMARY t3 range c c 5 NULL 2 Using where; Using index |
| 79 |
1 PRIMARY t1 ref c c 5 test.t3.c 1 |
| 80 |
# select_type=UNION, type=system |
| 81 |
# select_type=UNION RESULT, type=<union1,2> |
| 82 |
select * from t1 union select * from t2; |
| 83 |
a b c |
| 84 |
2 -2 -2 |
| 85 |
1 -1 -1 |
| 86 |
3 -3 -3 |
| 87 |
NULL NULL NULL |
| 88 |
explain select * from t1 union select * from t2; |
| 89 |
id select_type table type possible_keys key key_len ref rows Extra |
| 90 |
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 |
| 91 |
2 UNION t2 ALL NULL NULL NULL NULL 1 |
| 92 |
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL |
| 93 |
# select_type=DERIVED, type=system |
| 94 |
set @tmp_optimizer_switch=@@optimizer_switch; |
| 95 |
set optimizer_switch='derived_merge=off,derived_with_keys=off'; |
| 96 |
select * from (select a,b,c from t1) as t11; |
| 97 |
a b c |
| 98 |
2 -2 -2 |
| 99 |
1 -1 -1 |
| 100 |
1 -1 -1 |
| 101 |
3 -3 -3 |
| 102 |
NULL NULL NULL |
| 103 |
explain select * from (select a,b,c from t1) as t11; |
| 104 |
id select_type table type possible_keys key key_len ref rows Extra |
| 105 |
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 |
| 106 |
2 DERIVED t1 ALL NULL NULL NULL NULL 5 |
| 107 |
set optimizer_switch=@tmp_optimizer_switch; |
| 108 |
### |
| 109 |
### Using aggregate functions with/without DISTINCT |
| 110 |
### |
| 111 |
# SELECT COUNT(*) FROM tbl_name |
| 112 |
select count(*) from t1; |
| 113 |
count(*) |
| 114 |
5 |
| 115 |
explain select count(*) from t1; |
| 116 |
id select_type table type possible_keys key key_len ref rows Extra |
| 117 |
1 SIMPLE t1 index NULL c 5 NULL 5 Using index |
| 118 |
# SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name |
| 119 |
select count(distinct a) from t1; |
| 120 |
count(distinct a) |
| 121 |
3 |
| 122 |
explain select count(distinct a) from t1; |
| 123 |
id select_type table type possible_keys key key_len ref rows Extra |
| 124 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 |
| 125 |
# SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name |
| 126 |
select count(distinct b) from t1; |
| 127 |
count(distinct b) |
| 128 |
3 |
| 129 |
explain select count(distinct b) from t1; |
| 130 |
id select_type table type possible_keys key key_len ref rows Extra |
| 131 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 |
| 132 |
# SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name |
| 133 |
select count(distinct c) from t1; |
| 134 |
count(distinct c) |
| 135 |
3 |
| 136 |
explain select count(distinct c) from t1; |
| 137 |
id select_type table type possible_keys key key_len ref rows Extra |
| 138 |
1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by |
| 139 |
### |
| 140 |
### filesort & range-based utils |
| 141 |
### |
| 142 |
# SELECT * FROM tbl_name WHERE <vcol expr> |
| 143 |
select * from t3 where c >= -2; |
| 144 |
a b c |
| 145 |
2 -2 -2 |
| 146 |
1 -1 -1 |
| 147 |
explain select * from t3 where c >= -2; |
| 148 |
id select_type table type possible_keys key key_len ref rows Extra |
| 149 |
1 SIMPLE t3 range c c 5 NULL 2 Using index condition |
| 150 |
# SELECT * FROM tbl_name WHERE <non-vcol expr> |
| 151 |
select * from t3 where a between 1 and 2; |
| 152 |
a b c |
| 153 |
1 -1 -1 |
| 154 |
2 -2 -2 |
| 155 |
explain select * from t3 where a between 1 and 2; |
| 156 |
id select_type table type possible_keys key key_len ref rows Extra |
| 157 |
1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where |
| 158 |
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> |
| 159 |
select * from t3 where b between -2 and -1; |
| 160 |
a b c |
| 161 |
1 -1 -1 |
| 162 |
2 -2 -2 |
| 163 |
explain select * from t3 where b between -2 and -1; |
| 164 |
id select_type table type possible_keys key key_len ref rows Extra |
| 165 |
1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where |
| 166 |
# SELECT * FROM tbl_name WHERE <indexed vcol expr> |
| 167 |
select * from t3 where c between -2 and -1; |
| 168 |
a b c |
| 169 |
2 -2 -2 |
| 170 |
1 -1 -1 |
| 171 |
explain select * from t3 where c between -2 and -1; |
| 172 |
id select_type table type possible_keys key key_len ref rows Extra |
| 173 |
1 SIMPLE t3 range c c 5 NULL 2 Using index condition |
| 174 |
# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol> |
| 175 |
select * from t3 where a between 1 and 2 order by b; |
| 176 |
a b c |
| 177 |
2 -2 -2 |
| 178 |
1 -1 -1 |
| 179 |
explain select * from t3 where a between 1 and 2 order by b; |
| 180 |
id select_type table type possible_keys key key_len ref rows Extra |
| 181 |
1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort |
| 182 |
# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> |
| 183 |
select * from t3 where a between 1 and 2 order by c; |
| 184 |
a b c |
| 185 |
2 -2 -2 |
| 186 |
1 -1 -1 |
| 187 |
explain select * from t3 where a between 1 and 2 order by c; |
| 188 |
id select_type table type possible_keys key key_len ref rows Extra |
| 189 |
1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort |
| 190 |
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> |
| 191 |
select * from t3 where b between -2 and -1 order by a; |
| 192 |
a b c |
| 193 |
1 -1 -1 |
| 194 |
2 -2 -2 |
| 195 |
explain select * from t3 where b between -2 and -1 order by a; |
| 196 |
id select_type table type possible_keys key key_len ref rows Extra |
| 197 |
1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where |
| 198 |
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> |
| 199 |
select * from t3 where b between -2 and -1 order by b; |
| 200 |
a b c |
| 201 |
2 -2 -2 |
| 202 |
1 -1 -1 |
| 203 |
explain select * from t3 where b between -2 and -1 order by b; |
| 204 |
id select_type table type possible_keys key key_len ref rows Extra |
| 205 |
1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort |
| 206 |
# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> |
| 207 |
select * from t3 where c between -2 and -1 order by b; |
| 208 |
a b c |
| 209 |
2 -2 -2 |
| 210 |
1 -1 -1 |
| 211 |
explain select * from t3 where c between -2 and -1 order by b; |
| 212 |
id select_type table type possible_keys key key_len ref rows Extra |
| 213 |
1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort |
| 214 |
# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> |
| 215 |
select * from t3 where b between -2 and -1 order by c; |
| 216 |
a b c |
| 217 |
2 -2 -2 |
| 218 |
1 -1 -1 |
| 219 |
explain select * from t3 where b between -2 and -1 order by c; |
| 220 |
id select_type table type possible_keys key key_len ref rows Extra |
| 221 |
1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort |
| 222 |
# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> |
| 223 |
select * from t3 where c between -2 and -1 order by c; |
| 224 |
a b c |
| 225 |
2 -2 -2 |
| 226 |
1 -1 -1 |
| 227 |
explain select * from t3 where c between -2 and -1 order by c; |
| 228 |
id select_type table type possible_keys key key_len ref rows Extra |
| 229 |
1 SIMPLE t3 range c c 5 NULL 2 Using index condition |
| 230 |
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> |
| 231 |
select sum(b) from t1 group by b; |
| 232 |
sum(b) |
| 233 |
NULL |
| 234 |
-3 |
| 235 |
-2 |
| 236 |
-2 |
| 237 |
explain select sum(b) from t1 group by b; |
| 238 |
id select_type table type possible_keys key key_len ref rows Extra |
| 239 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort |
| 240 |
# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> |
| 241 |
select sum(c) from t1 group by c; |
| 242 |
sum(c) |
| 243 |
NULL |
| 244 |
-3 |
| 245 |
-2 |
| 246 |
-2 |
| 247 |
explain select sum(c) from t1 group by c; |
| 248 |
id select_type table type possible_keys key key_len ref rows Extra |
| 249 |
1 SIMPLE t1 index NULL c 5 NULL 5 Using index |
| 250 |
# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> |
| 251 |
select sum(b) from t1 group by c; |
| 252 |
sum(b) |
| 253 |
NULL |
| 254 |
-3 |
| 255 |
-2 |
| 256 |
-2 |
| 257 |
explain select sum(b) from t1 group by c; |
| 258 |
id select_type table type possible_keys key key_len ref rows Extra |
| 259 |
1 SIMPLE t1 index NULL c 5 NULL 5 |
| 260 |
# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> |
| 261 |
select sum(c) from t1 group by b; |
| 262 |
sum(c) |
| 263 |
NULL |
| 264 |
-3 |
| 265 |
-2 |
| 266 |
-2 |
| 267 |
explain select sum(c) from t1 group by b; |
| 268 |
id select_type table type possible_keys key key_len ref rows Extra |
| 269 |
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort |
| 270 |
SET GLOBAL innodb_stats_persistent=@save_stats_persistent; |