SET @@session.default_storage_engine = 'MyISAM'; create table t1 (a int, b int as (-a), c int as (-a) persistent, index (c)); insert into t1 (a) values (2), (1), (1), (3), (NULL); create table t2 like t1; insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); insert into t3 (a) values (2),(1),(3),(5),(4),(7); # select_type=SIMPLE, type=system select * from t2; a b c 1 -1 -1 explain select * from t2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 1 select * from t2 where c=-1; a b c 1 -1 -1 explain select * from t2 where c=-1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system c NULL NULL NULL 1 # select_type=SIMPLE, type=ALL select * from t1 where b=-1; a b c 1 -1 -1 1 -1 -1 explain select * from t1 where b=-1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where # select_type=SIMPLE, type=const select * from t3 where a=1; a b c 1 -1 -1 explain select * from t3 where a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 # select_type=SIMPLE, type=range select * from t3 where c>=-1; a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c 1 -1 -1 1 -1 -1 1 -1 -1 1 -1 -1 explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const c c 5 const 1 1 SIMPLE t1 ref c c 5 const 2 # select_type=PRIMARY, type=index,ALL select * from t1 where b in (select c from t3); a b c 2 -2 -2 1 -1 -1 1 -1 -1 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where 1 PRIMARY t3 ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c 2 -2 -2 1 -1 -1 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 range c c 5 NULL 3 Using index condition 1 PRIMARY t3 ref c c 5 test.t1.c 1 Using index # select_type=UNION, type=system # select_type=UNION RESULT, type= select * from t1 union select * from t2; a b c 2 -2 -2 1 -1 -1 3 -3 -3 NULL NULL NULL explain select * from t1 union select * from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 2 UNION t2 system NULL NULL NULL NULL 1 NULL UNION RESULT ALL NULL NULL NULL NULL NULL # select_type=DERIVED, type=system set @tmp_optimizer_switch=@@optimizer_switch; set optimizer_switch='derived_merge=off,derived_with_keys=off'; select * from (select a,b,c from t1) as t11; a b c 2 -2 -2 1 -1 -1 1 -1 -1 3 -3 -3 NULL NULL NULL explain select * from (select a,b,c from t1) as t11; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ALL NULL NULL NULL NULL 5 2 DERIVED t1 ALL NULL NULL NULL NULL 5 set optimizer_switch=@tmp_optimizer_switch; ### ### Using aggregate functions with/without DISTINCT ### # SELECT COUNT(*) FROM tbl_name select count(*) from t1; count(*) 5 explain select count(*) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away # SELECT COUNT(DISTINCT ) FROM tbl_name select count(distinct a) from t1; count(distinct a) 3 explain select count(distinct a) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 # SELECT COUNT(DISTINCT ) FROM tbl_name select count(distinct b) from t1; count(distinct b) 3 explain select count(distinct b) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 # SELECT COUNT(DISTINCT ) FROM tbl_name select count(distinct c) from t1; count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by (scanning) ### ### filesort & range-based utils ### # SELECT * FROM tbl_name WHERE select * from t3 where c >= -2; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where c >= -2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE select * from t3 where a between 1 and 2; a b c 1 -1 -1 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE select * from t3 where b between -2 and -1; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE select * from t3 where c between -2 and -1; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where a between 1 and 2 order by c; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where b between -2 and -1 order by a; a b c 1 -1 -1 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where c between -2 and -1 order by a; a b c 1 -1 -1 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where b between -2 and -1 order by b; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where c between -2 and -1 order by b; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where b between -2 and -1 order by c; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE ORDER BY select * from t3 where c between -2 and -1 order by c; a b c 2 -2 -2 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT sum() FROM tbl_name GROUP BY select sum(b) from t1 group by b; sum(b) NULL -3 -2 -2 explain select sum(b) from t1 group by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort # SELECT sum() FROM tbl_name GROUP BY select sum(c) from t1 group by c; sum(c) NULL -3 -2 -2 explain select sum(c) from t1 group by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c 5 NULL 5 Using index # SELECT sum() FROM tbl_name GROUP BY select sum(b) from t1 group by c; sum(b) NULL -3 -2 -2 explain select sum(b) from t1 group by c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort # SELECT sum() FROM tbl_name GROUP BY select sum(c) from t1 group by b; sum(c) NULL -3 -2 -2 explain select sum(c) from t1 group by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort # # Bug #806057: join with USING over a virtual column # CREATE TABLE t1 (b int); INSERT INTO t1 VALUES (NULL),( 78), (185), (0), (154); CREATE TABLE t2 (a int, b int AS (a) VIRTUAL); INSERT IGNORE INTO t2 VALUES (187,187), (9,9), (187,187); Warnings: Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored Warning 1906 The value specified for generated column 'b' in table 't2' has been ignored EXPLAIN EXTENDED SELECT * FROM t1 JOIN t2 USING (b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t2`.`b` SELECT * FROM t1 JOIN t2 USING (b); b a EXPLAIN EXTENDED SELECT * FROM t1 NATURAL JOIN t2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Using where; Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t2`.`b` SELECT * FROM t1 NATURAL JOIN t2; b a DROP TABLE t1,t2; create table t1 ( pk integer auto_increment, bi integer not null, vi integer generated always as (bi) persistent, bc varchar(1) not null, vc varchar(2) generated always as (concat(bc, bc)) persistent, primary key (pk), key (vi, vc)); insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v'); create table t2 ( pk integer auto_increment, bi integer not null, vi integer generated always as (bi) persistent, bc varchar(257) not null, vc varchar(2) generated always as (concat(bc, bc)) persistent, primary key (pk), key (vi, vc)); insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y'); explain # should be using join buffer select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL vi 10 NULL 19 Using index 1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); vi 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 6 6 6 7 7 8 8 8 8 9 9 drop table t2,t1; create table t1 ( pk integer auto_increment, bi integer not null, vi integer generated always as (bi) virtual, bc varchar(1) not null, vc varchar(2) generated always as (concat(bc, bc)), primary key (pk), key (vi, vc)); insert t1 (bi, bc) values (0, 'x'), (0, 'n'), (1, 'w'), (7, 's'), (0, 'a'), (4, 'd'), (1, 'w'), (1, 'j'), (1, 'm'), (4, 'k'), (7, 't'), (4, 'k'), (2, 'e'), (0, 'i'), (1, 't'), (6, 'z'), (3, 'c'), (6, 'i'), (8, 'v'); create table t2 ( pk integer auto_increment, bi integer not null, vi integer generated always as (bi) virtual, bc varchar(257) not null, vc varchar(2) generated always as (concat(bc, bc)), primary key (pk), key (vi, vc)); insert t2 (bi, bc) values (1, 'c'), (8, 'm'), (9, 'd'), (6, 'y'), (1, 't'), (6, 'd'), (2, 's'), (4, 'r'), (8, 'm'), (4, 'b'), (4, 'x'), (7, 'g'), (4, 'p'), (1, 'q'), (9, 'w'), (4, 'd'), (8, 'e'), (4, 'b'), (8, 'y'); explain # should be using join buffer select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL vi 10 NULL 19 Using index 1 SIMPLE t1 ALL NULL NULL NULL NULL 19 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 index NULL PRIMARY 4 NULL 19 Using where; Using index; Using join buffer (incremental, BNL join) select t2.vi from (t2 as t3 right join (t2 left join t1 on (t1.bi = t2.vi)) on (t1.vc = t2.vc)); vi 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 6 6 6 6 7 7 8 8 8 8 9 9 drop table t2,t1;