mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
7e4233746e
Allows index condition pushdown for reverse ordered scans, a previously disabled feature due to poor performance. This patch adds a new API to the handler class called set_end_range which allows callers to tell the handler what the end of the index range will be when scanning. Combined with a pushed index condition, the handler can scan the index efficiently and not read beyond the end of the given range. When checking if the pushed index condition matches, the handler will also check if scanning has reached the end of the provided range and stop if so. If we instead only enabled ICP for reverse ordered scans without also calling this new API, then the handler would perform unnecessary index condition checks. In fact this would continue until the end of the index is reached. These changes are agnostic of storage engine. That is, any storage engine that supports index condition pushdown will inhereit this new behavior as it is implemented in the SQL and storage engine API layers. The partitioned tables storage meta-engine (ha_partition) adds an override of set_end_range which recursively calls set_end_range on its child storage engine (handler) implementations. This commit updates the test made in an earlier commit to show that ICP matches happen for the reverse ordered case. This patch is based on changes written by Olav Sandstaa in MySQL commit da1d92fd46071cd86de61058b6ea39fd9affcd87
3426 lines
131 KiB
Text
3426 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_deleted 1
|
|
Handler_read_rnd_next 11
|
|
|
|
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 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` 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 18 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 18 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 18 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 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` 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 18 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 18 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 18 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 5 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 5 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 5 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 5 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 5 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 5 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 10
|
|
|
|
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 10
|
|
|
|
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;
|