1SET @@session.default_storage_engine = 'InnoDB'; 2create table t1 (a int, 3b int as (-a), 4c int as (-a) persistent, 5index (c)); 6insert into t1 (a) values (2), (1), (1), (3), (NULL); 7create table t2 like t1; 8insert into t2 (a) values (1); 9create table t3 (a int primary key, 10b int as (-a), 11c int as (-a) persistent unique); 12insert into t3 (a) values (2),(1),(3),(5),(4),(7); 13# select_type=SIMPLE, type=system 14select * from t2; 15a b c 161 -1 -1 17explain select * from t2; 18id select_type table type possible_keys key key_len ref rows Extra 191 SIMPLE t2 ALL NULL NULL NULL NULL 1 20select * from t2 where c=-1; 21a b c 221 -1 -1 23explain select * from t2 where c=-1; 24id select_type table type possible_keys key key_len ref rows Extra 251 SIMPLE t2 ref c c 5 const 1 26# select_type=SIMPLE, type=ALL 27select * from t1 where b=-1; 28a b c 291 -1 -1 301 -1 -1 31explain select * from t1 where b=-1; 32id select_type table type possible_keys key key_len ref rows Extra 331 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 34# select_type=SIMPLE, type=const 35select * from t3 where a=1; 36a b c 371 -1 -1 38explain select * from t3 where a=1; 39id select_type table type possible_keys key key_len ref rows Extra 401 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 41# select_type=SIMPLE, type=range 42select * from t3 where c>=-1; 43a b c 441 -1 -1 45explain select * from t3 where c>=-1; 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t3 range c c 5 NULL 1 Using index condition 48# select_type=SIMPLE, type=ref 49select * from t1,t3 where t1.c=t3.c and t3.c=-1; 50a b c a b c 511 -1 -1 1 -1 -1 521 -1 -1 1 -1 -1 53explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; 54id select_type table type possible_keys key key_len ref rows Extra 551 SIMPLE t3 const c c 5 const 1 561 SIMPLE t1 ref c c 5 const 2 57# select_type=PRIMARY, type=index,ALL 58select * from t1 where b in (select c from t3); 59a b c 602 -2 -2 611 -1 -1 621 -1 -1 633 -3 -3 64explain select * from t1 where b in (select c from t3); 65id select_type table type possible_keys key key_len ref rows Extra 661 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where 671 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index 68# select_type=PRIMARY, type=range,ref 69select * from t1 where c in (select c from t3 where c between -2 and -1); 70a b c 712 -2 -2 721 -1 -1 731 -1 -1 74explain select * from t1 where c in (select c from t3 where c between -2 and -1); 75id select_type table type possible_keys key key_len ref rows Extra 761 PRIMARY t3 range c c 5 NULL 2 Using where; Using index 771 PRIMARY t1 ref c c 5 test.t3.c 1 78# select_type=UNION, type=system 79# select_type=UNION RESULT, type=<union1,2> 80select * from t1 union select * from t2; 81a b c 822 -2 -2 831 -1 -1 843 -3 -3 85NULL NULL NULL 86explain select * from t1 union select * from t2; 87id select_type table type possible_keys key key_len ref rows Extra 881 PRIMARY t1 ALL NULL NULL NULL NULL 5 892 UNION t2 ALL NULL NULL NULL NULL 1 90NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 91# select_type=DERIVED, type=system 92set @tmp_optimizer_switch=@@optimizer_switch; 93set optimizer_switch='derived_merge=off,derived_with_keys=off'; 94select * from (select a,b,c from t1) as t11; 95a b c 962 -2 -2 971 -1 -1 981 -1 -1 993 -3 -3 100NULL NULL NULL 101explain select * from (select a,b,c from t1) as t11; 102id select_type table type possible_keys key key_len ref rows Extra 1031 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 1042 DERIVED t1 ALL NULL NULL NULL NULL 5 105set optimizer_switch=@tmp_optimizer_switch; 106### 107### Using aggregate functions with/without DISTINCT 108### 109# SELECT COUNT(*) FROM tbl_name 110select count(*) from t1; 111count(*) 1125 113explain select count(*) from t1; 114id select_type table type possible_keys key key_len ref rows Extra 1151 SIMPLE t1 index NULL c 5 NULL 5 Using index 116# SELECT COUNT(DISTINCT <non-vcol>) FROM tbl_name 117select count(distinct a) from t1; 118count(distinct a) 1193 120explain select count(distinct a) from t1; 121id select_type table type possible_keys key key_len ref rows Extra 1221 SIMPLE t1 ALL NULL NULL NULL NULL 5 123# SELECT COUNT(DISTINCT <non-stored vcol>) FROM tbl_name 124select count(distinct b) from t1; 125count(distinct b) 1263 127explain select count(distinct b) from t1; 128id select_type table type possible_keys key key_len ref rows Extra 1291 SIMPLE t1 ALL NULL NULL NULL NULL 5 130# SELECT COUNT(DISTINCT <stored vcol>) FROM tbl_name 131select count(distinct c) from t1; 132count(distinct c) 1333 134explain select count(distinct c) from t1; 135id select_type table type possible_keys key key_len ref rows Extra 1361 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by 137### 138### filesort & range-based utils 139### 140# SELECT * FROM tbl_name WHERE <vcol expr> 141select * from t3 where c >= -2; 142a b c 1432 -2 -2 1441 -1 -1 145explain select * from t3 where c >= -2; 146id select_type table type possible_keys key key_len ref rows Extra 1471 SIMPLE t3 range c c 5 NULL 2 Using index condition 148# SELECT * FROM tbl_name WHERE <non-vcol expr> 149select * from t3 where a between 1 and 2; 150a b c 1511 -1 -1 1522 -2 -2 153explain select * from t3 where a between 1 and 2; 154id select_type table type possible_keys key key_len ref rows Extra 1551 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where 156# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> 157select * from t3 where b between -2 and -1; 158a b c 1591 -1 -1 1602 -2 -2 161explain select * from t3 where b between -2 and -1; 162id select_type table type possible_keys key key_len ref rows Extra 1631 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where 164# SELECT * FROM tbl_name WHERE <indexed vcol expr> 165select * from t3 where c between -2 and -1; 166a b c 1672 -2 -2 1681 -1 -1 169explain select * from t3 where c between -2 and -1; 170id select_type table type possible_keys key key_len ref rows Extra 1711 SIMPLE t3 range c c 5 NULL 2 Using index condition 172# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <non-indexed vcol> 173select * from t3 where a between 1 and 2 order by b; 174a b c 1752 -2 -2 1761 -1 -1 177explain select * from t3 where a between 1 and 2 order by b; 178id select_type table type possible_keys key key_len ref rows Extra 1791 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 180# SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> 181select * from t3 where a between 1 and 2 order by c; 182a b c 1832 -2 -2 1841 -1 -1 185explain select * from t3 where a between 1 and 2 order by c; 186id select_type table type possible_keys key key_len ref rows Extra 1871 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 188# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> 189select * from t3 where b between -2 and -1 order by a; 190a b c 1911 -1 -1 1922 -2 -2 193explain select * from t3 where b between -2 and -1 order by a; 194id select_type table type possible_keys key key_len ref rows Extra 1951 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where 196# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> 197select * from t3 where b between -2 and -1 order by b; 198a b c 1992 -2 -2 2001 -1 -1 201explain select * from t3 where b between -2 and -1 order by b; 202id select_type table type possible_keys key key_len ref rows Extra 2031 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 204# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> 205select * from t3 where c between -2 and -1 order by b; 206a b c 2072 -2 -2 2081 -1 -1 209explain select * from t3 where c between -2 and -1 order by b; 210id select_type table type possible_keys key key_len ref rows Extra 2111 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort 212# SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> 213select * from t3 where b between -2 and -1 order by c; 214a b c 2152 -2 -2 2161 -1 -1 217explain select * from t3 where b between -2 and -1 order by c; 218id select_type table type possible_keys key key_len ref rows Extra 2191 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 220# SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> 221select * from t3 where c between -2 and -1 order by c; 222a b c 2232 -2 -2 2241 -1 -1 225explain select * from t3 where c between -2 and -1 order by c; 226id select_type table type possible_keys key key_len ref rows Extra 2271 SIMPLE t3 range c c 5 NULL 2 Using index condition 228# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> 229select sum(b) from t1 group by b; 230sum(b) 231NULL 232-3 233-2 234-2 235explain select sum(b) from t1 group by b; 236id select_type table type possible_keys key key_len ref rows Extra 2371 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 238# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> 239select sum(c) from t1 group by c; 240sum(c) 241NULL 242-3 243-2 244-2 245explain select sum(c) from t1 group by c; 246id select_type table type possible_keys key key_len ref rows Extra 2471 SIMPLE t1 index NULL c 5 NULL 5 Using index 248# SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <indexed vcol> 249select sum(b) from t1 group by c; 250sum(b) 251NULL 252-3 253-2 254-2 255explain select sum(b) from t1 group by c; 256id select_type table type possible_keys key key_len ref rows Extra 2571 SIMPLE t1 index NULL c 5 NULL 5 258# SELECT sum(<indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> 259select sum(c) from t1 group by b; 260sum(c) 261NULL 262-3 263-2 264-2 265explain select sum(c) from t1 group by b; 266id select_type table type possible_keys key key_len ref rows Extra 2671 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 268