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