set @save_storage_engine= @@session.default_storage_engine; set session default_storage_engine = MyISAM; #1 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: UPDATE t1 SET a = 10 WHERE a < 10 # select: SELECT * FROM t1 WHERE a < 10 # EXPLAIN UPDATE t1 SET a = 10 WHERE a < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`a` = 10 where `test`.`t1`.`a` < 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 10 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 Handler_update 3 DROP TABLE t1; #2 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: DELETE FROM t1 WHERE a < 10 # select: SELECT * FROM t1 WHERE a < 10 # EXPLAIN DELETE FROM t1 WHERE a < 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` < 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 10 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_delete 3 Handler_read_key 2 Handler_read_rnd_next 4 DROP TABLE t1; #3 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: DELETE FROM t1 USING t1 WHERE a = 1 # select: SELECT * FROM t1 WHERE a = 1 # EXPLAIN DELETE FROM t1 USING t1 WHERE a = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using `test`.`t1` where `test`.`t1`.`a` = 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 2 Handler_read_rnd_next 4 DROP TABLE t1; #4 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1 # select: SELECT * FROM t1, t2 WHERE t1.a = 1 # EXPLAIN UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET t1.a = 10 WHERE t1.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 update `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t1`.`a` = 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE t1.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 Handler_update 1 DROP TABLE t1, t2; #5 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1 # select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1 # EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY ALL NULL NULL NULL NULL 3 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` = 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a = 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where `test`.`t11`.`a` = 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 12 Handler_update 1 DROP TABLE t1, t2; #6 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) # select: SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3) # EXPLAIN UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`b` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b` < 3 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd 3 Handler_read_rnd_next 9 Handler_update 3 DROP TABLE t1, t2; #7 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) # select: SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3) # EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 33.33 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`b` = `test`.`t1`.`a` and `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 7 Handler_update 2 DROP TABLE t1, t2; #7a CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3), (1000); CREATE TABLE t3 like t2; insert into t3 select * from t2; insert into t3 select seq from seq_1001_to_2000; # # query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3) # select: SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3) # EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 1004 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 1004 100.00 Using where Warnings: Note 1003 update `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` set `test`.`t1`.`a` = 10 where `test`.`t3`.`b` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t3 WHERE t3.b < 3); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY eq_ref distinct_key distinct_key 4 func 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 1004 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t3`) join `test`.`t2` where `test`.`t3`.`b` < 3 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 9 Handler_read_rnd_next 1014 # Status of testing query execution: Variable_name Value Handler_read_key 9 Handler_read_rnd_next 1019 Handler_update 2 DROP TABLE t1, t2, t3; #8 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10 # select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 # EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 1 PRIMARY ALL NULL NULL NULL NULL 3 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = t11.a + 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd 3 Handler_read_rnd_deleted 1 Handler_read_rnd_next 24 Handler_update 3 DROP TABLE t1, t2; #9 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10 # select: SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12 # EXPLAIN UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 1 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 t11, (SELECT 1 FROM DUAL) t12 SET t11.a = t11.a + 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY system NULL NULL NULL NULL 1 100.00 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ update `test`.`t1` `t11` set `test`.`t11`.`a` = `test`.`t11`.`a` + 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT 1 FROM DUAL) t12; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY system NULL NULL NULL NULL 1 100.00 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t11`.`a` AS `a`,1 AS `1` from `test`.`t1` `t11` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 Handler_read_rnd_next 1 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd 3 Handler_read_rnd_next 9 Handler_update 3 DROP TABLE t1, t2; #10 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1 # select: SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1 # EXPLAIN UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY ALL NULL NULL NULL NULL 3 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 t11, (SELECT * FROM t2) t12 SET t11.a = 10 WHERE t11.a > 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY ALL NULL NULL NULL NULL 3 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 /* select#1 */ update `test`.`t1` `t11` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` set `test`.`t11`.`a` = 10 where `test`.`t11`.`a` > 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 t11, (SELECT * FROM t2) t12 WHERE t11.a > 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 3 100.00 Using where 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` `t11` join `test`.`t2` where `test`.`t11`.`a` > 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 8 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 16 Handler_update 2 DROP TABLE t1, t2; #11 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: DELETE FROM t1 WHERE a > 1 LIMIT 1 # select: SELECT * FROM t1 WHERE a > 1 LIMIT 1 # EXPLAIN DELETE FROM t1 WHERE a > 1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a > 1 LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` > 1 limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 1 LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 1 limit 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 2 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 2 Handler_read_rnd_next 2 DROP TABLE t1; #12 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: DELETE FROM t1 WHERE 0 # select: SELECT * FROM t1 WHERE 0 # EXPLAIN DELETE FROM t1 WHERE 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 delete from `test`.`t1` using dual where 0 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 # Status of testing query execution: Variable_name Value Handler_read_key 2 DROP TABLE t1; #13 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); # # query: DELETE FROM t1 USING t1 WHERE 0 # select: SELECT * FROM t1 WHERE 0 # EXPLAIN DELETE FROM t1 USING t1 WHERE 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 delete from `test`.`t1` using `test`.`t1` where 0 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 # Status of testing query execution: Variable_name Value Handler_read_key 2 DROP TABLE t1; #14 CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); INSERT INTO t1 VALUES (3, 3), (7, 7); # # query: DELETE FROM t1 WHERE a = 3 # select: SELECT * FROM t1 WHERE a = 3 # EXPLAIN DELETE FROM t1 WHERE a = 3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a = 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` = 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 5 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 const a a 5 const 1 100.00 Warnings: Note 1003 select 3 AS `a`,3 AS `b` from `test`.`t1` where 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 6 DROP TABLE t1; #15 CREATE TABLE t1 (a INT, b INT, UNIQUE KEY (a), KEY (b)); INSERT INTO t1 VALUES (3, 3), (7, 7); # # query: DELETE FROM t1 WHERE a < 3 # select: SELECT * FROM t1 WHERE a < 3 # EXPLAIN DELETE FROM t1 WHERE a < 3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 1 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a < 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 1 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 5 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 1 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` < 3 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 5 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 # Status of testing query execution: Variable_name Value Handler_read_key 6 DROP TABLE t1; #16 CREATE TABLE t1 ( a int PRIMARY KEY ); # # query: DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a # select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a # EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1003 select NULL AS `a` from `test`.`t1` where 0 order by NULL # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 Handler_read_rnd_next 1 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 3 Handler_read_rnd_next 1 # Status of testing query execution: Variable_name Value Handler_read_key 3 INSERT INTO t1 VALUES (1), (2), (3), (-1), (-2), (-3); # # query: DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a # select: SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a # EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 0 order by `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_next 3 # Status of testing query execution: Variable_name Value Handler_delete 3 Handler_read_key 4 Handler_read_next 3 DROP TABLE t1; #17 CREATE TABLE t1(a INT PRIMARY KEY); INSERT INTO t1 VALUES (4),(3),(1),(2); # # query: DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 # select: SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1 # EXPLAIN DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where @a:=`test`.`t1`.`a` order by `test`.`t1`.`a` limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where @a:=`test`.`t1`.`a` order by `test`.`t1`.`a` limit 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 3 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_first 1 Handler_read_key 3 DROP TABLE t1; #18 CREATE TABLE t1 (a DATE, b TIME, c INT, KEY c(c), KEY b(b), KEY a(a)); INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (); UPDATE t1 SET a = c, b = c; # # query: DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1 # select: SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1 # EXPLAIN DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 ORDER BY a ASC, b ASC LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort Warnings: Note 1003 delete from `test`.`t1` using dual order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 7 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 11 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 7 Handler_read_rnd 1 Handler_read_rnd_next 11 Sort_rows 10 Sort_scan 1 DROP TABLE t1; #19 CREATE TABLE t1 (a1 INT NOT NULL, b1 INT NOT NULL); CREATE TABLE t2 (a2 INT NOT NULL, b2 INT NOT NULL, PRIMARY KEY (a2,b2)); CREATE TABLE t3 (a3 INT NOT NULL, b3 INT NOT NULL, PRIMARY KEY (a3,b3)); INSERT INTO t1 VALUES (1,1), (2,1), (1,3); INSERT INTO t2 VALUES (1,1), (2,2), (3,3); INSERT INTO t3 VALUES (1,1), (2,1), (1,3); # # query: DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 # select: SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3 # EXPLAIN DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE t1,t2,t3 FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 Warnings: Note 1003 delete from `test`.`t1`,`test`.`t2`,`test`.`t3` using `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a2` = `test`.`t1`.`a1` and `test`.`t3`.`a3` = `test`.`t2`.`b2` and `test`.`t3`.`b3` = `test`.`t1`.`b1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 13 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a1 1 100.00 Using index 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 8 test.t2.b2,test.t1.b1 1 100.00 Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2`,`test`.`t3`.`a3` AS `a3`,`test`.`t3`.`b3` AS `b3` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t2`.`a2` = `test`.`t1`.`a1` and `test`.`t3`.`a3` = `test`.`t2`.`b2` and `test`.`t3`.`b3` = `test`.`t1`.`b1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 13 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 19 Handler_read_next 3 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_delete 8 Handler_read_key 19 Handler_read_next 3 Handler_read_rnd 5 Handler_read_rnd_next 4 DROP TABLE t1, t2, t3; #20 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (a INT); INSERT INTO t2 VALUES (1), (2), (3), (1000); # # query: UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2) # select: SELECT * FROM t1 WHERE a IN (SELECT a FROM t2) # EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t1) Warnings: Note 1003 update `test`.`t1` semi join (`test`.`t2`) set `test`.`t1`.`a` = 10 where `test`.`t2`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 25.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 10 Handler_update 3 DROP TABLE t1, t2; #21 CREATE TABLE t1 (a1 INT); INSERT INTO t1 VALUES (1), (2), (3), (4), (5); CREATE TABLE t2 (a2 VARCHAR(10)); INSERT INTO t2 VALUES (1), (2), (3), (4), (5); SET @save_optimizer_switch= @@optimizer_switch; # # query: DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) # select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) # EXPLAIN DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 /* select#1 */ delete from `test`.`t1` using dual where (`test`.`t1`.`a1`,(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where `test`.`t2`.`a2` > 2 and (`test`.`t1`.`a1`) = `test`.`t2`.`a2`)) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a1` AS `a1` from `test`.`t1` where <`test`.`t1`.`a1`>((`test`.`t1`.`a1`,(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where `test`.`t2`.`a2` > 2 and (`test`.`t1`.`a1`) = `test`.`t2`.`a2`))) # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 9 Handler_read_rnd_next 30 # Status of testing query execution: Variable_name Value Handler_delete 3 Handler_read_key 4 Handler_read_rnd_next 30 SET @@optimizer_switch= @save_optimizer_switch; TRUNCATE t1; INSERT INTO t1 VALUES (1), (2), (3), (4), (5); # # query: DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) # select: SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2) # EXPLAIN DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; FirstMatch(t1) FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 20.00 Using where; FirstMatch(t1) Warnings: Note 1003 delete from `test`.`t1` using (`test`.`t2`) where `test`.`t2`.`a2` > 2 and `test`.`t1`.`a1` = `test`.`t2`.`a2` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 20.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`a2` > 2 and `test`.`t1`.`a1` = `test`.`t2`.`a2` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 12 # Status of testing query execution: Variable_name Value Handler_delete 3 Handler_read_key 4 Handler_read_rnd_next 30 DROP TABLE t1, t2; #22 CREATE TABLE t1 (i INT, j INT); INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); # # query: UPDATE t1 SET i = 10 # select: SELECT * FROM t1 # EXPLAIN UPDATE t1 SET i = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET i = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`i` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 3 Handler_read_rnd_next 6 # Status of testing query execution: Variable_name Value Handler_read_key 3 Handler_read_rnd_next 6 Handler_update 5 DROP TABLE t1; #23 CREATE TABLE t1 (i INT, j INT); INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); # # query: DELETE FROM t1 # select: SELECT * FROM t1 # EXPLAIN DELETE FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL 5 Deleting all rows FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL 5 NULL Deleting all rows Warnings: Note 1003 delete from `test`.`t1` using dual # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 3 Handler_read_rnd_next 6 # Status of testing query execution: Variable_name Value Handler_read_key 3 DROP TABLE t1; #24 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; # # query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 15 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 DROP TABLE t1, t2; #25 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (i INT); # # query: INSERT INTO t2 SELECT * FROM t1 # select: SELECT * FROM t1 # EXPLAIN INSERT INTO t2 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO t2 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 insert into `test`.`t2` select `test`.`t1`.`i` AS `i` from `test`.`t1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 4 Handler_write 3 DROP TABLE t1, t2; #26 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (i INT); # # query: REPLACE INTO t2 SELECT * FROM t1 # select: SELECT * FROM t1 # EXPLAIN REPLACE INTO t2 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED REPLACE INTO t2 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 replace into `test`.`t2` select `test`.`t1`.`i` AS `i` from `test`.`t1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 4 Handler_write 3 DROP TABLE t1, t2; #27 CREATE TABLE t1 (i INT); # # query: INSERT INTO t1 SET i = 10 # select: # EXPLAIN INSERT INTO t1 SET i = 10; id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO t1 SET i = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL Warnings: Note 1003 insert into `test`.`t1`(i) values (10) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_write 1 DROP TABLE t1; #28 CREATE TABLE t1 (i INT); # # query: REPLACE INTO t1 SET i = 10 # select: # EXPLAIN REPLACE INTO t1 SET i = 10; id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED REPLACE INTO t1 SET i = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL Warnings: Note 1003 replace into `test`.`t1`(i) values (10) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_write 1 DROP TABLE t1; #29 CREATE TABLE t1 (a INT, i INT PRIMARY KEY); INSERT INTO t1 (i) VALUES (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); # # query: DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # EXPLAIN DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 5 Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 5 Handler_read_next 4 DROP TABLE t1; #30 CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); INSERT INTO t1 (i) VALUES (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); # # query: DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # select: SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # EXPLAIN DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` <= "18" of type `int` Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` > 10 and `test`.`t1`.`i` <= 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 4 Handler_read_rnd 5 Handler_read_rnd_next 27 Sort_rows 8 Sort_scan 1 DROP TABLE t1; #30a # # MDEV-32957 Unusable key notes report wrong predicates for > and >= # CREATE TABLE t1(a INT, i CHAR(2), INDEX(i(1))); INSERT INTO t1 (i) VALUES (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); # # query: DELETE FROM t1 WHERE i >= 10 AND i < 18 ORDER BY i LIMIT 5 # select: SELECT * FROM t1 WHERE i >= 10 AND i < 18 ORDER BY i LIMIT 5 # EXPLAIN DELETE FROM t1 WHERE i >= 10 AND i < 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE i >= 10 AND i < 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`i` >= 10 and `test`.`t1`.`i` < 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE i >= 10 AND i < 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` >= "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t1`.`i` of type `char` < "18" of type `int` Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where `test`.`t1`.`i` >= 10 and `test`.`t1`.`i` < 18 order by `test`.`t1`.`i` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 4 Handler_read_rnd 5 Handler_read_rnd_next 27 Sort_rows 8 Sort_scan 1 DROP TABLE t1; #31 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; # # query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 15 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 26 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_first 1 Handler_read_key 8 Handler_read_next 26 DROP TABLE t1, t2; #32 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; # # query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 15 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 DROP TABLE t1, t2; #33 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); INSERT INTO t2 SELECT i, i, i, i FROM t1; # # query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_rows 1 Sort_scan 1 DROP TABLE t1, t2; #34 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; INSERT INTO t2 SELECT i, i, i, i FROM t1; # # query: DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 1 Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_rows 1 Sort_scan 1 DROP TABLE t1, t2; #35 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; # # query: DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 # select: SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 # EXPLAIN DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 Using sort_union(key1,key2); Using where; Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort Warnings: Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort Warnings: Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_next 7 Handler_read_rnd 4 Sort_range 1 Sort_rows 4 # Status of testing query execution: Variable_name Value Handler_delete 4 Handler_read_key 8 Handler_read_next 7 Handler_read_rnd 8 Sort_range 1 Sort_rows 4 DROP TABLE t1, t2; #36 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; # # query: DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 # select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 # EXPLAIN DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where Warnings: Note 1003 delete from `test`.`t2` using dual where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 5 Handler_read_prev 4 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 5 Handler_read_prev 4 DROP TABLE t1, t2; #37 CREATE TABLE t1 (i INT); INSERT INTO t1 (i) VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 SELECT i, i, i FROM t1; # # query: DELETE FROM t2 ORDER BY a, b DESC LIMIT 5 # select: SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 # EXPLAIN DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort Warnings: Note 1003 delete from `test`.`t2` using dual order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 6 Handler_read_rnd 5 Handler_read_rnd_next 27 Sort_rows 26 Sort_scan 1 DROP TABLE t1, t2; #38 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); INSERT INTO t2 (a, b) SELECT i, i FROM t1; INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; # # query: DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5 # select: SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 # EXPLAIN DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 6 NULL 5 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Warnings: Note 1003 delete from `test`.`t2` using dual order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 Handler_read_last 1 Handler_read_prev 4 # Status of testing query execution: Variable_name Value Handler_delete 5 Handler_read_key 6 Handler_read_last 1 Handler_read_prev 4 DROP TABLE t1, t2; #39 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; # # query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 5 Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 5 Handler_read_next 4 Handler_read_rnd 5 Handler_update 5 DROP TABLE t1, t2; #40 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, i CHAR(2), INDEX(i(1))); INSERT INTO t2 (i) SELECT i FROM t1; # # query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5 # EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` Note 1003 update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` > "10" of type `int` Note 1105 Cannot use key `i` part[0] for lookup: `test`.`t2`.`i` of type `char` <= "18" of type `int` Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd 5 Handler_read_rnd_next 27 Handler_update 5 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 DROP TABLE t1, t2; #41 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; # # query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 15 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 26 # Status of testing query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 26 Handler_read_rnd 1 Handler_update 1 DROP TABLE t1, t2; #42 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, b INT, c INT, d INT, INDEX(a, b, c)); INSERT INTO t2 (a, b, c) SELECT i, i, i FROM t1; INSERT INTO t2 (a, b, c) SELECT t1.i, t1.i, t1.i FROM t1, t1 x1, t1 x2; # # query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 15 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 15 NULL 5 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 # Status of testing query execution: Variable_name Value Handler_read_first 1 Handler_read_key 8 Handler_read_next 4 Handler_read_rnd 5 Handler_update 5 DROP TABLE t1, t2; #43 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b(1),c)); INSERT INTO t2 SELECT i, i, i, i FROM t1; # # query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 DROP TABLE t1, t2; #44 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), INDEX (a,b,c)) ENGINE=HEAP; INSERT INTO t2 SELECT i, i, i, i FROM t1; # # query: UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5 # select: SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5 # EXPLAIN UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 update `test`.`t2` set `test`.`t2`.`d` = 10 where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 8 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort Warnings: Note 1105 Cannot use key `a` part[1] for lookup: `test`.`t2`.`b` of type `char` = "10" of type `int` Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where `test`.`t2`.`b` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 8 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 8 Handler_read_rnd 1 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 1 Sort_scan 1 DROP TABLE t1, t2; #45 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (i INT, key1 INT, key2 INT, INDEX (key1), INDEX (key2)); INSERT INTO t2 (key1, key2) SELECT i, i FROM t1; # # query: UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1 # select: SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1 # EXPLAIN UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 Using sort_union(key1,key2); Using where; Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET i = 123 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`i` = 123 where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort Warnings: Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where `test`.`t2`.`key1` < 13 or `test`.`t2`.`key2` < 14 order by `test`.`t2`.`key1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 8 Handler_read_next 7 Handler_read_rnd 4 Sort_range 1 Sort_rows 4 # Status of testing query execution: Variable_name Value Handler_read_key 8 Handler_read_next 7 Handler_read_rnd 8 Handler_update 4 Sort_range 1 Sort_rows 4 DROP TABLE t1, t2; #46 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2(a INT, i INT PRIMARY KEY); INSERT INTO t2 (i) SELECT i FROM t1; # # query: UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 # select: SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5 # EXPLAIN UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 5 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`a` = 10 where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using where Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where `test`.`t2`.`i` > 10 and `test`.`t2`.`i` <= 18 order by `test`.`t2`.`i` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 5 Handler_read_prev 4 # Status of testing query execution: Variable_name Value Handler_read_key 5 Handler_read_prev 4 Handler_read_rnd 5 Handler_update 5 DROP TABLE t1, t2; #47 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), INDEX (a, b)); INSERT INTO t2 SELECT i, i, i FROM t1; # # query: UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5 # select: SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5 # EXPLAIN UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`c` = 10 order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 Handler_read_rnd_next 27 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 6 Handler_read_rnd 5 Handler_read_rnd_next 27 Handler_update 4 Sort_priority_queue_sorts 1 Sort_rows 5 Sort_scan 1 DROP TABLE t1, t2; #48 CREATE TABLE t1 (i INT); INSERT INTO t1 VALUES (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); CREATE TABLE t2 (a CHAR(2), b CHAR(2), c INT, INDEX (a, b)); INSERT INTO t2 (a, b) SELECT i, i FROM t1; INSERT INTO t2 (a, b) SELECT t1.i, t1.i FROM t1, t1 x1, t1 x2; # # query: UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5 # select: SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5 # EXPLAIN UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index NULL a 6 NULL 5 Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t2 SET c = 10 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Using buffer Warnings: Note 1003 update `test`.`t2` set `test`.`t2`.`c` = 10 order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 index NULL a 6 NULL 5 100.00 Warnings: Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a` desc,`test`.`t2`.`b` desc limit 5 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 6 Handler_read_last 1 Handler_read_prev 4 # Status of testing query execution: Variable_name Value Handler_read_key 6 Handler_read_last 1 Handler_read_prev 4 Handler_read_rnd 5 Handler_update 5 DROP TABLE t1, t2; #49 CREATE TABLE t1 ( pk INT NOT NULL AUTO_INCREMENT, c1_idx CHAR(1) DEFAULT 'y', c2 INT, PRIMARY KEY (pk), INDEX c1_idx (c1_idx) ); INSERT INTO t1 VALUES (1,'y',1), (2,'n',2), (3,'y',3), (4,'n',4); # # query: UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 # select: SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 # EXPLAIN UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 Using where; Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`c2` = 0 where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_next 2 Sort_priority_queue_sorts 1 Sort_range 1 Sort_rows 2 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_next 2 Handler_read_rnd 2 Handler_update 2 Sort_priority_queue_sorts 1 Sort_range 1 Sort_rows 2 # # query: DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 # select: SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2 # EXPLAIN DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 Using where; Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range c1_idx c1_idx 2 NULL 2 100.00 Using where; Using filesort Warnings: Note 1003 delete from `test`.`t1` using dual where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where; Using filesort Warnings: Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1_idx` AS `c1_idx`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where `test`.`t1`.`c1_idx` = 'y' order by `test`.`t1`.`pk` desc limit 2 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_next 2 Sort_priority_queue_sorts 1 Sort_range 1 Sort_rows 2 # Status of testing query execution: Variable_name Value Handler_delete 2 Handler_read_key 7 Handler_read_next 2 Handler_read_rnd 2 Sort_range 1 Sort_rows 2 DROP TABLE t1; #50 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY); INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); # # query: UPDATE t1 SET a=a+10 WHERE a > 34 # select: SELECT * FROM t1 WHERE a > 34 # EXPLAIN UPDATE t1 SET a=a+10 WHERE a > 34; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a=a+10 WHERE a > 34; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 10 where `test`.`t1`.`a` > 34 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 34; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 34 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Handler_read_next 2 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_next 2 Handler_read_rnd 2 Handler_update 2 DROP TABLE t1; #51 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); CREATE TABLE t2 (c1 INT, c2 INT); INSERT INTO t1 VALUES (1, 1, 10), (2, 2, 20); # # query: UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 # select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 # EXPLAIN UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 7 Handler_read_rnd_next 1 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 4 # # query: UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10 # select: SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10 # EXPLAIN UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c1 SET t2.c2 = 10 WHERE t1.c3 = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c3 = 10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,NULL AS `c1`,NULL AS `c2` from `test`.`t1` where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 7 Handler_read_rnd_next 1 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 4 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 4 DROP TABLE t1, t2; #52 CREATE TABLE t1(f1 INT, f2 INT); CREATE TABLE t2(f3 INT, f4 INT); CREATE INDEX IDX ON t2(f3); INSERT INTO t1 VALUES(1,0),(2,0); INSERT INTO t2 VALUES(1,1),(2,2); # # query: UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) # select: SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1 # EXPLAIN UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t2 ref IDX IDX 5 test.t1.f1 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t2 ref IDX IDX 5 test.t1.f1 1 100.00 Warnings: Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ update `test`.`t1` set `test`.`t1`.`f2` = (/* select#2 */ select max(`test`.`t2`.`f4`) from `test`.`t2` where `test`.`t2`.`f3` = `test`.`t1`.`f1`) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t2 ref IDX IDX 5 test.t1.f1 1 100.00 Warnings: Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 Note 1003 /* select#1 */ select <`test`.`t1`.`f1`>((/* select#2 */ select max(`test`.`t2`.`f4`) from `test`.`t2` where `test`.`t2`.`f3` = `test`.`t1`.`f1`)) AS `(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1)` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 7 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 11 Handler_read_next 2 Handler_read_rnd_next 3 # Status of testing query execution: Variable_name Value Handler_read_key 9 Handler_read_next 2 Handler_read_rnd_next 3 Handler_update 2 DROP TABLE t1, t2; #55 CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1); SET @a = NULL; EXPLAIN DELETE FROM t1 WHERE (@a:= a); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using where DROP TABLE t1; #56 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); DELETE FROM t1 USING t1 WHERE uknown_column = 12345; ERROR 42S22: Unknown column 'uknown_column' in 'where clause' EXPLAIN EXTENDED DELETE FROM t1 USING t1 WHERE uknown_column = 12345; ERROR 42S22: Unknown column 'uknown_column' in 'where clause' DROP TABLE t1; #57 CREATE TABLE t1(f1 INT); EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDER BY f2; ERROR 42S22: Unknown column 'f2' in 'field list' UPDATE t1 SET f2=1 ORDER BY f2; ERROR 42S22: Unknown column 'f2' in 'field list' DROP TABLE t1; #62 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0), (1); CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12; # # query: UPDATE v1 SET a = 1 WHERE a > 0 # select: SELECT * FROM v1 WHERE a > 0 # EXPLAIN UPDATE v1 SET a = 1 WHERE a > 0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE v1 SET a = 1 WHERE a > 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 update `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` > 0 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 0; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` `t11` join `test`.`t1` `t12` where `test`.`t11`.`a` > 0 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 6 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd 1 Handler_read_rnd_deleted 1 Handler_read_rnd_next 8 # # query: UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a # select: SELECT * FROM t1, v1 WHERE t1.a = v1.a # EXPLAIN UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Warnings: Note 1003 update `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` set `test`.`t11`.`a` = 1 where `test`.`t11`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, v1 WHERE t1.a = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (incremental, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t11`.`a` AS `a`,`test`.`t12`.`a` AS `b` from `test`.`t1` join `test`.`t1` `t11` join `test`.`t1` `t12` where `test`.`t11`.`a` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 9 # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd 2 Handler_read_rnd_deleted 1 Handler_read_rnd_next 18 Handler_update 1 DROP TABLE t1; DROP VIEW v1; #63 CREATE TABLE t1 (a INT, PRIMARY KEY(a)); INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9); CREATE VIEW v1 (a) AS SELECT a FROM t1; # # query: DELETE FROM v1 WHERE a < 4 # select: SELECT * FROM v1 WHERE a < 4 # EXPLAIN DELETE FROM v1 WHERE a < 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE FROM v1 WHERE a < 4; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where Warnings: Note 1003 /* select#1 */ delete from `test`.`t1` using dual where `v1`.`a` < 4 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` < 4 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_first 1 Handler_read_key 3 Handler_read_next 3 # Status of testing query execution: Variable_name Value Handler_delete 3 Handler_read_first 1 Handler_read_key 3 Handler_read_next 3 DROP TABLE t1; DROP VIEW v1; #64 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); CREATE TABLE t2 (x INT); INSERT INTO t2 VALUES (1), (2), (3), (4); CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; # # query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a # select: SELECT * FROM t2, v1 WHERE t2.x = v1.a # EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 Warnings: Note 1003 delete from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 Warnings: Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` + 1 AS `c` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 10 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value Handler_delete 4 Handler_read_key 10 Handler_read_rnd 4 Handler_read_rnd_next 5 DROP TABLE t1,t2; DROP VIEW v1; #65 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a)); INSERT INTO t1 VALUES (1,2), (2,3), (3,4), (4,5), (5,10); CREATE TABLE t2 (x INT); INSERT INTO t2 VALUES (1), (2), (3), (4); CREATE VIEW v1 (a,c) AS SELECT a, b+1 FROM t1; # # query: DELETE v1 FROM t2, v1 WHERE t2.x = v1.a # select: SELECT * FROM t2, v1 WHERE t2.x = v1.a # EXPLAIN DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED DELETE v1 FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 Warnings: Note 1003 delete from `test`.`t1` using `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 6 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where 1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00 Warnings: Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` + 1 AS `c` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`x` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 6 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 10 Handler_read_rnd_next 5 # Status of testing query execution: Variable_name Value Handler_delete 4 Handler_read_key 10 Handler_read_rnd 4 Handler_read_rnd_next 5 DROP TABLE t1,t2; DROP VIEW v1; #66 CREATE TABLE t1 (a INT); CREATE VIEW v1 (x) AS SELECT a FROM t1; # # query: INSERT INTO v1 VALUES (10) # select: SELECT NULL # EXPLAIN INSERT INTO v1 VALUES (10); id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO v1 VALUES (10); id select_type table type possible_keys key key_len ref rows filtered Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL Warnings: Note 1003 insert into `test`.`t1`(x) values (10) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 2 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT NULL; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select NULL AS `NULL` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value # Status of "equivalent" SELECT query execution: Variable_name Value # Status of testing query execution: Variable_name Value Handler_read_key 2 Handler_write 1 DROP TABLE t1; DROP VIEW v1; #67 CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); CREATE VIEW v1 (x) AS SELECT b FROM t2; # # query: INSERT INTO v1 SELECT * FROM t1 # select: SELECT * FROM t1 # EXPLAIN INSERT INTO v1 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO v1 SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found Warnings: Note 1003 insert into `test`.`t2`(x) /* select#1 */ select NULL AS `a` from `test`.`t1` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 Handler_read_rnd_next 1 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 0.00 Const row not found Warnings: Note 1003 select NULL AS `a` from `test`.`t1` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 2 Handler_read_rnd_next 1 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 2 Handler_read_rnd_next 1 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd_next 1 DROP TABLE t1, t2; DROP VIEW v1; #68 CREATE TABLE t1 (i INT); EXPLAIN INSERT DELAYED INTO t1 VALUES (1); id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL DROP TABLE t1; #69 CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1), (2), (3); CREATE TABLE t2 (b INT); INSERT INTO t2 VALUES (1), (2), (3); # # query: UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # select: SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # EXPLAIN UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 /* select#1 */ update (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Handler_update 1 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # # query: UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # select: SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 3 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 /* select#1 */ update (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join `test`.`t2` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from (/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # # query: UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # select: SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x) # EXPLAIN UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 1 PRIMARY ALL NULL NULL NULL NULL 3 4 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1, (SELECT * FROM t2) y SET a = 10 WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 1 PRIMARY ALL NULL NULL NULL NULL 3 100.00 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Warnings: Note 1003 /* select#1 */ update (/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join (/* select#2 */ select `test`.`t2`.`b` AS `b` from `test`.`t2`) `y` set `test`.`t1`.`a` = 10 where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT * FROM t1, (SELECT * FROM t2) y WHERE a IN (SELECT * FROM (SELECT b FROM t2 ORDER BY b LIMIT 2,2) x); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1 PRIMARY eq_ref distinct_key distinct_key 5 test.t1.a 1 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) 4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from (/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x` join `test`.`t1` join `test`.`t2` where `x`.`b` = `test`.`t1`.`a` # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 4 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd_next 8 Sort_priority_queue_sorts 1 Sort_rows 3 Sort_scan 1 DROP TABLE t1,t2; #70 CREATE TABLE t1 (c1 INT KEY); CREATE TABLE t2 (c2 INT); CREATE TABLE t3 (c3 INT); EXPLAIN EXTENDED UPDATE t3 SET c3 = ( SELECT COUNT(d1.c1) FROM ( SELECT a11.c1 FROM t1 AS a11 STRAIGHT_JOIN t2 AS a21 ON a21.c2 = a11.c1 JOIN t1 AS a12 ON a12.c1 = a11.c1 ) d1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 /* select#1 */ update `test`.`t3` set `test`.`t3`.`c3` = (/* select#2 */ select count(NULL) from `test`.`t1` `a11` straight_join `test`.`t2` `a21` join `test`.`t1` `a12` where 0) DROP TABLE t1, t2, t3; #73 CREATE TABLE t1 (id INT); CREATE TABLE t2 (id INT); INSERT INTO t1 VALUES (1), (2); EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 Const row not found; Using temporary; Using filesort 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 DROP TABLE t1,t2; #74 CREATE TABLE t1(a INT PRIMARY KEY); INSERT INTO t1 VALUES (1), (2), (3), (4), (5); # used key is modified & Using temporary # # query: UPDATE t1 SET a=a+1 WHERE a>10 # select: SELECT a t1 FROM t1 WHERE a>10 # EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using buffer FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using buffer Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 1 where `test`.`t1`.`a` > 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where `test`.`t1`.`a` > 10 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 # Status of testing query execution: Variable_name Value Handler_read_key 4 # used key is modified & Using filesort # # query: UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20 # select: SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20 # EXPLAIN UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED UPDATE t1 SET a=a+1 WHERE a>10 ORDER BY a+20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using filesort Warnings: Note 1003 update `test`.`t1` set `test`.`t1`.`a` = `test`.`t1`.`a` + 1 where `test`.`t1`.`a` > 10 order by `test`.`t1`.`a` + 20 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 3 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED SELECT a t1 FROM t1 WHERE a>10 ORDER BY a+20; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where; Using index; Using filesort Warnings: Note 1003 select `test`.`t1`.`a` AS `t1` from `test`.`t1` where `test`.`t1`.`a` > 10 order by `test`.`t1`.`a` + 20 # Status of EXPLAIN EXTENDED "equivalent" SELECT query execution Variable_name Value Handler_read_key 3 # Status of "equivalent" SELECT query execution: Variable_name Value Handler_read_key 4 Sort_range 1 # Status of testing query execution: Variable_name Value Handler_read_key 4 Sort_range 1 DROP TABLE t1; #75 CREATE TABLE t1 (id INT PRIMARY KEY, i INT); # # query: INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); # select: # EXPLAIN INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO t1 VALUES (3,10), (7,11), (3,11) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; id select_type table type possible_keys key key_len ref rows filtered Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL Warnings: Note 1003 insert into `test`.`t1` values (3,10),(7,11),(3,11) on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 4 # Status of testing query execution: Variable_name Value Handler_read_key 4 Handler_read_rnd 1 Handler_write 3 DROP TABLE t1; #76 CREATE TABLE t1 (id INT PRIMARY KEY, i INT); CREATE TABLE t2 (a INT, b INT); INSERT INTO t2 VALUES (1,10), (3,10), (7,11), (3,11); # # query: INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); # select: # EXPLAIN INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 FLUSH STATUS; FLUSH TABLES; EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t2 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Warnings: Note 1003 insert into `test`.`t1` select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` on duplicate key update `test`.`t1`.`id` = last_insert_id(`test`.`t1`.`id`) # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 # Status of testing query execution: Variable_name Value Handler_read_key 7 Handler_read_rnd 1 Handler_read_rnd_next 5 Handler_write 4 DROP TABLE t1,t2; # # Bug #12949629: CLIENT LOSES CONNECTION AFTER EXECUTING A PROCEDURE WITH # EXPLAIN UPDATE/DEL/INS # CREATE TABLE t1 (i INT); CREATE TABLE t2 (i INT); CREATE PROCEDURE p1() BEGIN EXPLAIN INSERT INTO t1 VALUES (1);END| CREATE PROCEDURE p2() BEGIN INSERT INTO t1 VALUES (1);END| CREATE PROCEDURE p3() BEGIN EXPLAIN INSERT INTO t1 SELECT 1;END| CREATE PROCEDURE p4() BEGIN INSERT INTO t1 SELECT 1;END| CREATE PROCEDURE p5() BEGIN EXPLAIN REPLACE INTO t1 VALUES (1);END| CREATE PROCEDURE p6() BEGIN REPLACE INTO t1 VALUES (1);END| CREATE PROCEDURE p7() BEGIN EXPLAIN REPLACE INTO t1 SELECT 1;END| CREATE PROCEDURE p8() BEGIN REPLACE INTO t1 SELECT 1;END| CREATE PROCEDURE p9() BEGIN EXPLAIN UPDATE t1 SET i = 10;END| CREATE PROCEDURE p10() BEGIN UPDATE t1 SET i = 10;END| CREATE PROCEDURE p11() BEGIN EXPLAIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| CREATE PROCEDURE p12() BEGIN UPDATE t1,t2 SET t1.i = 10 WHERE t1.i = t2.i ;END| CREATE PROCEDURE p13() BEGIN EXPLAIN DELETE FROM t1;END| CREATE PROCEDURE p14() BEGIN DELETE FROM t1;END| CREATE PROCEDURE p15() BEGIN EXPLAIN DELETE FROM t1 USING t1;END| CREATE PROCEDURE p16() BEGIN DELETE FROM t1 USING t1;END| CALL p16(); DROP PROCEDURE p16; CALL p15(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found DROP PROCEDURE p15; CALL p14(); DROP PROCEDURE p14; CALL p13(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL 0 Deleting all rows DROP PROCEDURE p13; CALL p12(); DROP PROCEDURE p12; CALL p11(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables DROP PROCEDURE p11; CALL p10(); DROP PROCEDURE p10; CALL p9(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP PROCEDURE p9; CALL p8(); DROP PROCEDURE p8; CALL p7(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used DROP PROCEDURE p7; CALL p6(); DROP PROCEDURE p6; CALL p5(); id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL DROP PROCEDURE p5; CALL p4(); DROP PROCEDURE p4; CALL p3(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used DROP PROCEDURE p3; CALL p2(); DROP PROCEDURE p2; CALL p1(); id select_type table type possible_keys key key_len ref rows Extra 1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL DROP PROCEDURE p1; DROP TABLE t1, t2; # set default_storage_engine= @save_storage_engine; set optimizer_switch=default;