From: Date: April 8 2008 5:48pm
Subject: svn commit - mysqldoc@docsrva: r10430 - in trunk: it/refman-5.1 pt/refman-5.1 refman-4.1 refman-5.0 refman-5.1 refman-6.0
List-Archive: http://lists.mysql.com/commits/45073
Message-Id: <200804081548.m38FmW0N011608@docsrva.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Author: jstephens
Date: 2008-04-08 17:48:31 +0200 (Tue, 08 Apr 2008)
New Revision: 10430
Log:
EXPLAIN + subqueries can modify data
Fixes Docs Bug #31468
Modified:
trunk/it/refman-5.1/optimization.xml
trunk/it/refman-5.1/sql-syntax.xml
trunk/pt/refman-5.1/optimization.xml
trunk/pt/refman-5.1/sql-syntax.xml
trunk/refman-4.1/optimization.xml
trunk/refman-4.1/sql-syntax.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.0/sql-syntax.xml
trunk/refman-5.1/optimization.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-6.0/optimization.xml
Modified: trunk/it/refman-5.1/optimization.xml
===================================================================
--- trunk/it/refman-5.1/optimization.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/it/refman-5.1/optimization.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 642 bytes
@@ -2230,6 +2230,13 @@
clause.
+
+ It is possible in some cases to execute statements that modify
+ data when EXPLAIN SELECT is used with a
+ subquery; for more information, see
+ .
+
+
MySQL Enterprise
Modified: trunk/it/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/it/refman-5.1/sql-syntax.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/it/refman-5.1/sql-syntax.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5808 bytes
@@ -12568,6 +12568,129 @@
unless the subquery is executed.
+
+ It is also possible under certain circumstances to modify
+ table data using EXPLAIN SELECT. This can
+ occur if the outer query accesses any tables and an inner
+ query invokes a stored function that changes one or more rows
+ of a table. For example, suppose there are two tables
+ t1 and t2 in database
+ d1, created as shown here:
+
+
+mysql> CREATE DATABASE d1;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> USE d1;
+Database changed
+
+mysql> CREATE TABLE t1 (c1 INT);
+Query OK, 0 rows affected (0.15 sec)
+
+mysql> CREATE TABLE t2 (c1 INT);
+Query OK, 0 rows affected (0.08 sec)
+
+
+ Now we create a stored function f1 which
+ modifies t2:
+
+
+mysql> DELIMITER //
+mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
+mysql> BEGIN
+mysql> INSERT INTO t2 VALUES (p1);
+mysql> RETURN p1;
+mysql> END //
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> DELIMITER ;
+
+
+ Referencing the function directly in an EXPLAIN SELECT does
+ not have any affect on t2, as shown here:
+
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+mysql> EXPLAIN SELECT f1(5);
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 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 |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ This is because the SELECT statement did
+ not reference any tables, as can be seen in the
+ table and Extra columns
+ of the output. This is also true of the following nested
+ SELECT:
+
+
+mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql> SHOW WARNINGS;
++-------+------+------------------------------------------+
+| Level | Code | Message |
++-------+------+------------------------------------------+
+| Note | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ However, if the outer SELECT references
+ any tables, then the optimizer executes the statement in the
+ subquery as well:
+
+
+mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
+| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
++------+
+| c1 |
++------+
+| 5 |
++------+
+1 row in set (0.00 sec)
+
+
+
+
+ This also means that an EXPLAIN SELECT
+ statement such as the one shown here may take a long time to
+ execute:
+
+
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+
+
+ This is because the
+ BENCHMARK() function is
+ executed once for each row in t1.
+
+
Modified: trunk/pt/refman-5.1/optimization.xml
===================================================================
--- trunk/pt/refman-5.1/optimization.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/pt/refman-5.1/optimization.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 642 bytes
@@ -2230,6 +2230,13 @@
clause.
+
+ It is possible in some cases to execute statements that modify
+ data when EXPLAIN SELECT is used with a
+ subquery; for more information, see
+ .
+
+
MySQL Enterprise
Modified: trunk/pt/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/pt/refman-5.1/sql-syntax.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/pt/refman-5.1/sql-syntax.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5808 bytes
@@ -12568,6 +12568,129 @@
unless the subquery is executed.
+
+ It is also possible under certain circumstances to modify
+ table data using EXPLAIN SELECT. This can
+ occur if the outer query accesses any tables and an inner
+ query invokes a stored function that changes one or more rows
+ of a table. For example, suppose there are two tables
+ t1 and t2 in database
+ d1, created as shown here:
+
+
+mysql> CREATE DATABASE d1;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> USE d1;
+Database changed
+
+mysql> CREATE TABLE t1 (c1 INT);
+Query OK, 0 rows affected (0.15 sec)
+
+mysql> CREATE TABLE t2 (c1 INT);
+Query OK, 0 rows affected (0.08 sec)
+
+
+ Now we create a stored function f1 which
+ modifies t2:
+
+
+mysql> DELIMITER //
+mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
+mysql> BEGIN
+mysql> INSERT INTO t2 VALUES (p1);
+mysql> RETURN p1;
+mysql> END //
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> DELIMITER ;
+
+
+ Referencing the function directly in an EXPLAIN SELECT does
+ not have any affect on t2, as shown here:
+
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+mysql> EXPLAIN SELECT f1(5);
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 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 |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ This is because the SELECT statement did
+ not reference any tables, as can be seen in the
+ table and Extra columns
+ of the output. This is also true of the following nested
+ SELECT:
+
+
+mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql> SHOW WARNINGS;
++-------+------+------------------------------------------+
+| Level | Code | Message |
++-------+------+------------------------------------------+
+| Note | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ However, if the outer SELECT references
+ any tables, then the optimizer executes the statement in the
+ subquery as well:
+
+
+mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
+| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
++------+
+| c1 |
++------+
+| 5 |
++------+
+1 row in set (0.00 sec)
+
+
+
+
+ This also means that an EXPLAIN SELECT
+ statement such as the one shown here may take a long time to
+ execute:
+
+
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+
+
+ This is because the
+ BENCHMARK() function is
+ executed once for each row in t1.
+
+
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-4.1/optimization.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes
@@ -1811,6 +1811,13 @@
clause.
+
+ It is possible in some cases to execute statements that modify
+ data when EXPLAIN SELECT is used with a
+ subquery; for more information, see
+ .
+
+
MySQL Enterprise
Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-4.1/sql-syntax.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5792 bytes
@@ -9298,6 +9298,129 @@
unless the subquery is executed.
+
+ It is possible under certain circumstances to modify table
+ data using EXPLAIN SELECT. This can occur
+ if the outer query accesses any tables and an inner query
+ invokes a stored function that changes one or more rows of a
+ table. For example, suppose there are two tables
+ t1 and t2 in database
+ d1, created as shown here:
+
+
+mysql> CREATE DATABASE d1;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> USE d1;
+Database changed
+
+mysql> CREATE TABLE t1 (c1 INT);
+Query OK, 0 rows affected (0.15 sec)
+
+mysql> CREATE TABLE t2 (c1 INT);
+Query OK, 0 rows affected (0.08 sec)
+
+
+ Now we create a stored function f1 which
+ modifies t2:
+
+
+mysql> DELIMITER //
+mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
+mysql> BEGIN
+mysql> INSERT INTO t2 VALUES (p1);
+mysql> RETURN p1;
+mysql> END //
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> DELIMITER ;
+
+
+ Referencing the function directly in an EXPLAIN SELECT does
+ not have any affect on t2, as shown here:
+
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+mysql> EXPLAIN SELECT f1(5);
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 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 |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ This is because the SELECT statement did
+ not reference any tables, as can be seen in the
+ table and Extra columns
+ of the output. This is also true of the following nested
+ SELECT:
+
+
+mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql> SHOW WARNINGS;
++-------+------+------------------------------------------+
+| Level | Code | Message |
++-------+------+------------------------------------------+
+| Note | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ However, if the outer SELECT references
+ any tables, then the optimizer executes the statement in the
+ subquery as well:
+
+
+mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
+| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
++------+
+| c1 |
++------+
+| 5 |
++------+
+1 row in set (0.00 sec)
+
+
+
+
+ This also means that an EXPLAIN SELECT
+ statement such as the one shown here may take a long time to
+ execute:
+
+
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+
+
+ This is because the
+ BENCHMARK() function is
+ executed once for each row in t1.
+
+
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.0/optimization.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes
@@ -2090,6 +2090,13 @@
clause.
+
+ It is possible in some cases to execute statements that modify
+ data when EXPLAIN SELECT is used with a
+ subquery; for more information, see
+ .
+
+
MySQL Enterprise
Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.0/sql-syntax.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5794 bytes
@@ -10075,6 +10075,129 @@
unless the subquery is executed.
+
+ It is possible under certain circumstances to modify table
+ data using EXPLAIN SELECT. This can occur
+ if the outer query accesses any tables and an inner query
+ invokes a stored function that changes one or more rows of a
+ table. For example, suppose there are two tables
+ t1 and t2 in database
+ d1, created as shown here:
+
+
+mysql> CREATE DATABASE d1;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> USE d1;
+Database changed
+
+mysql> CREATE TABLE t1 (c1 INT);
+Query OK, 0 rows affected (0.15 sec)
+
+mysql> CREATE TABLE t2 (c1 INT);
+Query OK, 0 rows affected (0.08 sec)
+
+
+ Now we create a stored function f1 which
+ modifies t2:
+
+
+mysql> DELIMITER //
+mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
+mysql> BEGIN
+mysql> INSERT INTO t2 VALUES (p1);
+mysql> RETURN p1;
+mysql> END //
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> DELIMITER ;
+
+
+ Referencing the function directly in an EXPLAIN SELECT does
+ not have any affect on t2, as shown here:
+
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+mysql> EXPLAIN SELECT f1(5);
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 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 |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ This is because the SELECT statement did
+ not reference any tables, as can be seen in the
+ table and Extra columns
+ of the output. This is also true of the following nested
+ SELECT:
+
+
+mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql> SHOW WARNINGS;
++-------+------+------------------------------------------+
+| Level | Code | Message |
++-------+------+------------------------------------------+
+| Note | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ However, if the outer SELECT references
+ any tables, then the optimizer executes the statement in the
+ subquery as well:
+
+
+mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
+| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
++------+
+| c1 |
++------+
+| 5 |
++------+
+1 row in set (0.00 sec)
+
+
+
+
+ This also means that an EXPLAIN SELECT
+ statement such as the one shown here may take a long time to
+ execute:
+
+
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+
+
+ This is because the
+ BENCHMARK() function is
+ executed once for each row in t1.
+
+
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.1/optimization.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 7, Lines Deleted: 0; 633 bytes
@@ -2250,6 +2250,13 @@
clause.
+
+ It is possible in some cases to execute statements that modify
+ data when EXPLAIN SELECT is used with a
+ subquery; for more information, see
+ .
+
+
MySQL Enterprise
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2008-04-08 14:36:19 UTC (rev 10429)
+++ trunk/refman-5.1/sql-syntax.xml 2008-04-08 15:48:31 UTC (rev 10430)
Changed blocks: 1, Lines Added: 123, Lines Deleted: 0; 5794 bytes
@@ -12586,6 +12586,129 @@
unless the subquery is executed.
+
+ It is possible under certain circumstances to modify table
+ data using EXPLAIN SELECT. This can occur
+ if the outer query accesses any tables and an inner query
+ invokes a stored function that changes one or more rows of a
+ table. For example, suppose there are two tables
+ t1 and t2 in database
+ d1, created as shown here:
+
+
+mysql> CREATE DATABASE d1;
+Query OK, 1 row affected (0.00 sec)
+
+mysql> USE d1;
+Database changed
+
+mysql> CREATE TABLE t1 (c1 INT);
+Query OK, 0 rows affected (0.15 sec)
+
+mysql> CREATE TABLE t2 (c1 INT);
+Query OK, 0 rows affected (0.08 sec)
+
+
+ Now we create a stored function f1 which
+ modifies t2:
+
+
+mysql> DELIMITER //
+mysql> CREATE FUNCTION f1(p1 INT) RETURNS INT
+mysql> BEGIN
+mysql> INSERT INTO t2 VALUES (p1);
+mysql> RETURN p1;
+mysql> END //
+Query OK, 0 rows affected (0.01 sec)
+
+mysql> DELIMITER ;
+
+
+ Referencing the function directly in an EXPLAIN SELECT does
+ not have any affect on t2, as shown here:
+
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+mysql> EXPLAIN SELECT f1(5);
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 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 |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ This is because the SELECT statement did
+ not reference any tables, as can be seen in the
+ table and Extra columns
+ of the output. This is also true of the following nested
+ SELECT:
+
+
+mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+-------+------+---------------+------+---------+------+------+----------------+
+1 row in set, 1 warning (0.00 sec)
+
+mysql> SHOW WARNINGS;
++-------+------+------------------------------------------+
+| Level | Code | Message |
++-------+------+------------------------------------------+
+| Note | 1249 | Select 2 was reduced during optimization |
++-------+------+------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
+Empty set (0.00 sec)
+
+
+ However, if the outer SELECT references
+ any tables, then the optimizer executes the statement in the
+ subquery as well:
+
+
+mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+| 1 | PRIMARY | a1 | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
+| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+
+3 rows in set (0.00 sec)
+
+mysql> SELECT * FROM t2;
++------+
+| c1 |
++------+
+| 5 |
++------+
+1 row in set (0.00 sec)
+
+
+
+
+ This also means that an EXPLAIN SELECT
+ statement such as the one shown here may take a long time to
+ execute:
+
+
+EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));
+
+
+ This is because the
+ BENCHMARK() function is
+ executed once for each row in t1.
+
+
Modified: trunk/refman-6.0/optimization.xml
===================================================================
Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 110 bytes