| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | 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 | ||
| View as plain text | |||
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. </para> + <para> + It is possible in some cases to execute statements that modify + data when <literal>EXPLAIN SELECT</literal> is used with a + subquery; for more information, see + <xref linkend="unnamed-views"/>. + </para> + <formalpara role="mnmas"> <title>MySQL Enterprise</title> 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. </para> + <para> + It is also possible under certain circumstances to modify + table data using <literal>EXPLAIN SELECT</literal>. 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 + <literal>t1</literal> and <literal>t2</literal> in database + <literal>d1</literal>, created as shown here: + +<programlisting> +mysql> <userinput>CREATE DATABASE d1;</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>USE d1;</userinput> +Database changed + +mysql> <userinput>CREATE TABLE t1 (c1 INT);</userinput> +Query OK, 0 rows affected (0.15 sec) + +mysql> <userinput>CREATE TABLE t2 (c1 INT);</userinput> +Query OK, 0 rows affected (0.08 sec) +</programlisting> + + Now we create a stored function <literal>f1</literal> which + modifies <literal>t2</literal>: + +<programlisting> +mysql> <userinput>DELIMITER //</userinput> +mysql> <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> +mysql> <userinput>BEGIN</userinput> +mysql> <userinput>INSERT INTO t2 VALUES (p1);</userinput> +mysql> <userinput>RETURN p1;</userinput> +mysql> <userinput>END //</userinput> +Query OK, 0 rows affected (0.01 sec) + +mysql> <userinput>DELIMITER ;</userinput> +</programlisting> + + Referencing the function directly in an EXPLAIN SELECT does + not have any affect on t2, as shown here: + +<programlisting> +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) + +mysql> <userinput>EXPLAIN SELECT f1(5);</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + This is because the <literal>SELECT</literal> statement did + not reference any tables, as can be seen in the + <literal>table</literal> and <literal>Extra</literal> columns + of the output. This is also true of the following nested + <literal>SELECT</literal>: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SHOW WARNINGS;</userinput> ++-------+------+------------------------------------------+ +| Level | Code | Message | ++-------+------+------------------------------------------+ +| Note | 1249 | Select 2 was reduced during optimization | ++-------+------+------------------------------------------+ +1 row in set (0.00 sec) + +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + However, if the outer <userinput>SELECT</userinput> references + any tables, then the optimizer executes the statement in the + subquery as well: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> ++------+ +| c1 | ++------+ +| 5 | ++------+ +1 row in set (0.00 sec) +</programlisting> + </para> + + <para> + This also means that an <literal>EXPLAIN SELECT</literal> + statement such as the one shown here may take a long time to + execute: + +<programlisting> +EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW()))); +</programlisting> + + This is because the + <function role="sql">BENCHMARK()</function> function is + executed once for each row in <literal>t1</literal>. + </para> + </section> <section id="subquery-errors"> 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. </para> + <para> + It is possible in some cases to execute statements that modify + data when <literal>EXPLAIN SELECT</literal> is used with a + subquery; for more information, see + <xref linkend="unnamed-views"/>. + </para> + <formalpara role="mnmas"> <title>MySQL Enterprise</title> 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. </para> + <para> + It is also possible under certain circumstances to modify + table data using <literal>EXPLAIN SELECT</literal>. 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 + <literal>t1</literal> and <literal>t2</literal> in database + <literal>d1</literal>, created as shown here: + +<programlisting> +mysql> <userinput>CREATE DATABASE d1;</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>USE d1;</userinput> +Database changed + +mysql> <userinput>CREATE TABLE t1 (c1 INT);</userinput> +Query OK, 0 rows affected (0.15 sec) + +mysql> <userinput>CREATE TABLE t2 (c1 INT);</userinput> +Query OK, 0 rows affected (0.08 sec) +</programlisting> + + Now we create a stored function <literal>f1</literal> which + modifies <literal>t2</literal>: + +<programlisting> +mysql> <userinput>DELIMITER //</userinput> +mysql> <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> +mysql> <userinput>BEGIN</userinput> +mysql> <userinput>INSERT INTO t2 VALUES (p1);</userinput> +mysql> <userinput>RETURN p1;</userinput> +mysql> <userinput>END //</userinput> +Query OK, 0 rows affected (0.01 sec) + +mysql> <userinput>DELIMITER ;</userinput> +</programlisting> + + Referencing the function directly in an EXPLAIN SELECT does + not have any affect on t2, as shown here: + +<programlisting> +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) + +mysql> <userinput>EXPLAIN SELECT f1(5);</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + This is because the <literal>SELECT</literal> statement did + not reference any tables, as can be seen in the + <literal>table</literal> and <literal>Extra</literal> columns + of the output. This is also true of the following nested + <literal>SELECT</literal>: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SHOW WARNINGS;</userinput> ++-------+------+------------------------------------------+ +| Level | Code | Message | ++-------+------+------------------------------------------+ +| Note | 1249 | Select 2 was reduced during optimization | ++-------+------+------------------------------------------+ +1 row in set (0.00 sec) + +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + However, if the outer <userinput>SELECT</userinput> references + any tables, then the optimizer executes the statement in the + subquery as well: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> ++------+ +| c1 | ++------+ +| 5 | ++------+ +1 row in set (0.00 sec) +</programlisting> + </para> + + <para> + This also means that an <literal>EXPLAIN SELECT</literal> + statement such as the one shown here may take a long time to + execute: + +<programlisting> +EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW()))); +</programlisting> + + This is because the + <function role="sql">BENCHMARK()</function> function is + executed once for each row in <literal>t1</literal>. + </para> + </section> <section id="subquery-errors"> 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. </para> + <para> + It is possible in some cases to execute statements that modify + data when <literal>EXPLAIN SELECT</literal> is used with a + subquery; for more information, see + <xref linkend="unnamed-views"/>. + </para> + <formalpara role="mnmas"> <title>MySQL Enterprise</title> 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. </para> + <para> + It is possible under certain circumstances to modify table + data using <literal>EXPLAIN SELECT</literal>. 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 + <literal>t1</literal> and <literal>t2</literal> in database + <literal>d1</literal>, created as shown here: + +<programlisting> +mysql> <userinput>CREATE DATABASE d1;</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>USE d1;</userinput> +Database changed + +mysql> <userinput>CREATE TABLE t1 (c1 INT);</userinput> +Query OK, 0 rows affected (0.15 sec) + +mysql> <userinput>CREATE TABLE t2 (c1 INT);</userinput> +Query OK, 0 rows affected (0.08 sec) +</programlisting> + + Now we create a stored function <literal>f1</literal> which + modifies <literal>t2</literal>: + +<programlisting> +mysql> <userinput>DELIMITER //</userinput> +mysql> <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> +mysql> <userinput>BEGIN</userinput> +mysql> <userinput>INSERT INTO t2 VALUES (p1);</userinput> +mysql> <userinput>RETURN p1;</userinput> +mysql> <userinput>END //</userinput> +Query OK, 0 rows affected (0.01 sec) + +mysql> <userinput>DELIMITER ;</userinput> +</programlisting> + + Referencing the function directly in an EXPLAIN SELECT does + not have any affect on t2, as shown here: + +<programlisting> +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) + +mysql> <userinput>EXPLAIN SELECT f1(5);</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + This is because the <literal>SELECT</literal> statement did + not reference any tables, as can be seen in the + <literal>table</literal> and <literal>Extra</literal> columns + of the output. This is also true of the following nested + <literal>SELECT</literal>: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SHOW WARNINGS;</userinput> ++-------+------+------------------------------------------+ +| Level | Code | Message | ++-------+------+------------------------------------------+ +| Note | 1249 | Select 2 was reduced during optimization | ++-------+------+------------------------------------------+ +1 row in set (0.00 sec) + +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + However, if the outer <userinput>SELECT</userinput> references + any tables, then the optimizer executes the statement in the + subquery as well: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> ++------+ +| c1 | ++------+ +| 5 | ++------+ +1 row in set (0.00 sec) +</programlisting> + </para> + + <para> + This also means that an <literal>EXPLAIN SELECT</literal> + statement such as the one shown here may take a long time to + execute: + +<programlisting> +EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW()))); +</programlisting> + + This is because the + <function role="sql">BENCHMARK()</function> function is + executed once for each row in <literal>t1</literal>. + </para> + </section> <section id="subquery-errors"> 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. </para> + <para> + It is possible in some cases to execute statements that modify + data when <literal>EXPLAIN SELECT</literal> is used with a + subquery; for more information, see + <xref linkend="unnamed-views"/>. + </para> + <formalpara role="mnmas"> <title>MySQL Enterprise</title> 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. </para> + <para> + It is possible under certain circumstances to modify table + data using <literal>EXPLAIN SELECT</literal>. 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 + <literal>t1</literal> and <literal>t2</literal> in database + <literal>d1</literal>, created as shown here: + +<programlisting> +mysql> <userinput>CREATE DATABASE d1;</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>USE d1;</userinput> +Database changed + +mysql> <userinput>CREATE TABLE t1 (c1 INT);</userinput> +Query OK, 0 rows affected (0.15 sec) + +mysql> <userinput>CREATE TABLE t2 (c1 INT);</userinput> +Query OK, 0 rows affected (0.08 sec) +</programlisting> + + Now we create a stored function <literal>f1</literal> which + modifies <literal>t2</literal>: + +<programlisting> +mysql> <userinput>DELIMITER //</userinput> +mysql> <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> +mysql> <userinput>BEGIN</userinput> +mysql> <userinput>INSERT INTO t2 VALUES (p1);</userinput> +mysql> <userinput>RETURN p1;</userinput> +mysql> <userinput>END //</userinput> +Query OK, 0 rows affected (0.01 sec) + +mysql> <userinput>DELIMITER ;</userinput> +</programlisting> + + Referencing the function directly in an EXPLAIN SELECT does + not have any affect on t2, as shown here: + +<programlisting> +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) + +mysql> <userinput>EXPLAIN SELECT f1(5);</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + This is because the <literal>SELECT</literal> statement did + not reference any tables, as can be seen in the + <literal>table</literal> and <literal>Extra</literal> columns + of the output. This is also true of the following nested + <literal>SELECT</literal>: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SHOW WARNINGS;</userinput> ++-------+------+------------------------------------------+ +| Level | Code | Message | ++-------+------+------------------------------------------+ +| Note | 1249 | Select 2 was reduced during optimization | ++-------+------+------------------------------------------+ +1 row in set (0.00 sec) + +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + However, if the outer <userinput>SELECT</userinput> references + any tables, then the optimizer executes the statement in the + subquery as well: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> ++------+ +| c1 | ++------+ +| 5 | ++------+ +1 row in set (0.00 sec) +</programlisting> + </para> + + <para> + This also means that an <literal>EXPLAIN SELECT</literal> + statement such as the one shown here may take a long time to + execute: + +<programlisting> +EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW()))); +</programlisting> + + This is because the + <function role="sql">BENCHMARK()</function> function is + executed once for each row in <literal>t1</literal>. + </para> + </section> <section id="subquery-errors"> 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. </para> + <para> + It is possible in some cases to execute statements that modify + data when <literal>EXPLAIN SELECT</literal> is used with a + subquery; for more information, see + <xref linkend="unnamed-views"/>. + </para> + <formalpara role="mnmas"> <title>MySQL Enterprise</title> 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. </para> + <para> + It is possible under certain circumstances to modify table + data using <literal>EXPLAIN SELECT</literal>. 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 + <literal>t1</literal> and <literal>t2</literal> in database + <literal>d1</literal>, created as shown here: + +<programlisting> +mysql> <userinput>CREATE DATABASE d1;</userinput> +Query OK, 1 row affected (0.00 sec) + +mysql> <userinput>USE d1;</userinput> +Database changed + +mysql> <userinput>CREATE TABLE t1 (c1 INT);</userinput> +Query OK, 0 rows affected (0.15 sec) + +mysql> <userinput>CREATE TABLE t2 (c1 INT);</userinput> +Query OK, 0 rows affected (0.08 sec) +</programlisting> + + Now we create a stored function <literal>f1</literal> which + modifies <literal>t2</literal>: + +<programlisting> +mysql> <userinput>DELIMITER //</userinput> +mysql> <userinput>CREATE FUNCTION f1(p1 INT) RETURNS INT</userinput> +mysql> <userinput>BEGIN</userinput> +mysql> <userinput>INSERT INTO t2 VALUES (p1);</userinput> +mysql> <userinput>RETURN p1;</userinput> +mysql> <userinput>END //</userinput> +Query OK, 0 rows affected (0.01 sec) + +mysql> <userinput>DELIMITER ;</userinput> +</programlisting> + + Referencing the function directly in an EXPLAIN SELECT does + not have any affect on t2, as shown here: + +<programlisting> +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) + +mysql> <userinput>EXPLAIN SELECT f1(5);</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + This is because the <literal>SELECT</literal> statement did + not reference any tables, as can be seen in the + <literal>table</literal> and <literal>Extra</literal> columns + of the output. This is also true of the following nested + <literal>SELECT</literal>: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+-------+------+---------------+------+---------+------+------+----------------+ +| 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> <userinput>SHOW WARNINGS;</userinput> ++-------+------+------------------------------------------+ +| Level | Code | Message | ++-------+------+------------------------------------------+ +| Note | 1249 | Select 2 was reduced during optimization | ++-------+------+------------------------------------------+ +1 row in set (0.00 sec) + +mysql> <userinput>SELECT * FROM t2;</userinput> +Empty set (0.00 sec) +</programlisting> + + However, if the outer <userinput>SELECT</userinput> references + any tables, then the optimizer executes the statement in the + subquery as well: + +<programlisting> +mysql> <userinput>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2;</userinput> ++----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+ +| 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> <userinput>SELECT * FROM t2;</userinput> ++------+ +| c1 | ++------+ +| 5 | ++------+ +1 row in set (0.00 sec) +</programlisting> + </para> + + <para> + This also means that an <literal>EXPLAIN SELECT</literal> + statement such as the one shown here may take a long time to + execute: + +<programlisting> +EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW()))); +</programlisting> + + This is because the + <function role="sql">BENCHMARK()</function> function is + executed once for each row in <literal>t1</literal>. + </para> + </section> <section id="subquery-errors"> Modified: trunk/refman-6.0/optimization.xml =================================================================== Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 110 bytes
| Thread | ||
|---|---|---|
| • 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 | jon | 8 Apr |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.998 sec. using MySQL 5.1.14-beta-log