Why EXPLAIN runs forever

Sometimes you need to run EXPLAIN on long running queries. Most time EXPLAIN takes few seconds, but sometimes it looks like it executes query itself instead of using statistic.


Like in the example following:


mysql> explain select \* from (select sleep(10) as foo) bar;
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
2 rows in set (10.02 sec)


Look at the time: 10.02 sec for such easy query.


This happens because query in the subquery executes first, then executes whole statement. If rewrite query in a way what it does not use subquery EXPLAIN would work fast as usual:


mysql> explain select sleep(10) as foo;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 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.01 sec)


For not generic this will mean you should replace subquery with JOIN.

Comments:

Same symptom described here:
http://openquery.com/blog/innodb-lock-timeout-before-query-execution
Different cause trigger.

Posted by Arjen Lentz on May 18, 2009 at 06:05 AM MSD #

Yes, another interesting case.

Posted by Sveta Smirnova on May 18, 2009 at 06:23 AM MSD #

This is because MySQL is unable to determine the types of the columns for the derived table without executing it. So the query is executed as part of the pre-execution phase... specifically, as part of the open_and_lock tables operation. A minor hack could probably be easily implemented to not execute the derived table's query if the outer statement is an explain.

This problem did not appear for me when I grafted in table functions because I made sure that the table's columns are known before execution.

I believe that the optimizer team is looking to solve this issue by flattening the query by turning the derived table into a joined table and some of this work may already be in the 6.0 tree.

Posted by Antony T Curtis on May 18, 2009 at 01:46 PM MSD #

I forget who, but someone demonstrated that if you have a stored function that modifies data, you could actually run an explain that would change the database.

Scary!

Posted by Gavin Towey on May 18, 2009 at 02:33 PM MSD #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today