diff options
Diffstat (limited to 'mysql-test/main/myisam_explain_non_select_all.result')
| -rw-r--r-- | mysql-test/main/myisam_explain_non_select_all.result | 2929 |
1 files changed, 2929 insertions, 0 deletions
diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result new file mode 100644 index 00000000000..09e662f5d6a --- /dev/null +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -0,0 +1,2929 @@ +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +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 <derived2> 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 <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +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 t1 ALL NULL NULL NULL NULL 3 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +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 t1 ALL NULL NULL NULL NULL 3 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +Handler_read_rnd_next 1 +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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 5 +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 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +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 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 100.00 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +Handler_read_rnd_next 7 +Handler_update 2 + +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, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +# select: SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3) +# +EXPLAIN UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +FLUSH STATUS; +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where `test`.`t2`.`b` < 3 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 12 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 16 +Handler_update 2 + +DROP TABLE t1, t2; +#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 <derived2> 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 <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +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 <derived2> 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 <derived2> 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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 <derived2> 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_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 5 +# Status of testing query execution: +Variable_name Value +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 <derived2> 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 <derived2> ALL NULL NULL NULL NULL 3 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 2 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +# Status of testing query execution: +Variable_name Value + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +# Status of testing query execution: +Variable_name Value + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_key 1 + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +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 t1 range PRIMARY PRIMARY 4 NULL 1 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 1 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +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_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +INSERT INTO t1 VALUES (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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 3 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_key 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +# Status of testing query execution: +Variable_name Value +Handler_delete 1 +Handler_read_first 1 + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 6 +Handler_read_next 3 +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 8 +Handler_read_key 6 +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); +# +# 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 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where +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 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 8 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 <expr_cache><`test`.`t1`.`a1`>(<in_optimizer>(`test`.`t1`.`a1`,<exists>(/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where `test`.`t2`.`a2` > 2 and <cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))) +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 5 +Handler_read_rnd_next 30 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +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 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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 100.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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 12 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_first 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_key 1 +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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 ALL NULL NULL NULL NULL 26 Using where; Using filesort +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_rnd 1 +Handler_read_rnd_next 27 +Sort_rows 1 +Sort_scan 1 + +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_first 1 +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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 2 +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 2 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +Handler_read_key 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_last 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_delete 5 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 ALL NULL NULL NULL NULL 26 Using where; Using filesort +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_rnd 1 +Handler_read_rnd_next 27 +Handler_update 1 +Sort_priority_queue_sorts 1 +Sort_rows 1 +Sort_scan 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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 4 +# Status of testing query execution: +Variable_name Value +Handler_read_first 1 +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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 2 +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 2 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_last 1 +Handler_read_prev 4 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +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 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +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 1 +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 3 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 3 100.00 Using where; Using buffer +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 3 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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 1 +Handler_read_next 2 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 4 +# Status of testing query execution: +Variable_name Value +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 ALL IDX NULL NULL NULL 2 Using where +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 ALL IDX NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 ALL IDX NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select <expr_cache><`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 +# 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_rnd_next 9 +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 'order clause' +UPDATE t1 SET f2=1 ORDER BY f2; +ERROR 42S22: Unknown column 'f2' in 'order clause' +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +# Status of testing query execution: +Variable_name Value +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 9 +# Status of testing query execution: +Variable_name Value +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); +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 3 +# Status of testing query execution: +Variable_name Value +Handler_delete 3 +Handler_read_first 1 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# 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_delete 4 +Handler_read_key 4 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +# 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_delete 4 +Handler_read_key 4 +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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_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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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_rnd_next 1 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 1 +# Status of testing query execution: +Variable_name Value +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 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 +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 +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 5 func 2 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived3> 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 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_priority_queue_sorts 1 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +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 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 MATERIALIZED <derived3> 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 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 +3 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using filesort +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> 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 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join ((/* select#3 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_priority_queue_sorts 1 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +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 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 +3 MATERIALIZED <derived4> 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 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 +3 MATERIALIZED <derived4> 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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 +1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 3 100.00 +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 `test`.`t1` semi join ((/* select#4 */ select `test`.`t2`.`b` AS `b` from `test`.`t2` order by `test`.`t2`.`b` limit 2,2) `x`) join `test`.`t2` where 1 +# Status of EXPLAIN EXTENDED "equivalent" SELECT query execution +Variable_name Value +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +Sort_priority_queue_sorts 1 +Sort_rows 3 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 3 +Handler_read_rnd_next 10 +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 PRIMARY t3 ALL NULL NULL NULL NULL 0 100.00 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 index PRIMARY PRIMARY 4 NULL 5 20.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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_first 1 +Handler_read_next 5 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 + +# 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 +# Status of EXPLAIN EXTENDED query +Variable_name Value +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 index PRIMARY PRIMARY 4 NULL 5 20.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 +# Status of "equivalent" SELECT query execution: +Variable_name Value +Handler_read_rnd_next 6 +Sort_scan 1 +# Status of testing query execution: +Variable_name Value +Handler_read_key 1 +Sort_range 1 + +DROP TABLE t1; +# +# 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 t1 ALL NULL NULL NULL NULL 0 +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; |