#At file:///mnt/sda7/work/mysql-next-mr-opt-backporting-wl4897/ based on revid:gleb.shchepa@stripped
3358 Gleb Shchepa 2011-05-25
WL4897: Add EXPLAIN INSERT/UPDATE/DELETE
Post-review modifications.
@ client/mysqltest.cc
Guilhem's patch for MTR --explain-protocol
@ mysql-test/include/explain_non_select.inc
Additional tests for WL4897.
@ mysql-test/r/case.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/r/explain.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/r/explain_non_select.result
Additional tests for WL4897.
@ mysql-test/r/func_gconcat.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/r/key.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/r/optimizer_switch.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/r/udf.result
Minor changes ("explain" column message reordering) after WL4897.
@ mysql-test/t/explain_non_select.test
Additional tests for WL4897.
@ sql/opt_explain.cc
+ The memroot_str helper class has been added to remove intermediate
buff_* and str_* member variables of Explain_* classes.
* Refactoring of explain_key_and_len and explain_extra methods of
Explain_table and Explain_join classes.
* Minor changes
@ sql/opt_explain.h
Minor changes and renamings.
@ sql/opt_range.h
Update of the Copyright.
@ sql/sql_class.h
Minor changes.
@ sql/sql_delete.cc
Common exit path for new code branches has been added.
@ sql/sql_insert.cc
Common exit path for new code branches has been added.
@ sql/sql_parse.cc
Renaming.
@ sql/sql_select.cc
Renaming.
@ sql/sql_update.cc
Common exit path for new code branches has been added.
@ sql/sql_view.cc
Permission check for views has been added.
@ sql/sql_yacc.yy
Refactoring.
modified:
client/mysqltest.cc
mysql-test/include/explain_non_select.inc
mysql-test/r/case.result
mysql-test/r/explain.result
mysql-test/r/explain_non_select.result
mysql-test/r/func_gconcat.result
mysql-test/r/key.result
mysql-test/r/optimizer_switch.result
mysql-test/r/udf.result
mysql-test/t/explain_non_select.test
sql/opt_explain.cc
sql/opt_explain.h
sql/opt_range.h
sql/sql_class.h
sql/sql_delete.cc
sql/sql_insert.cc
sql/sql_parse.cc
sql/sql_select.cc
sql/sql_update.cc
sql/sql_view.cc
sql/sql_yacc.yy
=== modified file 'client/mysqltest.cc'
--- a/client/mysqltest.cc 2011-03-22 15:40:32 +0000
+++ b/client/mysqltest.cc 2011-05-25 14:16:06 +0000
@@ -201,6 +201,7 @@ static char *opt_plugin_dir= 0;
static my_regex_t ps_re; /* the query can be run using PS protocol */
static my_regex_t sp_re; /* the query can be run as a SP */
static my_regex_t view_re; /* the query can be run as a view*/
+static my_regex_t explain_re;/* the query can be converted to EXPLAIN */
static void init_re(void);
static int match_re(my_regex_t *, char *);
@@ -6389,7 +6390,7 @@ static struct my_option my_long_options[
{"view-protocol", OPT_VIEW_PROTOCOL, "Use views for select.",
&view_protocol, &view_protocol, 0,
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"explain-protocol", OPT_EXPLAIN_PROTOCOL, "Explains all select.",
+ {"explain-protocol", OPT_EXPLAIN_PROTOCOL, "Explains all select/update/etc.",
&explain_protocol, &explain_protocol, 0,
GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
{"connect_timeout", OPT_CONNECT_TIMEOUT,
@@ -7880,7 +7881,7 @@ void run_explain(struct st_connection *c
{
if (explain_protocol_enabled &&
!command->expected_errors.count &&
- match_re(&view_re, command->query))
+ match_re(&explain_re, command->query))
{
st_command save_command= *command;
DYNAMIC_STRING query_str;
@@ -7890,6 +7891,7 @@ void run_explain(struct st_connection *c
init_dynamic_string(&query_str, "EXPLAIN EXTENDED ", 256, 256);
dynstr_append_mem(&query_str, command->query,
command->end - command->query);
+
command->query= query_str.str;
command->query_len= query_str.length;
command->end= strend(command->query);
@@ -7963,9 +7965,16 @@ void init_re(void)
"^("
"[[:space:]]*SELECT[[:space:]])";
+
+ /* Filter for queries that can be converted to EXPLAIN */
+ const char *explain_re_str =
+ "^("
+ "[[:space:]]*(SELECT|DELETE|UPDATE|INSERT|REPLACE)[[:space:]])";
+
init_re_comp(&ps_re, ps_re_str);
init_re_comp(&sp_re, sp_re_str);
init_re_comp(&view_re, view_re_str);
+ init_re_comp(&explain_re, explain_re_str);
}
@@ -8629,8 +8638,15 @@ int main(int argc, char **argv)
strmake(command->require_file, save_file, sizeof(save_file) - 1);
save_file[0]= 0;
}
- run_query(cur_con, command, flags);
+ /*
+ Gleb:
+ I run EXPLAIN _before_ the query. If query is UPDATE/DELETE is
+ matters: a DELETE may delete rows, and then EXPLAIN DELETE will
+ usually terminate quickly with "no matching rows". To make it more
+ interesting, EXPLAIN is now first.
+ */
run_explain(cur_con, command, flags);
+ run_query(cur_con, command, flags);
command_executed++;
command->last_argument= command->end;
=== modified file 'mysql-test/include/explain_non_select.inc'
--- a/mysql-test/include/explain_non_select.inc 2011-03-30 16:48:25 +0000
+++ b/mysql-test/include/explain_non_select.inc 2011-05-25 14:16:06 +0000
@@ -15,14 +15,22 @@ if (`SELECT ROW_COUNT() > 0`) {
--die Unexpected ROW_COUNT() <> 0
}
+FLUSH STATUS;
+FLUSH TABLES;
--eval EXPLAIN EXTENDED $query
if (`SELECT ROW_COUNT() > 0`) {
--echo # Erroneous query: EXPLAIN EXTENDED $query
--die Unexpected ROW_COUNT() <> 0
}
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+ Variable_name LIKE 'Handler_read_%') AND Value <> 0;
if ($select) {
+FLUSH STATUS;
+FLUSH TABLES;
--eval EXPLAIN EXTENDED $select
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+ Variable_name LIKE 'Handler_read_%') AND Value <> 0;
}
--disable_query_log
=== modified file 'mysql-test/r/case.result'
--- a/mysql-test/r/case.result 2010-08-19 11:55:35 +0000
+++ b/mysql-test/r/case.result 2011-05-25 14:16:06 +0000
@@ -64,7 +64,7 @@ fcase count(*)
3 1
explain extended select case a when 1 then 2 when 2 then 3 else 0 end as fcase, count(*) from t1 group by fcase;
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end) AS `fcase`,count(0) AS `count(*)` from `test`.`t1` group by (case `test`.`t1`.`a` when 1 then 2 when 2 then 3 else 0 end)
select case a when 1 then "one" when 2 then "two" else "nothing" end as fcase, count(*) from t1 group by fcase;
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result 2010-12-17 11:28:59 +0000
+++ b/mysql-test/r/explain.result 2011-05-25 14:16:06 +0000
@@ -115,7 +115,7 @@ EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
@@ -123,7 +123,7 @@ EXPLAIN EXTENDED SELECT 1
FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
@@ -133,7 +133,7 @@ prepare s1 from
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
@@ -143,14 +143,14 @@ prepare s1 from
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
execute s1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1`
=== modified file 'mysql-test/r/explain_non_select.result'
--- a/mysql-test/r/explain_non_select.result 2011-03-30 16:48:25 +0000
+++ b/mysql-test/r/explain_non_select.result 2011-05-25 14:16:06 +0000
@@ -1,3 +1,4 @@
+SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (3);
#
@@ -7,14 +8,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 4
@@ -33,14 +44,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 10;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 10)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 4
@@ -59,14 +80,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 1)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 4
@@ -88,16 +119,26 @@ EXPLAIN UPDATE t1, t2 SET t1.a =
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 Using join buffer (BNL, incremental buffers)
+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 Using join buffer (BNL, incremental buffers)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE t1.a = 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (BNL, incremental buffers)
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)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 8
@@ -120,11 +161,19 @@ id select_type table type possible_keys
1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
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 Using join buffer (BNL, incremental buffers)
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 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 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where
@@ -132,6 +181,10 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` = 1)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 4
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 12
@@ -154,16 +207,26 @@ EXPLAIN UPDATE t1 SET a = 10 WHER
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE 1 IN (SELECT 1 FROM t2 WHERE t2.b < 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+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 t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`b` < 3)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 8
@@ -185,12 +248,19 @@ EXPLAIN UPDATE t1 SET a = 10 WHER
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT b FROM t2 WHERE t1.a < 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
@@ -198,6 +268,9 @@ id select_type table type possible_keys
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))
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 8
@@ -220,11 +293,18 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1, t2 SET a = 10 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using join buffer (BNL, incremental buffers)
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a IN (SELECT b FROM t2 WHERE t2.b < 3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
@@ -232,6 +312,9 @@ id select_type table type possible_keys
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t2); Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) join `test`.`t2` where ((`test`.`t2`.`b` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 12
@@ -254,11 +337,19 @@ id select_type table type possible_keys
1 PRIMARY t11 ALL NULL NULL NULL NULL 3
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
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 Using join buffer (BNL, incremental buffers)
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 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 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00
@@ -266,6 +357,10 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 4
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 12
@@ -290,11 +385,20 @@ id select_type table type possible_keys
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+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
@@ -302,6 +406,11 @@ id select_type table type possible_keys
2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1003 select `test`.`t11`.`a` AS `a`,'1' AS `1` from `test`.`t1` `t11`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -328,11 +437,19 @@ id select_type table type possible_keys
1 PRIMARY t11 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using join buffer (BNL, incremental buffers)
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 Using join buffer (BNL, incremental buffers)
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 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 PRIMARY t11 ALL NULL NULL NULL NULL 3 100.00 Using where
@@ -340,6 +457,10 @@ id select_type table type possible_keys
2 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t11`.`a` AS `a`,`t12`.`b` AS `b` from `test`.`t1` `t11` join (select `test`.`t2`.`b` AS `b` from `test`.`t2`) `t12` where (`test`.`t11`.`a` > 1)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_rnd_next 4
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 12
@@ -359,14 +480,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 1 LIMIT 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 1) limit 1
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 2
@@ -385,14 +516,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
# Status of testing query execution:
@@ -408,14 +549,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 0;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
# Status of testing query execution:
@@ -431,14 +582,25 @@ INSERT INTO t1 VALUES (3, 3), (7, 7);
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,b 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,b a 5 NULL 1 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 const a a 5 const 1 100.00
Warnings:
Note 1003 select '3' AS `a`,'3' AS `b` from `test`.`t1` where 1
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_key 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -457,14 +619,24 @@ INSERT INTO t1 VALUES (3, 3), (7, 7);
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,b 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,b a 5 NULL 1 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a < 3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range a a 5 NULL 1 100.00 Using index condition
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` < 3)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -481,14 +653,26 @@ CREATE TABLE t1 ( a int PRIMARY KEY );
EXPLAIN DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
Note 1003 select NULL AS `a` from `test`.`t1` where 0 order by NULL
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -505,14 +689,24 @@ INSERT INTO t1 VALUES (1), (2), (3);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index 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`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -533,14 +727,24 @@ INSERT INTO t1 VALUES (4),(3),(1),(2);
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 ALL PRIMARY PRIMARY 4 NULL 4 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 ALL PRIMARY PRIMARY 4 NULL 4 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE (@a:= a) ORDER BY a LIMIT 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 index NULL PRIMARY 4 NULL 1 400.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -560,14 +764,24 @@ UPDATE t1 SET a = c, b = c;
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 c,b,a 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 c,b,a NULL NULL NULL 10 100.00 Using filesort
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 ORDER BY a ASC, b ASC LIMIT 1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` limit 1
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 11
@@ -597,11 +811,18 @@ id select_type table type possible_keys
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1,t2,t3 WHERE a1=a2 AND b2=a3 AND b1=b3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
@@ -609,6 +830,9 @@ id select_type table type possible_keys
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`.`t3`.`b3` = `test`.`t1`.`b1`) and (`test`.`t3`.`a3` = `test`.`t2`.`b2`) and (`test`.`t2`.`a2` = `test`.`t1`.`a1`))
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 6
@@ -635,16 +859,26 @@ EXPLAIN UPDATE t1 SET a = 10 WHERE a IN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1 SET a = 10 WHERE a IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (SELECT a FROM t2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` semi join (`test`.`t2`) where (`test`.`t2`.`a` = `test`.`t1`.`a`)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 8
@@ -668,16 +902,26 @@ EXPLAIN DELETE FROM t1 WHERE a1 IN (SE
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`a1` AS `a1` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a1`,<exists>(select 1 from `test`.`t2` where ((`test`.`t2`.`a2` > 2) and (<cache>(`test`.`t1`.`a1`) = `test`.`t2`.`a2`))))
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 30
@@ -697,16 +941,26 @@ EXPLAIN DELETE FROM t1 WHERE a1 IN (SE
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a1 IN (SELECT a2 FROM t2 WHERE a2 > 2);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (BNL, incremental buffers)
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`))
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 12
@@ -725,14 +979,24 @@ INSERT INTO t1 VALUES (1, 1), (2, 2), (3
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 6
@@ -742,7 +1006,7 @@ Handler_read_rnd_next 6
Handler_update 5
DROP TABLE t1;
-CREATE TABLE t1 (i INT, j INT);
+CREATE TABLE t1 (i INT, j INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
#
# query: DELETE FROM t1
@@ -750,15 +1014,25 @@ INSERT INTO t1 VALUES (1, 1), (2, 2), (3
#
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 Using delete_all_rows
+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 Using delete_all_rows
+1 SIMPLE NULL NULL NULL NULL NULL NULL 5 NULL Deleting all rows
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 6
@@ -766,6 +1040,46 @@ Handler_read_rnd_next 6
Variable_name Value
DROP TABLE t1;
+CREATE TABLE t1 (i INT, j INT) ENGINE=InnoDB;
+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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00
+Warnings:
+Note 1003 select `test`.`t1`.`i` AS `i`,`test`.`t1`.`j` AS `j` from `test`.`t1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_first 1
+Handler_read_key 2
+Handler_read_rnd_next 6
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 5
+Handler_read_first 1
+Handler_read_key 2
+Handler_read_rnd_next 6
+
+DROP TABLE t1;
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),
@@ -780,14 +1094,24 @@ INSERT INTO t2 (a, b, c) SELECT t1.i, t1
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 a a 15 NULL 17602 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 ALL a a 15 NULL 17602 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index NULL a 15 NULL 5 352040.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -809,14 +1133,24 @@ CREATE TABLE t2 (i INT);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 4
@@ -836,14 +1170,24 @@ CREATE TABLE t2 (i INT);
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00
Warnings:
Note 1003 select `test`.`t1`.`i` AS `i` from `test`.`t1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 4
@@ -861,9 +1205,14 @@ CREATE TABLE t1 (i INT);
EXPLAIN INSERT INTO t1 SET i = 10;
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
+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 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of testing query execution:
Variable_name Value
Handler_write 1
@@ -877,9 +1226,14 @@ CREATE TABLE t1 (i INT);
EXPLAIN REPLACE INTO t1 SET i = 10;
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
+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 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of testing query execution:
Variable_name Value
Handler_write 1
@@ -896,14 +1250,24 @@ INSERT INTO t1 (i) VALUES (10),(11),(12)
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 8 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 8 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`i` AS `i` from `test`.`t1` where ((`test`.`t1`.`i` > 10) and (`test`.`t1`.`i` <= 18)) order by `test`.`t1`.`i` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -926,12 +1290,19 @@ INSERT INTO t1 (i) VALUES (10),(11),(12)
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 i NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort
@@ -941,6 +1312,9 @@ Warning 1713 Cannot use range access on
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -968,14 +1342,24 @@ INSERT INTO t2 (a, b, c) SELECT i, i, i
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1004,14 +1388,24 @@ INSERT INTO t2 (a, b, c) SELECT t1.i, t1
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 a a 15 NULL 17602 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 ALL a a 15 NULL 17602 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index NULL a 15 NULL 5 352040.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -1036,16 +1430,26 @@ INSERT INTO t2 SELECT i, i, i, i FROM t1
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1074,16 +1478,26 @@ INSERT INTO t2 SELECT i, i, i, i FROM t1
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd 1
@@ -1113,14 +1527,24 @@ INSERT INTO t2 (key1, key2) SELECT i, i
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 2
@@ -1151,14 +1575,24 @@ INSERT INTO t2 (i) SELECT i FROM t1;
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 8 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 8 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using 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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -1183,14 +1617,24 @@ INSERT INTO t2 SELECT i, i, i FROM t1;
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 a 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 a NULL NULL NULL 26 100.00 Using filesort
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1219,14 +1663,24 @@ INSERT INTO t2 (a, b) SELECT t1.i, t1.i
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 ALL a a 6 NULL 5
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED DELETE FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a a 6 NULL 5 100.00
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index NULL a 6 NULL 5 352040.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_last 1
@@ -1251,14 +1705,24 @@ INSERT INTO t2 (i) SELECT i FROM t1;
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 8 Using where
+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 8 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using index condition
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`i` AS `i` from `test`.`t2` where ((`test`.`t2`.`i` > 10) and (`test`.`t2`.`i` <= 18)) order by `test`.`t2`.`i` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -1284,12 +1748,19 @@ INSERT INTO t2 (i) SELECT i FROM t1;
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 i NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET a = 10 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL i NULL NULL NULL 26 100.00 Using where; Using filesort
@@ -1299,6 +1770,9 @@ Warning 1713 Cannot use range access on
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
Warning 1713 Cannot use range access on index 'i' due to type or collation conversion on field 'i'
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1326,14 +1800,24 @@ INSERT INTO t2 (a, b, c) SELECT i, i, i
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1362,14 +1846,24 @@ INSERT INTO t2 (a, b, c) SELECT t1.i, t1
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 a a 15 NULL 17602 Using where
+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 a a 15 NULL 17602 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index NULL a 15 NULL 5 352040.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -1395,16 +1889,26 @@ INSERT INTO t2 SELECT i, i, i, i FROM t1
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1432,16 +1936,26 @@ INSERT INTO t2 SELECT i, i, i, i FROM t1
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 a NULL NULL NULL 26 Using where; Using filesort
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL a NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Warning 1713 Cannot use range access on index 'a' due to type or collation conversion on field 'b'
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE b = 10 ORDER BY a, c LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` where (`test`.`t2`.`b` = 10) order by `test`.`t2`.`a`,`test`.`t2`.`c` limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd 1
@@ -1470,14 +1984,24 @@ INSERT INTO t2 (key1, key2) SELECT i, i
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE key1 < 13 or key2 < 14 ORDER BY key1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index_merge key1,key2 key1,key2 5,5 NULL 7 100.00 Using sort_union(key1,key2); Using where; Using filesort
Warnings:
Note 1003 select `test`.`t2`.`i` AS `i`,`test`.`t2`.`key1` AS `key1`,`test`.`t2`.`key2` AS `key2` from `test`.`t2` where ((`test`.`t2`.`key1` < 13) or (`test`.`t2`.`key2` < 14)) order by `test`.`t2`.`key1`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 2
@@ -1508,14 +2032,24 @@ INSERT INTO t2 (i) SELECT i FROM t1;
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 8 Using where
+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 8 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 WHERE i > 10 AND i <= 18 ORDER BY i DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 8 100.00 Using 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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -1541,14 +2075,24 @@ INSERT INTO t2 SELECT i, i, i FROM t1;
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 a 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 a NULL NULL NULL 26 100.00 Using filesort
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a, b DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 26 100.00 Using filesort
Warnings:
Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` order by `test`.`t2`.`a`,`test`.`t2`.`b` desc limit 5
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_rnd_next 27
@@ -1577,14 +2121,24 @@ INSERT INTO t2 (a, b) SELECT t1.i, t1.i
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 ALL a a 6 NULL 5
+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 ALL a a 6 NULL 5 100.00
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t2 ORDER BY a DESC, b DESC LIMIT 5;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 index NULL a 6 NULL 5 352040.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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_last 1
@@ -1612,14 +2166,26 @@ INSERT INTO t1 VALUES (1,'y',1), (2,'n',
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 PRIMARY,c1_idx c1_idx 2 NULL 2 Using where
+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 PRIMARY,c1_idx c1_idx 2 NULL 2 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_key 3
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 5
@@ -1638,14 +2204,26 @@ Handler_update 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 PRIMARY,c1_idx c1_idx 2 NULL 2 Using where
+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 PRIMARY,c1_idx c1_idx 2 NULL 2 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_key 3
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ref c1_idx c1_idx 2 const 2 100.00 Using index condition; Using where
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_key 3
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 5
@@ -1666,14 +2244,24 @@ INSERT INTO t1 VALUES (),(),(),(),(),(),
EXPLAIN UPDATE t1 SET a=a+10 WHERE a > 34;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED UPDATE t1 SET a=a+10 WHERE a > 34;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > 34;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 34)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 1
@@ -1697,16 +2285,30 @@ EXPLAIN UPDATE t1 LEFT JOIN t2 ON
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+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`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -1724,16 +2326,30 @@ EXPLAIN UPDATE t1 LEFT JOIN t2 ON
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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+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)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_first 1
@@ -1757,12 +2373,19 @@ EXPLAIN UPDATE t1 SET t1.f2=(SELECT MAX(
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 2
+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 2 100.00
Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
EXPLAIN EXTENDED SELECT (SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1) FROM t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -1770,6 +2393,9 @@ id select_type table type possible_keys
Warnings:
Note 1276 Field or reference 'test.t1.f1' of SELECT #2 was resolved in SELECT #1
Note 1003 select (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`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
# Status of "equivalent" SELECT query execution:
Variable_name Value
Handler_read_key 2
@@ -1814,3 +2440,318 @@ ERROR HY000: You are using safe update m
SET SESSION sql_safe_updates = DEFAULT;
DROP TABLE t1;
DROP VIEW v1;
+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 Using join buffer (BNL, incremental buffers)
+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 Using join buffer (BNL, incremental buffers)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 0;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
+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)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 6
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd 1
+Handler_read_rnd_next 9
+Handler_write 2
+
+#
+# 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; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 Using join buffer (BNL, incremental buffers)
+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; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t1, v1 WHERE t1.a = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t11 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (BNL, incremental buffers)
+1 SIMPLE t12 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
+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`)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_rnd_next 9
+# Status of testing query execution:
+Variable_name Value
+Handler_read_rnd 2
+Handler_read_rnd_next 19
+Handler_update 1
+Handler_write 4
+
+DROP TABLE t1;
+DROP VIEW v1;
+CREATE TABLE t1 (a INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 (a) AS SELECT a FROM t1;
+#
+# query: DELETE FROM v1 WHERE a < 4
+# select: SELECT * FROM v1 WHERE a < 4
+#
+EXPLAIN DELETE FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using where
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED DELETE FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM v1 WHERE a < 4;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 100.00 Using where; Using index
+Warnings:
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` < 4)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_first 1
+Handler_read_next 3
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 3
+Handler_read_first 1
+Handler_read_next 3
+
+DROP TABLE t1;
+DROP VIEW v1;
+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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+Warnings:
+Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 4
+Handler_read_rnd_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 4
+Handler_read_key 4
+Handler_read_rnd 4
+Handler_read_rnd_next 5
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT * FROM t2, v1 WHERE t2.x = v1.a;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.x 1 100.00
+Warnings:
+Note 1003 select `test`.`t2`.`x` AS `x`,`test`.`t1`.`a` AS `a`,(`test`.`t1`.`b` + 1) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`x`)
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_key 4
+Handler_read_rnd_next 5
+# Status of testing query execution:
+Variable_name Value
+Handler_delete 4
+Handler_read_key 4
+Handler_read_rnd 4
+Handler_read_rnd_next 5
+
+DROP TABLE t1,t2;
+DROP VIEW v1;
+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 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
+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 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+FLUSH STATUS;
+FLUSH TABLES;
+EXPLAIN EXTENDED SELECT NULL;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1003 select NULL AS `NULL`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+# Status of testing query execution:
+Variable_name Value
+Handler_write 1
+
+DROP TABLE t1;
+DROP VIEW v1;
+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
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+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`
+SHOW STATUS WHERE (Variable_name LIKE 'Sort%' OR
+Variable_name LIKE 'Handler_read_%') AND Value <> 0;
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
+# Status of "equivalent" SELECT query execution:
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
+# Status of testing query execution:
+Variable_name Value
+Handler_read_first 1
+Handler_read_rnd_next 1
+
+DROP TABLE t1, t2;
+DROP VIEW v1;
+CREATE DATABASE grant_db;
+CREATE TABLE grant_db.t1 (a INT);
+CREATE VIEW grant_db.v1 (x) AS SELECT a FROM grant_db.t1;
+GRANT ALL ON grant_db.* TO 'granttest'@localhost;
+REVOKE SELECT ON grant_db.* FROM 'granttest'@localhost;
+USE grant_db;
+EXPLAIN INSERT INTO v1 SELECT * FROM v1;
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 'v1'
+EXPLAIN REPLACE INTO v1 SELECT * FROM v1;
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 'v1'
+EXPLAIN UPDATE t1 SET t1.a = 10 WHERE t1.a IN (SELECT a FROM v1);
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 'v1'
+EXPLAIN UPDATE t1 t11, (SELECT * FROM v1) t12 SET t11.a = 10 WHERE t11.a = 1;
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 'v1'
+EXPLAIN DELETE FROM t1 WHERE t1.a IN (SELECT a FROM v1);
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 'v1'
+EXPLAIN DELETE FROM t1 USING t1 WHERE t1.a IN (SELECT a FROM v1);
+ERROR 42000: SELECT command denied to user 'granttest'@'localhost' for table 't1'
+DROP USER 'granttest'@localhost;
+USE test;
+DROP DATABASE grant_db;
=== modified file 'mysql-test/r/func_gconcat.result'
--- a/mysql-test/r/func_gconcat.result 2011-03-08 19:14:42 +0000
+++ b/mysql-test/r/func_gconcat.result 2011-05-25 14:16:06 +0000
@@ -993,7 +993,7 @@ EXPLAIN EXTENDED SELECT 1 FROM
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 t1 ALL NULL NULL NULL NULL 2 100.00
-2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort; Distinct
+2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary; Distinct
2 DERIVED td ALL NULL NULL NULL NULL 2 100.00 Distinct; Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1`
@@ -1014,7 +1014,7 @@ EXPLAIN EXTENDED SELECT 1 FROM
t1 t2, t1 GROUP BY t1.a) AS d;
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
-2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
+2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (BNL, incremental buffers)
Warnings:
Note 1003 select 1 AS `1` from dual
=== modified file 'mysql-test/r/key.result'
--- a/mysql-test/r/key.result 2010-08-30 06:38:09 +0000
+++ b/mysql-test/r/key.result 2011-05-25 14:16:06 +0000
@@ -607,13 +607,13 @@ CREATE TABLE t1( a INT, b INT, KEY( a )
INSERT INTO t1 values (1, 2), (1, 3), (2, 3), (2, 4), (3, 4), (3, 5);
EXPLAIN SELECT a, SUM( b ) FROM t1 GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort; Using temporary
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 9.212184
EXPLAIN SELECT a, SUM( b ) FROM t1 USE INDEX( a ) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort; Using temporary
SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 9.212184
=== modified file 'mysql-test/r/optimizer_switch.result'
--- a/mysql-test/r/optimizer_switch.result 2011-01-24 14:17:03 +0000
+++ b/mysql-test/r/optimizer_switch.result 2011-05-25 14:16:06 +0000
@@ -281,7 +281,7 @@ HAVING ta.a=tc.f));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where
2 DEPENDENT SUBQUERY tb ALL NULL NULL NULL NULL 6 Using where
-3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
+3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using filesort; Using temporary
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
@@ -301,7 +301,7 @@ HAVING ta.a=tc.f));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tb ALL NULL NULL NULL NULL 6 Start temporary
1 PRIMARY ta ALL NULL NULL NULL NULL 7 Using where; End temporary; Using join buffer (BNL, incremental buffers)
-3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
+3 DEPENDENT SUBQUERY tc ALL NULL NULL NULL NULL 6 Using filesort; Using temporary
SELECT * FROM t1 AS ta
WHERE ta.a IN (SELECT c FROM t2 AS tb
WHERE tb.d >= SOME(SELECT SUM(g) FROM t4 as tc
=== modified file 'mysql-test/r/udf.result'
--- a/mysql-test/r/udf.result 2011-02-09 10:19:05 +0000
+++ b/mysql-test/r/udf.result 2011-05-25 14:16:06 +0000
@@ -101,7 +101,7 @@ create table t1(f1 int);
insert into t1 values(1),(2);
explain select myfunc_int(f1) from t1 order by 1;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort; Using temporary
drop table t1;
CREATE TABLE t1(a INT, b INT);
INSERT INTO t1 values (1,1),(2,2);
@@ -145,22 +145,22 @@ c
2
EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY 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 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY 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 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY 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 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select myfunc_int(`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY 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 Using temporary; Using filesort
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using filesort; Using temporary
Warnings:
Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
=== modified file 'mysql-test/t/explain_non_select.test'
--- a/mysql-test/t/explain_non_select.test 2011-03-30 16:48:25 +0000
+++ b/mysql-test/t/explain_non_select.test 2011-05-25 14:16:06 +0000
@@ -1,3 +1,13 @@
+-- source include/have_innodb.inc
+
+# Grant tests not performed with embedded server
+-- source include/not_embedded.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM;
+
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (3);
--let $query = UPDATE t1 SET a = 10 WHERE a < 10
@@ -196,7 +206,14 @@ INSERT INTO t1 VALUES (1, 1), (2, 2), (3
--source include/explain_non_select.inc
DROP TABLE t1;
-CREATE TABLE t1 (i INT, j INT);
+CREATE TABLE t1 (i INT, j INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+--let $query = DELETE FROM t1
+--let $select = SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT, j INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
--let $query = DELETE FROM t1
--let $select = SELECT * FROM t1
@@ -536,6 +553,8 @@ EXPLAIN EXTENDED UPDATE t1 SET f2=1 ORDE
UPDATE t1 SET f2=1 ORDER BY f2;
DROP TABLE t1;
+# views
+
CREATE TABLE t1 ( a INT, KEY( a ) );
INSERT INTO t1 VALUES (0), (1);
CREATE VIEW v1 AS SELECT t11.a, t12.a AS b FROM t1 t11, t1 t12;
@@ -548,3 +567,102 @@ SET SESSION sql_safe_updates = DEFAULT;
DROP TABLE t1;
DROP VIEW v1;
+
+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;
+--let $query = UPDATE v1 SET a = 1 WHERE a > 0
+--let $select = SELECT * FROM v1 WHERE a > 0
+--source include/explain_non_select.inc
+--let $query = UPDATE t1, v1 SET v1.a = 1 WHERE t1.a = v1.a
+--let $select = SELECT * FROM t1, v1 WHERE t1.a = v1.a
+--source include/explain_non_select.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+CREATE TABLE t1 (a INT, PRIMARY KEY(a));
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+CREATE VIEW v1 (a) AS SELECT a FROM t1;
+--let $query = DELETE FROM v1 WHERE a < 4
+--let $select = SELECT * FROM v1 WHERE a < 4
+--source include/explain_non_select.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+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;
+--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
+--source include/explain_non_select.inc
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+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;
+--let $query = DELETE v1 FROM t2, v1 WHERE t2.x = v1.a
+--let $select = SELECT * FROM t2, v1 WHERE t2.x = v1.a
+--source include/explain_non_select.inc
+DROP TABLE t1,t2;
+DROP VIEW v1;
+
+CREATE TABLE t1 (a INT);
+CREATE VIEW v1 (x) AS SELECT a FROM t1;
+--let $query = INSERT INTO v1 VALUES (10)
+--let $select = SELECT NULL
+--source include/explain_non_select.inc
+DROP TABLE t1;
+DROP VIEW v1;
+
+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;
+--let $query = INSERT INTO v1 SELECT * FROM t1
+--let $select = SELECT * FROM t1
+--source include/explain_non_select.inc
+DROP TABLE t1, t2;
+DROP VIEW v1;
+
+#
+# Grants
+#
+
+CREATE DATABASE grant_db;
+
+CREATE TABLE grant_db.t1 (a INT);
+CREATE VIEW grant_db.v1 (x) AS SELECT a FROM grant_db.t1;
+
+GRANT ALL ON grant_db.* TO 'granttest'@localhost;
+REVOKE SELECT ON grant_db.* FROM 'granttest'@localhost;
+
+connect(con1,localhost,granttest,,);
+connection con1;
+
+USE grant_db;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN INSERT INTO v1 SELECT * FROM v1;
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN REPLACE INTO v1 SELECT * FROM v1;
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN UPDATE t1 SET t1.a = 10 WHERE t1.a IN (SELECT a FROM v1);
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN UPDATE t1 t11, (SELECT * FROM v1) t12 SET t11.a = 10 WHERE t11.a = 1;
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN DELETE FROM t1 WHERE t1.a IN (SELECT a FROM v1);
+--error ER_TABLEACCESS_DENIED_ERROR
+EXPLAIN DELETE FROM t1 USING t1 WHERE t1.a IN (SELECT a FROM v1);
+
+connection default;
+disconnect con1;
+
+DROP USER 'granttest'@localhost;
+USE test;
+DROP DATABASE grant_db;
+
=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc 2011-03-30 16:48:25 +0000
+++ b/sql/opt_explain.cc 2011-05-25 14:16:06 +0000
@@ -14,15 +14,16 @@
51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
#include "opt_explain.h"
+#include "sql_select.h"
#include "sql_partition.h" // for make_used_partitions_str()
+#define EMPTY_NAME ""
+
/**
A base for all Explain_* classes
- This class hierarchy is a successor of the old select_describe() function
- implementation. It extends old select_describe() functionality to deal with
- single-table data-modifying commands (UPDATE and DELETE).
+ Explain_* classes collect and output EXPLAIN data.
*/
class Explain
@@ -32,25 +33,64 @@ private:
Item_null *nil; ///< pre-allocated NULL item to fill empty columns in EXPLAIN
protected:
+ /**
+ Helper class to keep string data in memroot before passing to Item_string
+ */
+ struct memroot_str
+ {
+ const char *str;
+ size_t length;
+
+ memroot_str() { cleanup(); }
+ void cleanup()
+ {
+ str= NULL;
+ length= 0;
+ }
+ bool nil() const { return str == NULL; }
+
+ bool set(THD *thd, const char *str_arg)
+ {
+ return set(thd, str_arg, strlen(str_arg));
+ }
+ bool set(THD *thd, const String &s)
+ {
+ return set(thd, s.ptr(), s.length());
+ }
+ bool set(THD *thd, const char *str_arg, size_t length_arg)
+ {
+ if (!(str= static_cast<char *>(alloc_root(thd->mem_root, length_arg))))
+ return true;
+ memcpy(const_cast<char *>(str), str_arg, length_arg);
+ length= length_arg;
+ return false;
+ }
+ void set_const(const char *str_arg)
+ {
+ str= str_arg;
+ length= strlen(str_arg);
+ }
+ };
+
/*
Next "col_*" fields are intended for the filling by "explain_*()" methods.
Then the make_list() method links these Items into "items" list.
- NOTE: NULL value means that Item_null object will be pushed into "items"
- list instead.
+ NOTE: NULL value or memroot_str.nil()==true means that Item_null object
+ will be pushed into "items" list instead.
*/
Item_uint *col_id; ///< "id" column: seq. number of SELECT withing the query
- Item_string *col_select_type; ///< "select_type" column
- Item_string *col_table_name; ///< "table" to which the row of output refers
- Item_string *col_partitions; ///< "partitions" column
- Item_string *col_join_type; ///< "type" column, see join_type_str array
- Item_string *col_possible_keys; ///< "possible_keys": comma-separated list
- Item_string *col_key; ///< "key" column: index that is actually decided to use
- Item_string *col_key_len; ///< "key_length" column: length of the "key" above
- Item_string *col_ref; ///< "ref":columns/constants which are compared to "key"
+ memroot_str col_select_type; ///< "select_type" column
+ memroot_str col_table_name; ///< "table" to which the row of output refers
+ memroot_str col_partitions; ///< "partitions" column
+ memroot_str col_join_type; ///< "type" column, see join_type_str array
+ memroot_str col_possible_keys; ///< "possible_keys": comma-separated list
+ memroot_str col_key; ///< "key" column: index that is actually decided to use
+ memroot_str col_key_len; ///< "key_length" column: length of the "key" above
+ memroot_str col_ref; ///< "ref":columns/constants which are compared to "key"
Item_int *col_rows; ///< "rows": estimated number of examined table rows
Item_float *col_filtered; ///< "filtered": % of rows filtered by condition
- Item_string *col_extra; ///< "extra" column: additional information
+ memroot_str col_extra; ///< "extra" column: additional information
THD *thd; ///< cached THD pointer
const CHARSET_INFO *cs; ///< cached pointer to system_charset_info
@@ -59,10 +99,9 @@ protected:
select_result *external_result; ///< result stream (if any) provided by caller
-public:
- explicit Explain(JOIN *join_arg= NULL)
+ explicit Explain(THD *thd_arg, JOIN *join_arg= NULL)
: nil(NULL),
- thd(current_thd),
+ thd(thd_arg),
cs(system_charset_info),
join(join_arg),
select_lex(join ? join->select_lex : &thd->lex->select_lex),
@@ -72,12 +111,20 @@ public:
}
virtual ~Explain() {}
+public:
bool send();
private:
void init_columns();
bool make_list();
bool push(Item *item) { return items.push_back(item ? item : nil); }
+ bool push(const memroot_str &s)
+ {
+ if (s.nil())
+ return items.push_back(nil);
+ Item_string *item= new Item_string(s.str, s.length, cs);
+ return item == NULL || items.push_back(item);
+ }
protected:
bool describe(uint8 mask) { return thd->lex->describe & mask; }
@@ -89,8 +136,8 @@ protected:
select_send object. Then that JOIN object prepares the select_send
object calling result->prepare() in JOIN::prepare(),
result->initalize_tables() in JOIN::optimize() and result->prepare2()
- in JOIN::exe().
- However witout the presence of the top-level JOIN we have to
+ in JOIN::exec().
+ However without the presence of the top-level JOIN we have to
prepare/initialize select_send object manually.
*/
bool prepare(select_result *result)
@@ -104,22 +151,22 @@ protected:
virtual bool send_to(select_result *to);
/*
- Rest of the methods are overloadable functions those calculate and fill
+ Rest of the methods are overloadable functions, those calculate and fill
"col_*" fields with Items for further sending as EXPLAIN columns.
- "explain_*" methods return FALSE on success and TRUE on error (usually OOM).
+ "explain_*" methods return false on success and true on error (usually OOM).
*/
virtual bool explain_id();
virtual bool explain_select_type();
- virtual bool explain_table_name() { return FALSE; }
- virtual bool explain_partitions() { return FALSE; }
- virtual bool explain_join_type() { return FALSE; }
- virtual bool explain_possible_keys() { return FALSE; }
- /* fill col_key and and col_key_len fields together */
- virtual bool explain_key_and_len() { return FALSE; }
- virtual bool explain_ref() { return FALSE; }
- /* fill col_rows and col_filtered fields together */
- virtual bool explain_rows_and_filtered() { return FALSE; }
+ virtual bool explain_table_name() { return false; }
+ virtual bool explain_partitions() { return false; }
+ virtual bool explain_join_type() { return false; }
+ virtual bool explain_possible_keys() { return false; }
+ /** fill col_key and and col_key_len fields together */
+ virtual bool explain_key_and_len() { return false; }
+ virtual bool explain_ref() { return false; }
+ /** fill col_rows and col_filtered fields together */
+ virtual bool explain_rows_and_filtered() { return false; }
virtual bool explain_extra();
};
@@ -127,7 +174,6 @@ protected:
/**
Explain_msg class outputs a trivial EXPLAIN row with "extra" column
- Fromer part of the old select_describe() function.
This class is intended for simple cases to produce EXPLAIN output
with "No tables used", "No matching records" etc.
Optionally it can output number of estimated rows in the "row"
@@ -143,12 +189,12 @@ private:
const ha_rows rows; ///< HA_POS_ERROR or cached "rows" argument
public:
- Explain_msg(JOIN *join_arg, const char *message_arg)
- : Explain(join_arg), message(message_arg), rows(HA_POS_ERROR)
+ Explain_msg(THD *thd_arg, JOIN *join_arg, const char *message_arg)
+ : Explain(thd_arg, join_arg), message(message_arg), rows(HA_POS_ERROR)
{}
-
- explicit Explain_msg(const char *message_arg, ha_rows rows_arg= HA_POS_ERROR)
- : message(message_arg), rows(rows_arg)
+
+ explicit Explain_msg(THD *thd_arg, const char *message_arg, ha_rows rows_arg= HA_POS_ERROR)
+ : Explain(thd_arg), message(message_arg), rows(rows_arg)
{}
protected:
@@ -159,8 +205,6 @@ protected:
/**
Explain_union class outputs EXPLAIN row for UNION
-
- Former part of the old select_describe() function.
*/
class Explain_union : public Explain
@@ -169,7 +213,7 @@ private:
char table_name_buffer[NAME_CHAR_LEN];
public:
- Explain_union(JOIN *join_arg) : Explain(join_arg)
+ Explain_union(THD *thd_arg, JOIN *join_arg) : Explain(thd_arg, join_arg)
{
/* it's a UNION: */
DBUG_ASSERT(join_arg->select_lex == join_arg->unit->fake_select_lex);
@@ -186,8 +230,6 @@ protected:
/**
Common base class for Explain_join and Explain_table
-
- Former part of the old select_describe() function.
*/
class Explain_table_base : public Explain {
@@ -195,70 +237,147 @@ protected:
TABLE *table;
key_map usable_keys;
- char buff_possible_keys[512];
- String str_possible_keys;
-
-public:
- explicit Explain_table_base(JOIN *join_arg)
- : Explain(join_arg), table(NULL),
- str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs)
+ Explain_table_base(THD *thd_arg, JOIN *join_arg)
+ : Explain(thd_arg, join_arg), table(NULL)
{}
- explicit Explain_table_base(TABLE *table_arg)
- : table(table_arg),
- str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs)
+ Explain_table_base(THD *thd_arg, TABLE *table_arg)
+ : Explain(thd_arg), table(table_arg)
{}
-protected:
virtual bool explain_partitions();
virtual bool explain_possible_keys();
+
+ bool explain_key_and_len_quick(const SQL_SELECT *select)
+ {
+ DBUG_ASSERT(select && select->quick);
+
+ char buff_key[512];
+ String str_key(buff_key, sizeof(buff_key), cs);
+ str_key.length(0);
+
+ char buff_key_len[512];
+ String str_key_len(buff_key_len, sizeof(buff_key_len), cs);
+ str_key_len.length(0);
+
+ select->quick->add_keys_and_lengths(&str_key, &str_key_len);
+ return col_key.set(thd, str_key) || col_key_len.set(thd, str_key_len);
+ }
+ bool explain_key_and_len_index(int key)
+ {
+ DBUG_ASSERT(key != MAX_KEY);
+
+ KEY *key_info= table->key_info + key;
+ char buff_key_len[512];
+ int length= longlong2str(key_info->key_length, buff_key_len, 10) -
+ buff_key_len;
+ return col_key.set(thd, key_info->name) ||
+ col_key_len.set(thd, buff_key_len, length);
+ }
+ void explain_extra_common(const SQL_SELECT *select, const JOIN_TAB *tab, int quick_type, uint keyno, bool need_sort, String *str_extra)
+ {
+ if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
+ table->file->pushed_idx_cond) || (tab && tab->cache_idx_cond))
+ str_extra->append(STRING_WITH_LEN("; Using index condition"));
+
+ switch (quick_type) {
+ case QUICK_SELECT_I::QS_TYPE_ROR_UNION:
+ case QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT:
+ case QUICK_SELECT_I::QS_TYPE_INDEX_MERGE:
+ str_extra->append(STRING_WITH_LEN("; Using "));
+ select->quick->add_info_string(str_extra);
+ break;
+ default: ;
+ }
+
+ if (select)
+ {
+ if (tab && tab->use_quick == QS_DYNAMIC_RANGE)
+ {
+ /* 4 bits per 1 hex digit + terminating '\0' */
+ char buf[MAX_KEY / 4 + 1];
+ str_extra->append(STRING_WITH_LEN("; Range checked for each "
+ "record (index map: 0x"));
+ str_extra->append(tab->keys.print(buf));
+ str_extra->append(')');
+ }
+ else if (select->cond)
+ {
+ const Item *pushed_cond= table->file->pushed_cond;
+
+ if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
+ pushed_cond)
+ {
+ str_extra->append(STRING_WITH_LEN("; Using where with pushed condition"));
+ if (describe(DESCRIBE_EXTENDED))
+ {
+ str_extra->append(STRING_WITH_LEN(": "));
+ ((Item *)pushed_cond)->print(str_extra, QT_ORDINARY);
+ }
+ }
+ else
+ str_extra->append(STRING_WITH_LEN("; Using where"));
+ }
+ }
+ if (table->reginfo.not_exists_optimize)
+ str_extra->append(STRING_WITH_LEN("; Not exists"));
+
+ if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
+ !(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags &
+ (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
+ {
+ /*
+ During normal execution of a query, multi_range_read_init() is
+ called to initialize MRR. If HA_MRR_SORTED is set at this point,
+ multi_range_read_init() for any native MRR implementation will
+ revert to default MRR because they cannot produce sorted output
+ currently.
+ Calling multi_range_read_init() can potentially be costly, so it
+ is not done when executing an EXPLAIN. We therefore make the
+ assumption that HA_MRR_SORTED means no MRR. If some MRR native
+ implementation will support sorted output in the future, a
+ function "bool mrr_supports_sorted()" should be added in the
+ handler.
+ */
+ str_extra->append(STRING_WITH_LEN("; Using MRR"));
+ }
+ if (need_sort)
+ str_extra->append(STRING_WITH_LEN("; Using filesort"));
+ }
};
/**
Explain_join class produces EXPLAIN output for JOINs
-
- Former part of the old select_describe() function.
*/
class Explain_join : public Explain_table_base
{
private:
- const bool need_tmp_table;
- const bool need_order;
- const bool distinct;
-
- uint tabnum;
- JOIN_TAB *tab;
- int quick_type;
- table_map used_tables;
- uint last_sjm_table;
+ const bool need_tmp_table; ///< add "Using temporary" to "extra" if true
+ const bool need_order; ///< add "Using filesort"" to "extra" if true
+ const bool distinct; ///< add "Distinct" string to "extra" column if true
+
+ uint tabnum; ///< current tab number in join->join_tab[]
+ JOIN_TAB *tab; ///< current JOIN_TAB
+ int quick_type; ///< current quick type, see anon. enum at QUICK_SELECT_I
+ table_map used_tables; ///< accumulate used tables bitmap
+ uint last_sjm_table; ///< last materialized semi-joined table
+ /*
+ Next few private fields are intermediate buffers.
+ We need them here since String objects don't care about pre-allocated
+ data lifetime.
+ */
char table_name_buffer[NAME_LEN];
- char buff_key[512];
- String str_key;
-
- char buff_key_len[512];
- String str_key_len;
-
- char buff_ref[512];
- String str_ref;
-
- char buff_extra[512];
- String str_extra;
-
public:
- Explain_join(JOIN *join_arg,
+ Explain_join(THD *thd_arg, JOIN *join_arg,
bool need_tmp_table_arg, bool need_order_arg,
- bool distinct_arg)
- : Explain_table_base(join_arg), need_tmp_table(need_tmp_table_arg),
+ bool distinct_arg)
+ : Explain_table_base(thd_arg, join_arg), need_tmp_table(need_tmp_table_arg),
need_order(need_order_arg), distinct(distinct_arg),
- tabnum(0), used_tables(0), last_sjm_table(MAX_TABLES),
- str_key(buff_key, sizeof(buff_key), cs),
- str_key_len(buff_key_len, sizeof(buff_key_len), cs),
- str_ref(buff_ref, sizeof(buff_ref), cs),
- str_extra(buff_extra, sizeof(buff_extra), cs)
+ tabnum(0), used_tables(0), last_sjm_table(MAX_TABLES)
{
/* it is not UNION: */
DBUG_ASSERT(join_arg->select_lex != join_arg->unit->fake_select_lex);
@@ -291,28 +410,15 @@ private:
const ha_rows limit; ///< HA_POS_ERROR or cached "limit" argument
const bool need_sort; ///< cached need_sort argument
- /*
- Pre-allocated buffers and String wrapper objects for them
- */
- char buff_key[512];
- String str_key;
- char buff_key_len[512];
- String str_key_len;
- char buff_extra[512];
- String str_extra;
-
public:
- Explain_table(TABLE *table_arg, SQL_SELECT *select_arg,
+ Explain_table(THD *thd_arg, TABLE *table_arg, SQL_SELECT *select_arg,
uint key_arg, ha_rows limit_arg, bool need_sort_arg)
- : Explain_table_base(table_arg), select(select_arg), key(key_arg),
- limit(limit_arg), need_sort(need_sort_arg),
- str_key(buff_key, sizeof(buff_key), cs),
- str_key_len(buff_key_len, sizeof(buff_key_len), cs),
- str_extra(buff_extra, sizeof(buff_extra), cs)
+ : Explain_table_base(thd_arg, table_arg), select(select_arg), key(key_arg),
+ limit(limit_arg), need_sort(need_sort_arg)
{
usable_keys= table->keys_in_use_for_query;
}
-
+
private:
virtual bool explain_table_name();
virtual bool explain_join_type();
@@ -338,18 +444,19 @@ static join_type calc_join_type(int quic
/**
Explain class main function
-
+
This method:
a) allocates a select_send object (if no one pre-allocated available),
b) calculates and sends whole EXPLAIN data.
@returns
- @retval FALSE Ok
- @retval TRUE Error
+ @retval false Ok
+ @retval true Error
*/
bool Explain::send()
{
+ DBUG_ENTER("Explain::send");
/* Don't log this into the slow query log */
thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
SERVER_QUERY_NO_GOOD_INDEX_USED);
@@ -358,13 +465,12 @@ bool Explain::send()
if (external_result == NULL)
{
/* Create select_result object if the called doesn't provide one: */
- result= new select_send;
if (!(result= new select_send))
- return TRUE;
+ DBUG_RETURN(true);
if (thd->send_explain_fields(result) || prepare(result))
{
delete result;
- return TRUE;
+ DBUG_RETURN(true);;
}
}
else
@@ -373,8 +479,8 @@ bool Explain::send()
external_result->reset_offset_limit_cnt();
}
- if (!(nil= new Item_null))
- return TRUE;
+ if (nil == NULL && !(nil= new Item_null))
+ DBUG_RETURN(true);
bool ret= send_to(result);
if (ret && join)
@@ -393,7 +499,7 @@ bool Explain::send()
result->send_eof();
delete result;
}
- return ret;
+ DBUG_RETURN(ret);
}
@@ -404,17 +510,17 @@ bool Explain::send()
void Explain::init_columns()
{
col_id= NULL;
- col_select_type= NULL;
- col_table_name= NULL;
- col_partitions= NULL;
- col_join_type= NULL;
- col_possible_keys= NULL;
- col_key= NULL;
- col_key_len= NULL;
- col_ref= NULL;
+ col_select_type.cleanup();
+ col_table_name.cleanup();
+ col_partitions.cleanup();
+ col_join_type.cleanup();
+ col_possible_keys.cleanup();
+ col_key.cleanup();
+ col_key_len.cleanup();
+ col_ref.cleanup();
col_rows= NULL;
col_filtered= NULL;
- col_extra= NULL;
+ col_extra.cleanup();
}
@@ -422,8 +528,8 @@ void Explain::init_columns()
Calculate EXPLAIN column values and link them into "items" list
@returns
- @retval FALSE Ok
- @retval TRUE Error
+ @retval false Ok
+ @retval true Error
*/
bool Explain::make_list()
@@ -438,9 +544,9 @@ bool Explain::make_list()
explain_ref() ||
explain_rows_and_filtered() ||
explain_extra())
- return TRUE;
-
- /*
+ return true;
+
+ /*
NOTE: the number/types of items pushed into item_list must be in sync with
EXPLAIN column types as they're "defined" in THD::send_explain_fields()
*/
@@ -467,13 +573,13 @@ bool Explain::make_list()
it many times (once for each JOIN::join_tab[] element).
@returns
- @retval FALSE Ok
- @retval TRUE Error
+ @retval false Ok
+ @retval true Error
*/
bool Explain::send_to(select_result *to)
{
- bool ret= make_list() || to->send_data(items);
+ const bool ret= make_list() || to->send_data(items);
items.empty();
init_columns();
return ret;
@@ -490,20 +596,19 @@ bool Explain::explain_id()
bool Explain::explain_select_type()
{
if (select_lex->type)
- col_select_type= new Item_string(select_lex->type,
- strlen(select_lex->type), cs);
+ col_select_type.set(thd, select_lex->type);
else if (select_lex->first_inner_unit() || select_lex->next_select())
- col_select_type= new Item_string(STRING_WITH_LEN("PRIMARY"), cs);
+ col_select_type.set_const("PRIMARY");
else
- col_select_type= new Item_string(STRING_WITH_LEN("SIMPLE"), cs);
- return col_select_type == NULL;
+ col_select_type.set_const("SIMPLE");
+ return col_select_type.nil();
}
bool Explain::explain_extra()
{
- col_extra= new Item_string("", 0, cs);
- return col_extra == NULL;
+ col_extra.set_const("");
+ return false;
}
@@ -513,7 +618,7 @@ bool Explain::explain_extra()
bool Explain_msg::explain_rows_and_filtered()
{
if (rows == HA_POS_ERROR)
- return FALSE;
+ return false;
col_rows= new Item_int(rows, MY_INT64_NUM_DECIMAL_DIGITS);
return col_rows == NULL;
}
@@ -521,8 +626,7 @@ bool Explain_msg::explain_rows_and_filte
bool Explain_msg::explain_extra()
{
- col_extra= new Item_string(message, strlen(message), cs);
- return col_extra == NULL;
+ return col_extra.set(thd, message);
}
@@ -531,8 +635,7 @@ bool Explain_msg::explain_extra()
bool Explain_union::explain_id()
{
- col_id= NULL;
- return FALSE;
+ return false;
}
@@ -550,8 +653,8 @@ bool Explain_union::explain_table_name()
- 6 + last_length: the number of characters needed to print
'...,'<last_select->select_number>'>\0'
*/
- for (;
- sl && len + lastop + 6 + last_length < NAME_CHAR_LEN;
+ for (;
+ sl && len + lastop + 6 + last_length < NAME_CHAR_LEN;
sl= sl->next_select())
{
len+= lastop;
@@ -568,25 +671,19 @@ bool Explain_union::explain_table_name()
len+= lastop;
table_name_buffer[len - 1]= '>'; // change ',' to '>'
- col_table_name= new Item_string(table_name_buffer, len, cs);
-
- return col_table_name == NULL;
+ return col_table_name.set(thd, table_name_buffer, len);
}
bool Explain_union::explain_join_type()
{
- col_join_type= new Item_string(join_type_str[JT_ALL],
- strlen(join_type_str[JT_ALL]), cs);
- return col_join_type == NULL;
+ return col_join_type.set(thd, join_type_str[JT_ALL]);
}
bool Explain_union::explain_extra()
{
- /*
- Moved from select_describe():
-
+ /*
here we assume that the query will return at least two rows, so we
show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
and no filesort will be actually done, but executing all selects in
@@ -595,8 +692,8 @@ bool Explain_union::explain_extra()
*/
if (join->unit->global_parameters->order_list.first)
{
- col_extra= new Item_string(STRING_WITH_LEN("Using filesort"), cs);
- return col_extra == NULL;
+ col_extra.set_const("Using filesort");
+ return false;
}
return Explain::explain_extra();
}
@@ -609,41 +706,38 @@ bool Explain_table_base::explain_partiti
{
#ifdef WITH_PARTITION_STORAGE_ENGINE
if (!table->derived_select_number && table->part_info)
- {
- col_partitions= new Item_string(cs);
- if (col_partitions == NULL)
- return TRUE;
- make_used_partitions_str(table->part_info, &col_partitions->str_value);
+ {
+ String s;
+ make_used_partitions_str(table->part_info, &s);
+ return col_partitions.set(thd, s);
}
#endif
- return FALSE;
+ return false;
}
bool Explain_table_base::explain_possible_keys()
{
if (usable_keys.is_clear_all())
- return FALSE;
+ return false;
+ char buff_possible_keys[512];
+ String str_possible_keys(buff_possible_keys, sizeof(buff_possible_keys), cs);
str_possible_keys.length(0);
- for (uint j=0 ; j < table->s->keys ; j++)
+ for (uint j= 0 ; j < table->s->keys ; j++)
{
if (usable_keys.is_set(j))
{
if (str_possible_keys.length())
str_possible_keys.append(',');
- str_possible_keys.append(table->key_info[j].name,
+ str_possible_keys.append(table->key_info[j].name,
strlen(table->key_info[j].name), cs);
}
}
if (str_possible_keys.length())
- {
- col_possible_keys= new Item_string(str_possible_keys.ptr(),
- str_possible_keys.length(), cs);
- return col_possible_keys == NULL;
- }
- return FALSE;
+ return col_possible_keys.set(thd, str_possible_keys);
+ return false;
}
@@ -658,7 +752,7 @@ bool Explain_join::send_to(select_result
table= tab->table;
usable_keys= tab->keys;
quick_type= -1;
-
+
if (tab->type == JT_ALL && tab->select && tab->select->quick)
{
quick_type= tab->select->quick->get_type();
@@ -666,11 +760,11 @@ bool Explain_join::send_to(select_result
}
if (Explain_table_base::send_to(external_result))
- return TRUE;
-
+ return true;
+
used_tables|= table->map;
}
- return FALSE;
+ return false;
}
@@ -681,63 +775,51 @@ bool Explain_join::explain_table_name()
/* Derived table name generation */
int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer) - 1,
"<derived%u>", table->derived_select_number);
- col_table_name= new Item_string(table_name_buffer, len, cs);
+ return col_table_name.set(thd, table_name_buffer, len);
}
else
{
- TABLE_LIST *real_table= table->pos_in_table_list;
- col_table_name= new Item_string(real_table->alias,
- strlen(real_table->alias), cs);
+ TABLE_LIST *real_table= table->pos_in_table_list;
+ return col_table_name.set(thd, real_table->alias);
}
- return col_table_name == NULL;
}
bool Explain_join::explain_join_type()
{
- col_join_type= new Item_string(join_type_str[tab->type],
- strlen(join_type_str[tab->type]), cs);
- return col_join_type == NULL;
+ return col_join_type.set(thd, join_type_str[tab->type]);
}
bool Explain_join::explain_key_and_len()
{
- str_key.length(0);
- str_key_len.length(0);
-
+ char buff_key_len[512];
if (tab->ref.key_parts)
{
KEY *key_info= table->key_info + tab->ref.key;
uint length;
- col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
+ col_key.set(thd, key_info->name);
length= longlong2str(tab->ref.key_length, buff_key_len, 10) - buff_key_len;
- col_key_len= new Item_string(buff_key_len, length, cs);
- return col_key == NULL || col_key_len == NULL;
+ col_key_len.set(thd, buff_key_len, length);
+ return col_key.nil() || col_key_len.nil();
}
else if (tab->type == JT_NEXT)
- {
- KEY *key_info= table->key_info + tab->index;
- uint length;
- col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
- length= longlong2str(key_info->key_length, buff_key_len, 10) -
- buff_key_len;
- col_key_len= new Item_string(buff_key_len, length, cs);
- return col_key == NULL || col_key_len == NULL;
- }
+ return explain_key_and_len_index(tab->index);
else if (tab->select && tab->select->quick)
- {
- tab->select->quick->add_keys_and_lengths(&str_key, &str_key_len);
- col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
- col_key_len= new Item_string(str_key_len.ptr(), str_key_len.length(), cs);
- return col_key == NULL || col_key_len == NULL;
- }
+ return explain_key_and_len_quick(tab->select);
else
{
- TABLE_LIST *table_list= tab->table->pos_in_table_list;
+ TABLE_LIST *table_list= table->pos_in_table_list;
if (table_list->schema_table &&
table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
{
+ char buff_key[512];
+ String str_key(buff_key, sizeof(buff_key), cs);
+ str_key.length(0);
+
+ String str_key_len(buff_key_len, sizeof(buff_key_len), cs);
+ str_key_len.length(0);
+
const char *f_name;
int f_idx;
if (table_list->has_db_lookup_value)
@@ -745,7 +827,7 @@ bool Explain_join::explain_key_and_len()
f_idx= table_list->schema_table->idx_field1;
f_name= table_list->schema_table->fields_info[f_idx].field_name;
str_key.append(f_name, strlen(f_name), cs);
- }
+ }
if (table_list->has_table_lookup_value)
{
if (table_list->has_db_lookup_value)
@@ -755,39 +837,36 @@ bool Explain_join::explain_key_and_len()
str_key.append(f_name, strlen(f_name), cs);
}
if (str_key.length())
- {
- col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
- return col_key == NULL;
- }
+ return col_key.set(thd, str_key);
}
}
- return FALSE;
+ return false;
}
bool Explain_join::explain_ref()
{
- str_ref.length(0);
-
if (tab->ref.key_parts)
{
+ char buff_ref[512];
+ String str_ref(buff_ref, sizeof(buff_ref), cs);
+ str_ref.length(0);
for (store_key **ref= tab->ref.key_copy; *ref; ref++)
{
if (str_ref.length())
str_ref.append(',');
str_ref.append((*ref)->name(), strlen((*ref)->name()), cs);
}
- col_ref= new Item_string(str_ref.ptr(), str_ref.length(), cs);
- return col_ref == NULL;
+ return col_ref.set(thd, str_ref);
}
- return FALSE;
+ return false;
}
bool Explain_join::explain_rows_and_filtered()
{
- if (tab->table->pos_in_table_list->schema_table)
- return FALSE;
+ if (table->pos_in_table_list->schema_table)
+ return false;
double examined_rows;
if (tab->select && tab->select->quick)
@@ -798,47 +877,47 @@ bool Explain_join::explain_rows_and_filt
examined_rows= rows2double(tab->limit);
else
{
- tab->table->file->info(HA_STATUS_VARIABLE);
- examined_rows= rows2double(tab->table->file->stats.records);
+ table->file->info(HA_STATUS_VARIABLE);
+ examined_rows= rows2double(table->file->stats.records);
}
}
else
- examined_rows= join->best_positions[tabnum].records_read;
+ examined_rows= join->best_positions[tabnum].records_read;
- col_rows= new Item_int((longlong) (ulonglong) examined_rows,
+ col_rows= new Item_int((longlong) (ulonglong) examined_rows,
MY_INT64_NUM_DECIMAL_DIGITS);
if (col_rows == NULL)
- return TRUE;
+ return true;
/* Add "filtered" field */
if (describe(DESCRIBE_EXTENDED))
{
- float f= 0.0;
+ float f= 0.0;
if (examined_rows)
- f= (float) (100.0 * join->best_positions[tabnum].records_read /
- examined_rows);
+ f= 100.0 * join->best_positions[tabnum].records_read / examined_rows;
col_filtered= new Item_float(f, 2);
- if (col_filtered == NULL)
- return TRUE;
+ return col_filtered == NULL;
}
- return FALSE;
+ return false;
}
bool Explain_join::explain_extra()
{
+ char buff_extra[512];
+ String str_extra(buff_extra, sizeof(buff_extra), cs);
str_extra.length(0);
- my_bool key_read=table->key_read;
+ bool key_read= table->key_read;
if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
table->covering_keys.is_set(tab->index))
- key_read=1;
+ key_read= true;
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
!((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
- key_read=1;
-
+ key_read= true;
+
if (tab->info)
- col_extra= new Item_string(tab->info,strlen(tab->info), cs);
+ col_extra.set(thd, tab->info);
else if (tab->packed_info & TAB_INFO_HAVE_VALUE)
{
if (tab->packed_info & TAB_INFO_USING_INDEX)
@@ -855,7 +934,7 @@ bool Explain_join::explain_extra()
str += 2;
len -= 2;
}
- col_extra= new Item_string(str, len, cs);
+ col_extra.set(thd, str, len);
}
else
{
@@ -865,48 +944,10 @@ bool Explain_join::explain_extra()
else if (tab->select && tab->select->quick)
keyno = tab->select->quick->index;
- if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
- table->file->pushed_idx_cond) || tab->cache_idx_cond)
- str_extra.append(STRING_WITH_LEN("; Using index condition"));
+ explain_extra_common(tab->select, tab, quick_type, keyno,
+ (tabnum == 0 && need_order), &str_extra);
- if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
- quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
- quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE)
- {
- str_extra.append(STRING_WITH_LEN("; Using "));
- tab->select->quick->add_info_string(&str_extra);
- }
- if (tab->select)
- {
- if (tab->use_quick == QS_DYNAMIC_RANGE)
- {
- /* 4 bits per 1 hex digit + terminating '\0' */
- char buf[MAX_KEY / 4 + 1];
- str_extra.append(STRING_WITH_LEN("; Range checked for each "
- "record (index map: 0x"));
- str_extra.append(tab->keys.print(buf));
- str_extra.append(')');
- }
- else if (tab->select->cond)
- {
- const Item *pushed_cond= tab->table->file->pushed_cond;
-
- if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
- pushed_cond)
- {
- str_extra.append(STRING_WITH_LEN("; Using where with pushed "
- "condition"));
- if (describe(DESCRIBE_EXTENDED))
- {
- str_extra.append(STRING_WITH_LEN(": "));
- ((Item *)pushed_cond)->print(&str_extra, QT_ORDINARY);
- }
- }
- else
- str_extra.append(STRING_WITH_LEN("; Using where"));
- }
- }
- TABLE_LIST *table_list= tab->table->pos_in_table_list;
+ TABLE_LIST *table_list= table->pos_in_table_list;
if (table_list->schema_table &&
table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE)
{
@@ -929,7 +970,7 @@ bool Explain_join::explain_extra()
{
if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX)
{
- QUICK_GROUP_MIN_MAX_SELECT *qgs=
+ QUICK_GROUP_MIN_MAX_SELECT *qgs=
(QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick;
str_extra.append(STRING_WITH_LEN("; Using index for group-by"));
qgs->append_loose_scan_type(&str_extra);
@@ -937,41 +978,15 @@ bool Explain_join::explain_extra()
else
str_extra.append(STRING_WITH_LEN("; Using index"));
}
- if (table->reginfo.not_exists_optimize)
- str_extra.append(STRING_WITH_LEN("; Not exists"));
- if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
- !(((QUICK_RANGE_SELECT*)(tab->select->quick))->mrr_flags &
- (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
- {
- /*
- During normal execution of a query, multi_range_read_init() is
- called to initialize MRR. If HA_MRR_SORTED is set at this point,
- multi_range_read_init() for any native MRR implementation will
- revert to default MRR because they cannot produce sorted output
- currently.
- Calling multi_range_read_init() can potentially be costly, so it
- is not done when executing an EXPLAIN. We therefore make the
- assumption that HA_MRR_SORTED means no MRR. If some MRR native
- implementation will support sorted output in the future, a
- function "bool mrr_supports_sorted()" should be added in the
- handler.
- */
- str_extra.append(STRING_WITH_LEN("; Using MRR"));
- }
if (tabnum == 0 && need_tmp_table)
str_extra.append(STRING_WITH_LEN("; Using temporary"));
- if (tabnum == 0 && need_order)
- str_extra.append(STRING_WITH_LEN("; Using filesort"));
-
if (distinct && test_all_bits(used_tables,thd->used_tables))
str_extra.append(STRING_WITH_LEN("; Distinct"));
if (tab->loosescan_match_tab)
- {
str_extra.append(STRING_WITH_LEN("; LooseScan"));
- }
if (tab->flush_weedout_table)
str_extra.append(STRING_WITH_LEN("; Start temporary"));
@@ -984,7 +999,7 @@ bool Explain_join::explain_extra()
else
{
str_extra.append(STRING_WITH_LEN("; FirstMatch("));
- TABLE *prev_table=tab->do_firstmatch->table;
+ TABLE *prev_table= tab->do_firstmatch->table;
if (prev_table->derived_select_number)
{
char namebuf[NAME_LEN];
@@ -1051,9 +1066,9 @@ bool Explain_join::explain_extra()
str += 2;
len -= 2;
}
- col_extra= new Item_string(str, len, cs);
+ col_extra.set(thd, str, len);
}
- return col_extra == NULL;
+ return col_extra.nil();
}
@@ -1062,8 +1077,7 @@ bool Explain_join::explain_extra()
bool Explain_table::explain_table_name()
{
- col_table_name= new Item_string(table->alias, strlen(table->alias), cs);
- return col_table_name == NULL;
+ return col_table_name.set(thd, table->alias);
}
@@ -1075,34 +1089,17 @@ bool Explain_table::explain_join_type()
else
jt= JT_ALL;
- col_join_type= new Item_string(join_type_str[jt],
- strlen(join_type_str[jt]), cs);
- return col_join_type == NULL;
+ return col_join_type.set(thd, join_type_str[jt]);
}
bool Explain_table::explain_key_and_len()
{
- str_key.length(0);
- str_key_len.length(0);
-
if (key != MAX_KEY)
- {
- KEY *key_info= table->key_info + key;
- col_key= new Item_string(key_info->name, strlen(key_info->name), cs);
- int length= longlong2str(key_info->key_length, buff_key_len, 10) -
- buff_key_len;
- col_key_len= new Item_string(buff_key_len, length, cs);
- return col_key == NULL || col_key_len == NULL;
- }
- else if (select && select->quick)
- {
- select->quick->add_keys_and_lengths(&str_key, &str_key_len);
- col_key= new Item_string(str_key.ptr(), str_key.length(), cs);
- col_key_len= new Item_string(str_key_len.ptr(), str_key_len.length(), cs);
- return col_key == NULL || col_key_len == NULL;
- }
- return FALSE;
+ return explain_key_and_len_index(key);
+ if (select && select->quick)
+ return explain_key_and_len_quick(select);
+ return false;
}
@@ -1121,83 +1118,28 @@ bool Explain_table::explain_rows_and_fil
col_rows= new Item_int((longlong) (ulonglong) examined_rows,
MY_INT64_NUM_DECIMAL_DIGITS);
if (col_rows == NULL)
- return TRUE;
+ return true;
if (describe(DESCRIBE_EXTENDED))
{
col_filtered= new Item_float(100.0, 2);
if (col_filtered == NULL)
- return TRUE;
+ return true;
}
- return FALSE;
+ return false;
}
bool Explain_table::explain_extra()
{
+ char buff_extra[512];
+ String str_extra(buff_extra, sizeof(buff_extra), cs);
str_extra.length(0);
uint keyno= (select && select->quick) ? select->quick->index : key;
-
- if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
- table->file->pushed_idx_cond)
- str_extra.append(STRING_WITH_LEN("; Using index condition"));
-
int quick_type= (select && select->quick) ? select->quick->get_type() : -1;
- switch (quick_type) {
- case QUICK_SELECT_I::QS_TYPE_ROR_UNION:
- case QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT:
- case QUICK_SELECT_I::QS_TYPE_INDEX_MERGE:
- str_extra.append(STRING_WITH_LEN("; Using "));
- select->quick->add_info_string(&str_extra);
- break;
- default: ;
- }
-
- if (select && select->cond)
- {
- const Item *pushed_cond= table->file->pushed_cond;
-
- if (thd->optimizer_switch_flag(OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) &&
- pushed_cond)
- {
- str_extra.append(STRING_WITH_LEN("; Using where with pushed "
- "condition"));
- if (describe(DESCRIBE_EXTENDED))
- {
- str_extra.append(STRING_WITH_LEN(": "));
- ((Item *)pushed_cond)->print(&str_extra, QT_ORDINARY);
- }
- }
- else
- str_extra.append(STRING_WITH_LEN("; Using where"));
- }
-
- if (table->reginfo.not_exists_optimize)
- str_extra.append(STRING_WITH_LEN("; Not exists"));
-
- if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE &&
- !(((QUICK_RANGE_SELECT*)(select->quick))->mrr_flags &
- (HA_MRR_USE_DEFAULT_IMPL | HA_MRR_SORTED)))
- {
- /*
- During normal execution of a query, multi_range_read_init() is
- called to initialize MRR. If HA_MRR_SORTED is set at this point,
- multi_range_read_init() for any native MRR implementation will
- revert to default MRR because they cannot produce sorted output
- currently.
- Calling multi_range_read_init() can potentially be costly, so it
- is not done when executing an EXPLAIN. We therefore make the
- assumption that HA_MRR_SORTED means no MRR. If some MRR native
- implementation will support sorted output in the future, a
- function "bool mrr_supports_sorted()" should be added in the
- handler.
- */
- str_extra.append(STRING_WITH_LEN("; Using MRR"));
- }
- if (need_sort)
- str_extra.append(STRING_WITH_LEN("; Using filesort"));
+ explain_extra_common(select, NULL, quick_type, keyno, need_sort, &str_extra);
/* Skip initial "; "*/
const char *str= str_extra.ptr();
@@ -1207,8 +1149,7 @@ bool Explain_table::explain_extra()
str += 2;
len -= 2;
}
- col_extra= new Item_string(str, len, cs);
- return col_extra == NULL;
+ return col_extra.set(thd, str, len);
}
@@ -1244,12 +1185,25 @@ protected:
for multiple invocations, so result_state bitmap guards data interceptor
object from method re-invocation.
*/
- enum result_state_enum {
- SELECT_RESULT_PREPARED = 0x01, // 1st bit set: prepare() is complete
- SELECT_RESULT_PREPARED2 = 0x02, // 2nd bit set: prepare2() is complete
- SELECT_RESULT_INITIALIZED = 0x04 // 3rd bit set: initialize_tables() done
- };
- int result_state; ///< bitmap of result_state_enum bits
+ enum result_state_enum {
+ SELECT_RESULT_NONE = 0x00, ///< initial value
+ SELECT_RESULT_PREPARED = 0x01, ///< 1st bit set: prepare() is done
+ SELECT_RESULT_PREPARED2 = 0x02, ///< 2nd bit set: prepare2() is done
+ SELECT_RESULT_INITIALIZED = 0x04 ///< 3rd bit set: initialize_tables() done
+ } result_state; ///< bitmap of result_state_enum bits
+
+ /**
+ Binary OR operator for result_state_enum to return result_state_enum
+ without casts
+
+ The "friend" modifier is to declare a "static" operator since "static" is
+ prohibited there.
+ */
+ friend result_state_enum operator|(result_state_enum x, result_state_enum y)
+ {
+ return static_cast<result_state_enum>(static_cast<int>(x) |
+ static_cast<int>(y));
+ }
/**
Pointer to underlying insert_select, multi_update or multi_delete object
@@ -1257,35 +1211,32 @@ protected:
select_result_interceptor *interceptor;
public:
- explain_send(select_result_interceptor *interceptor_arg)
- : result_state(0), interceptor(interceptor_arg)
+ explain_send(select_result_interceptor *interceptor_arg)
+ : result_state(SELECT_RESULT_NONE), interceptor(interceptor_arg)
{}
protected:
virtual int prepare(List<Item> &list, SELECT_LEX_UNIT *u)
{
if (result_state & SELECT_RESULT_PREPARED)
- return FALSE;
- else
- result_state|= SELECT_RESULT_PREPARED;
+ return false;
+ result_state= result_state|SELECT_RESULT_PREPARED;
return select_send::prepare(list, u) || interceptor->prepare(list, u);
}
virtual int prepare2(void)
{
if (result_state & SELECT_RESULT_PREPARED2)
- return FALSE;
- else
- result_state|= SELECT_RESULT_PREPARED2;
+ return false;
+ result_state= result_state|SELECT_RESULT_PREPARED2;
return select_send::prepare2() || interceptor->prepare2();
}
virtual bool initialize_tables(JOIN *join)
{
if (result_state & SELECT_RESULT_INITIALIZED)
- return FALSE;
- else
- result_state|= SELECT_RESULT_INITIALIZED;
+ return false;
+ result_state= result_state|SELECT_RESULT_INITIALIZED;
return select_send::initialize_tables(join) ||
interceptor->initialize_tables(join);
}
@@ -1304,47 +1255,54 @@ protected:
/**
- Send a messages as an "extra" column value
+ Send a message as an "extra" column value
This function forms the 1st row of the QEP output with a simple text message.
This is useful to explain such trivial cases as "No tables used" etc.
-
- NOTE: Also this function explains the rest of QEP (subqueries or joined
+
+ @note Also this function explains the rest of QEP (subqueries or joined
tables if any).
+ @param thd current THD
+ @param join JOIN
@param message text message for the "extra" column.
- @param rows HA_POS_ERROR or a value for the "rows" column.
@returns
- @retval FALSE OK
- @retval TRUE Error
+ @retval false OK
+ @retval true Error
*/
-bool msg_describe(JOIN *join, const char *message)
+bool explain_msg(THD *thd, JOIN *join, const char *message)
{
- return Explain_msg(join, message).send();
+ DBUG_ENTER("explain_msg");
+ const bool ret= Explain_msg(thd, join, message).send();
+ DBUG_RETURN(ret);
}
/**
- Send a messages as an "extra" column value
+ Send a message as an "extra" column value
This function forms the 1st row of the QEP output with a simple text message.
This is useful to explain such trivial cases as "No tables used" etc.
-
- NOTE: Also this function explains the rest of QEP (subqueries if any).
+ @note Also this function explains the rest of QEP (subqueries if any).
+
+ @param thd current THD
@param message text message for the "extra" column.
@param rows HA_POS_ERROR or a value for the "rows" column.
@returns
- @retval FALSE OK
- @retval TRUE Error
+ @retval false OK
+ @retval true Error
*/
-bool msg_describe(const char *message, ha_rows rows)
+bool explain_msg(THD *thd, const char *message, ha_rows rows)
{
- return Explain_msg(message, rows).send();
+ DBUG_ENTER("explain_msg");
+ const bool ret= Explain_msg(thd, message, rows).send();
+ DBUG_RETURN(ret);
+
}
@@ -1357,6 +1315,7 @@ bool msg_describe(const char *message, h
thus we deal with this single table in a special way and then call
mysql_explain_union() for subqueries (if any).
+ @param thd current THD
@param table TABLE object to update/delete rows in the UPDATE/DELETE
query.
@param select SQL_SELECT object that represents quick access methods/
@@ -1364,49 +1323,55 @@ bool msg_describe(const char *message, h
@param key MAX_KEY or and index number of the key that was chosen to
access table data.
@param limit HA_POS_ERROR or LIMIT value.
- @param need_sort TRUE if it requires filesort() -- "Using filesort"
+ @param need_sort true if it requires filesort() -- "Using filesort"
string in the "extra" column.
@returns
- @retval FALSE OK
- @retval TRUE Error
+ @retval false OK
+ @retval true Error
*/
-bool table_describe(TABLE *table, SQL_SELECT *select, uint key, ha_rows limit,
- bool need_sort)
+bool explain_table_no_JOIN(THD *thd, TABLE *table, SQL_SELECT *select, uint key,
+ ha_rows limit, bool need_sort)
{
- return Explain_table(table, select, key, limit, need_sort).send();
+ DBUG_ENTER("explain_table_no_JOIN");
+ const bool ret= Explain_table(thd, table, select, key, limit,
+ need_sort).send();
+ DBUG_RETURN(ret);
}
/**
EXPLAIN handling for EXPLAIN SELECT queries
- Send a description about what how the select will be done to the client
+ Send QEP to the client.
+ @param thd current THD
@param join JOIN
- @param need_tmp_table TRUE if it requires a temporary table --
+ @param need_tmp_table true if it requires a temporary table --
"Using temporary" string in the "extra" column.
- @param need_order TRUE if it requires filesort() -- "Using filesort"
+ @param need_order true if it requires filesort() -- "Using filesort"
string in the "extra" column.
- @param distinct TRUE if there is the DISTINCT clause (not optimized
+ @param distinct true if there is the DISTINCT clause (not optimized
out) -- "Distinct" string in the "extra" column.
@returns
- @retval FALSE OK
- @retval TRUE Error
+ @retval false OK
+ @retval true Error
*/
-bool select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+bool select_describe(THD *thd, JOIN *join, bool need_tmp_table, bool need_order,
bool distinct)
{
DBUG_ENTER("select_describe");
- DBUG_PRINT("info", ("Select 0x%lx, type %s",
- (ulong)join->select_lex, join->select_lex->type));
+ DBUG_PRINT("info", ("Select %p, type %s",
+ join->select_lex, join->select_lex->type));
+ bool ret;
if (join->select_lex == join->unit->fake_select_lex)
- DBUG_RETURN(Explain_union(join).send());
+ ret= Explain_union(thd, join).send();
else
- DBUG_RETURN(Explain_join(join, need_tmp_table, need_order, distinct).send());
+ ret= Explain_join(thd, join, need_tmp_table, need_order, distinct).send();
+ DBUG_RETURN(ret);
}
@@ -1418,25 +1383,28 @@ bool select_describe(JOIN *join, bool ne
UPDATE and DELETE queries) like mysql_select() does for SELECT queries in
the "describe" mode.
- NOTE: See table_describe() for single-table UPDATE/DELETE EXPLAIN handling.
+ @note See explain_table_no_JOIN() for single-table UPDATE/DELETE EXPLAIN
+ handling.
- NOTE: Unlike the mysql_select() function, explain_data_modification()
+ @note Unlike the mysql_select function, explain_data_modification_having_JOIN
calls abort_result_set() itself in the case of failure (OOM etc.)
- since explain_data_modification() uses internally created select_result
- stream.
+ since explain_data_modification_having_JOIN() uses internally created
+ select_result stream.
+ @param thd current THD
@param result pointer to select_insert, multi_delete or multi_update object:
the function uses it to call result->prepare(),
result->prepare2() and result->initialize_tables() only but
not to modify table data or to send a result to client.
@returns
- @retval FALSE OK
- @retval TRUE Error
+ @retval false OK
+ @retval true Error
*/
-bool explain_data_modification(select_result_interceptor *result)
+bool explain_data_modification_having_JOIN(THD *thd,
+ select_result_interceptor *result)
{
- THD *thd= current_thd;
+ DBUG_ENTER("explain_data_modification_having_JOIN");
explain_send explain(result);
bool res= thd->send_explain_fields(&explain) ||
mysql_explain_union(thd, &thd->lex->unit, &explain) ||
@@ -1445,6 +1413,6 @@ bool explain_data_modification(select_re
explain.abort_result_set();
else
explain.send_eof();
- return res;
+ DBUG_RETURN(res);
}
=== modified file 'sql/opt_explain.h'
--- a/sql/opt_explain.h 2011-03-30 16:48:25 +0000
+++ b/sql/opt_explain.h 2011-05-25 14:16:06 +0000
@@ -14,17 +14,24 @@
51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA */
-#ifndef SQL_EXPLAIN_INCLUDED
-#define SQL_EXPLAIN_INCLUDED
+#ifndef OPT_EXPLAIN_INCLUDED
+#define OPT_EXPLAIN_INCLUDED
-#include "sql_select.h"
+class JOIN;
+class select_result_interceptor;
+class SQL_SELECT;
+class TABLE;
+class THD;
-bool msg_describe(JOIN *join, const char *message);
-bool msg_describe(const char *message, ha_rows rows= HA_POS_ERROR);
-bool table_describe(TABLE *table, SQL_SELECT *select, uint key, ha_rows limit,
- bool need_sort);
-bool select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+#include "my_base.h"
+
+bool explain_msg(THD *thd, JOIN *join, const char *message);
+bool explain_msg(THD *thd, const char *message, ha_rows rows= HA_POS_ERROR);
+bool explain_table_no_JOIN(THD *thd, TABLE *table, SQL_SELECT *select, uint key,
+ ha_rows limit, bool need_sort);
+bool select_describe(THD *thd, JOIN *join, bool need_tmp_table, bool need_order,
bool distinct);
-bool explain_data_modification(select_result_interceptor *result);
+bool explain_data_modification_having_JOIN(THD *thd,
+ select_result_interceptor *result);
-#endif /* SQL_EXPLAIN_INCLUDED */
+#endif /* OPT_EXPLAIN_INCLUDED */
=== modified file 'sql/opt_range.h'
--- a/sql/opt_range.h 2011-03-30 16:48:25 +0000
+++ b/sql/opt_range.h 2011-05-25 14:16:06 +0000
@@ -1,4 +1,4 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2011-03-30 16:48:25 +0000
+++ b/sql/sql_class.h 2011-05-25 14:16:06 +0000
@@ -3112,7 +3112,7 @@ public:
virtual void cleanup();
void set_thd(THD *thd_arg) { thd= thd_arg; }
- /*
+ /**
If we execute EXPLAIN SELECT ... LIMIT (or any other EXPLAIN query)
we have to ignore LIMIT value sending EXPLAIN output rows since
LIMIT value belongs to the underlying query, not to the whole EXPLAIN.
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2011-03-30 16:48:25 +0000
+++ b/sql/sql_delete.cc 2011-05-25 14:16:06 +0000
@@ -62,6 +62,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *
bool reverse= FALSE;
bool skip_record;
bool need_sort= FALSE;
+ bool err= true;
ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
order_list->first : NULL);
uint usable_index= MAX_KEY;
@@ -149,10 +150,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (thd->lex->describe)
{
- bool err= msg_describe("Using delete_all_rows", maybe_deleted);
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_msg(thd, "Deleting all rows", maybe_deleted);
+ goto exit_without_my_ok;
}
DBUG_PRINT("debug", ("Trying to use delete_all_rows()"));
@@ -185,10 +184,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (thd->lex->describe)
{
- bool err= msg_describe("Impossible WHERE");
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_msg(thd, "Impossible WHERE");
+ goto exit_without_my_ok;
}
}
}
@@ -198,10 +195,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *
{ // No matching records
if (thd->lex->describe)
{
- bool err= msg_describe("No matching records");
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_msg(thd, "No matching records");
+ goto exit_without_my_ok;
}
free_underlaid_joins(thd, select_lex);
@@ -259,10 +254,9 @@ bool mysql_delete(THD *thd, TABLE_LIST *
if (thd->lex->describe)
{
- bool err= table_describe(table, select, usable_index, limit, need_sort);
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_table_no_JOIN(thd, table, select, usable_index,
+ limit, need_sort);
+ goto exit_without_my_ok;
}
if (options & OPTION_QUICK)
@@ -454,6 +448,11 @@ cleanup:
DBUG_PRINT("info",("%ld records deleted",(long) deleted));
}
DBUG_RETURN(thd->is_error() || thd->killed);
+
+exit_without_my_ok:
+ delete select;
+ free_underlaid_joins(thd, select_lex);
+ DBUG_RETURN((err || thd->is_error() || thd->killed) ? 1 : 0);
}
=== modified file 'sql/sql_insert.cc'
--- a/sql/sql_insert.cc 2011-03-30 16:48:25 +0000
+++ b/sql/sql_insert.cc 2011-05-25 14:16:06 +0000
@@ -658,6 +658,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
bool ignore)
{
int error, res;
+ int err= true;
bool transactional_table, joins_freed= FALSE;
bool changed;
bool was_insert_delayed= (table_list->lock_type == TL_WRITE_DELAYED);
@@ -730,7 +731,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
!ignore && (thd->variables.sql_mode &
(MODE_STRICT_TRANS_TABLES |
MODE_STRICT_ALL_TABLES))))
- goto abort;
+ goto exit_without_my_ok;
/* mysql_prepare_insert set table_list->table if it was not set */
table= table_list->table;
@@ -761,10 +762,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
if (values->elements != value_count)
{
my_error(ER_WRONG_VALUE_COUNT_ON_ROW, MYF(0), counter);
- goto abort;
+ goto exit_without_my_ok;
}
if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
- goto abort;
+ goto exit_without_my_ok;
}
its.rewind ();
@@ -774,14 +775,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
if (thd->lex->describe)
{
/*
- Obviously INSERT without the SELECT is not suitable for EXPLAIN, since we
- don't plan how read tables.
- So we simply send "No tables used" and stop execution here.
+ Send "No tables used" and stop execution here since
+ there is no SELECT to explain.
*/
- bool err= msg_describe("No tables used");
- free_underlaid_joins(thd, &thd->lex->select_lex);
- DBUG_RETURN(err);
+ err= explain_msg(thd, "No tables used");
+ goto exit_without_my_ok;
}
/*
@@ -811,7 +810,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
(info.handle_duplicates == DUP_UPDATE) &&
(table->next_number_field != NULL) &&
rpl_master_has_bug(active_mi->rli, 24432, TRUE, NULL, NULL))
- goto abort;
+ goto exit_without_my_ok;
#endif
error=0;
@@ -1085,7 +1084,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE);
if (error)
- goto abort;
+ goto exit_without_my_ok;
if (values_list.elements == 1 && (!(thd->variables.option_bits & OPTION_WARNINGS) ||
!thd->cuted_fields))
{
@@ -1113,7 +1112,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
thd->abort_on_warning= 0;
DBUG_RETURN(FALSE);
-abort:
+exit_without_my_ok:
#ifndef EMBEDDED_LIBRARY
if (lock_type == TL_WRITE_DELAYED)
end_delayed_insert(thd);
@@ -1123,7 +1122,7 @@ abort:
if (!joins_freed)
free_underlaid_joins(thd, &thd->lex->select_lex);
thd->abort_on_warning= 0;
- DBUG_RETURN(TRUE);
+ DBUG_RETURN(err || thd->is_error() || thd->killed);
}
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-03-30 16:48:25 +0000
+++ b/sql/sql_parse.cc 2011-05-25 14:16:06 +0000
@@ -2943,7 +2943,7 @@ end_with_restore_list:
lex->ignore)))
{
if (lex->describe)
- res= explain_data_modification(sel_result);
+ res= explain_data_modification_having_JOIN(thd, sel_result);
else
{
res= handle_select(thd, lex, sel_result, OPTION_SETUP_TABLES_DONE);
@@ -3026,7 +3026,7 @@ end_with_restore_list:
(del_result= new multi_delete(aux_tables, lex->table_count)))
{
if (lex->describe)
- res= explain_data_modification(del_result);
+ res= explain_data_modification_having_JOIN(thd, del_result);
else
{
res= mysql_select(thd, &select_lex->ref_pointer_array,
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-03-30 16:48:25 +0000
+++ b/sql/sql_select.cc 2011-05-25 14:16:06 +0000
@@ -2793,8 +2793,8 @@ JOIN::exec()
if (!tables_list && (tables || !select_lex->with_sum_func))
{ // Only test of functions
if (select_options & SELECT_DESCRIBE)
- msg_describe(this, zero_result_cause ? zero_result_cause
- : "No tables used");
+ explain_msg(thd, this, zero_result_cause ? zero_result_cause
+ : "No tables used");
else
{
if (result->send_result_set_metadata(*columns_list,
@@ -2889,11 +2889,11 @@ JOIN::exec()
order=0;
having= tmp_having;
if (tables)
- select_describe(this, need_tmp,
+ select_describe(thd, this, need_tmp,
order != 0 && !skip_sort_order,
select_distinct);
else
- msg_describe(this, "No tables used");
+ explain_msg(thd, this, "No tables used");
DBUG_VOID_RETURN;
}
@@ -11939,7 +11939,7 @@ return_zero_rows(JOIN *join, select_resu
if (select_options & SELECT_DESCRIBE)
{
- msg_describe(join, info);
+ explain_msg(join->thd, join, info);
DBUG_RETURN(0);
}
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2011-03-30 16:48:25 +0000
+++ b/sql/sql_update.cc 2011-05-25 14:16:06 +0000
@@ -267,6 +267,8 @@ int mysql_update(THD *thd,
uint used_index, dup_key_found;
bool need_sort= TRUE;
bool reverse= FALSE;
+ bool using_filesort;
+ bool err= true;
#ifndef NO_EMBEDDED_ACCESS_CHECKS
uint want_privilege;
#endif
@@ -396,9 +398,8 @@ int mysql_update(THD *thd,
{ // No matching records
if (thd->lex->describe)
{
- bool err= msg_describe("No matching records");
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_msg(thd, "Impossible WHERE");
+ goto exit_without_my_ok;
}
free_underlaid_joins(thd, select_lex);
@@ -414,6 +415,12 @@ int mysql_update(THD *thd,
if (error || !limit ||
(select && select->check_quick(thd, safe_update, limit)))
{
+ if (thd->lex->describe && !error && !thd->is_error())
+ {
+ err= explain_msg(thd, "Impossible WHERE");
+ goto exit_without_my_ok;
+ }
+
delete select;
free_underlaid_joins(thd, select_lex);
/*
@@ -427,13 +434,6 @@ int mysql_update(THD *thd,
{
DBUG_RETURN(1); // Error in where
}
- if (thd->lex->describe)
- {
- bool err= msg_describe("Impossible WHERE");
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
- }
my_ok(thd); // No matching records
DBUG_RETURN(0);
}
@@ -446,7 +446,7 @@ int mysql_update(THD *thd,
{
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
- goto err;
+ goto exit_without_my_ok;
}
}
init_ftfuncs(thd, select_lex, 1);
@@ -465,13 +465,12 @@ int mysql_update(THD *thd,
used_key_is_modified= is_key_used(table, used_index, table->write_set);
}
+ using_filesort= order && (need_sort||used_key_is_modified);
if (thd->lex->describe)
{
- bool err= table_describe(table, select, used_index, limit,
- order && (need_sort||used_key_is_modified));
- delete select;
- free_underlaid_joins(thd, select_lex);
- DBUG_RETURN(err);
+ err= explain_table_no_JOIN(thd, table, select, used_index,
+ limit, using_filesort);
+ goto exit_without_my_ok;
}
#ifdef WITH_PARTITION_STORAGE_ENGINE
@@ -493,7 +492,7 @@ int mysql_update(THD *thd,
}
/* note: We avoid sorting if we sort on the used index */
- if (order && (need_sort || used_key_is_modified))
+ if (using_filesort)
{
/*
Doing an ORDER BY; Let filesort find and sort the rows we are going
@@ -514,7 +513,7 @@ int mysql_update(THD *thd,
&examined_rows, &found_rows))
== HA_POS_ERROR)
{
- goto err;
+ goto exit_without_my_ok;
}
thd->inc_examined_row_count(examined_rows);
/*
@@ -535,11 +534,11 @@ int mysql_update(THD *thd,
IO_CACHE tempfile;
if (open_cached_file(&tempfile, mysql_tmpdir,TEMP_PREFIX,
DISK_BUFFER_SIZE, MYF(MY_WME)))
- goto err;
+ goto exit_without_my_ok;
/* If quick select is used, initialize it before retrieving rows. */
if (select && select->quick && select->quick->reset())
- goto err;
+ goto exit_without_my_ok;
table->file->try_semi_consistent_read(1);
/*
@@ -616,7 +615,7 @@ int mysql_update(THD *thd,
error=1; /* purecov: inspected */
select->file=tempfile; // Read row ptrs from this file
if (error >= 0)
- goto err;
+ goto exit_without_my_ok;
}
if (table->key_read)
table->restore_column_maps_after_mark_index();
@@ -626,7 +625,7 @@ int mysql_update(THD *thd,
table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
if (select && select->quick && select->quick->reset())
- goto err;
+ goto exit_without_my_ok;
table->file->try_semi_consistent_read(1);
init_read_record(&info, thd, table, select, 0, 1, FALSE);
@@ -935,12 +934,12 @@ int mysql_update(THD *thd,
*updated_return= updated;
DBUG_RETURN((error >= 0 || thd->is_error()) ? 1 : 0);
-err:
+exit_without_my_ok:
delete select;
free_underlaid_joins(thd, select_lex);
table->set_keyread(FALSE);
thd->abort_on_warning= 0;
- DBUG_RETURN(1);
+ DBUG_RETURN((err || thd->is_error() || thd->killed) ? 1 : 0);
}
/*
@@ -1363,7 +1362,7 @@ bool mysql_multi_update(THD *thd,
MODE_STRICT_ALL_TABLES));
if (thd->lex->describe)
- res= explain_data_modification(*result);
+ res= explain_data_modification_having_JOIN(thd, *result);
else
{
List<Item> total_list;
=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc 2011-03-17 17:39:31 +0000
+++ b/sql/sql_view.cc 2011-05-25 14:16:06 +0000
@@ -1277,8 +1277,14 @@ bool mysql_make_view(THD *thd, File_pars
underlying tables.
Skip this step if we are opening view for prelocking only.
*/
- if (!table->prelocking_placeholder &&
- (old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
+ if (!table->prelocking_placeholder && old_lex->describe &&
+ (old_lex->sql_command == SQLCOM_SELECT ||
+ old_lex->sql_command == SQLCOM_INSERT ||
+ old_lex->sql_command == SQLCOM_INSERT_SELECT ||
+ old_lex->sql_command == SQLCOM_UPDATE ||
+ old_lex->sql_command == SQLCOM_UPDATE_MULTI ||
+ old_lex->sql_command == SQLCOM_DELETE ||
+ old_lex->sql_command == SQLCOM_DELETE_MULTI))
{
if (check_table_access(thd, SELECT_ACL, view_tables, FALSE,
UINT_MAX, TRUE) &&
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2011-03-30 16:48:25 +0000
+++ b/sql/sql_yacc.yy 2011-05-25 14:16:06 +0000
@@ -11371,39 +11371,16 @@ describe:
opt_describe_column {}
| describe_command opt_extended_describe
{ Lex->describe|= DESCRIBE_NORMAL; }
+ explanable_command
+ { Lex->select_lex.options|= SELECT_DESCRIBE; }
+ ;
+
+explanable_command:
select
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
- | describe_command opt_extended_describe
- { Lex->describe|= DESCRIBE_NORMAL; }
- insert
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
- | describe_command opt_extended_describe
- { Lex->describe|= DESCRIBE_NORMAL; }
- replace
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
- | describe_command opt_extended_describe
- { Lex->describe|= DESCRIBE_NORMAL; }
- update
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
- | describe_command opt_extended_describe
- { Lex->describe|= DESCRIBE_NORMAL; }
- delete
- {
- LEX *lex=Lex;
- lex->select_lex.options|= SELECT_DESCRIBE;
- }
+ | insert
+ | replace
+ | update
+ | delete
;
describe_command:
Attachment: [text/bzr-bundle] bzr/gleb.shchepa@oracle.com-20110525141606-ucnxuzq1lo2h9uda.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (gleb.shchepa:3358) | Gleb Shchepa | 25 May |
