Bug #59203 Serious optimizer failure when query is encapsulated in additional SELECT/INSERT
Submitted: 29 Dec 2010 4:38 Modified: 24 Feb 2015 16:10
Reporter: Roel Van de Paar Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.91,5.1.39,5.1.55,5.5.8 OS:Any
Assigned to: CPU Architecture:Any

[29 Dec 2010 4:38] Roel Van de Paar
Description:
Same query, but second one is not encapsulated in another SELECT():

*************************** 1. row ***************************
QUERY: SLOW
1 row in set (0.00 sec)

+--------+----+
| d      | id |
+--------+----+
| abcdef |  1 |
| abcdef |  2 |
| abcdef |  3 |
| abcdef |  4 |
| abcdef |  5 |
| abcdef |  6 |
| abcdef |  7 |
| abcdef |  8 |
| abcdef |  9 |
| abcdef | 10 |
+--------+----+
10 rows in set (16.01 sec)

*************************** 1. row ***************************
QUERY: FAST
1 row in set (0.98 sec)

+--------+----+
| d      | id |
+--------+----+
| abcdef |  1 |
| abcdef |  2 |
| abcdef |  3 |
| abcdef |  4 |
| abcdef |  5 |
| abcdef |  6 |
| abcdef |  7 |
| abcdef |  8 |
| abcdef |  9 |
| abcdef | 10 |
+--------+----+
10 rows in set (0.02 sec)

How to repeat:
DROP DATABASE IF EXISTS testi;
CREATE DATABASE testi;
USE testi;

CREATE TABLE "a" ("id" int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE "b" ("vc" varchar(96) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DELIMITER //
CREATE PROCEDURE setuplarge(IN numrows INT)
BEGIN
 DECLARE a INT;
 SET a = 1;
 WHILE (a < numrows) DO
  INSERT INTO a VALUES (a);
  INSERT INTO b VALUES ('abcdef');
  SET a=a+1; 
 END WHILE;
END;
//
DELIMITER ;
CALL setuplarge(2000); 

SELECT 'SLOW' AS 'QUERY'\G

SELECT * FROM
  (SELECT d, c.* FROM
    (SELECT * FROM(
      (SELECT id
       FROM a
       WHERE id >= 1
      )
    ) AS e
  ) AS c JOIN
    (
      (
        (SELECT vc AS d
         FROM b AS t 
        )
      ) AS f
    ) 
  ) AS z
LIMIT 10;

SELECT 'FAST' AS 'QUERY'\G

/* SELECT * FROM 
  (*/SELECT d, c.* FROM
    (SELECT * FROM(
      (SELECT id
       FROM a
       WHERE id >= 1
      )
    ) AS e
  ) AS c JOIN
    (
      (
        (SELECT vc AS d
         FROM b AS t 
        )
      ) AS f
    ) 
/*  ) AS z */
LIMIT 10;
[29 Dec 2010 4:40] Roel Van de Paar
Small testcase cleanup:

CREATE TABLE a (id int(10) unsigned NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE b (vc varchar(96) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[29 Dec 2010 4:42] Roel Van de Paar
Confirmed in 5.5.8:

Fast: 10 rows in set (22.30 sec)
Slow: 10 rows in set (0.00 sec)

And in 5.1.55:

Fast: 10 rows in set (16.01 sec)
Slow: 10 rows in set (0.02 sec)
[29 Dec 2010 4:45] Roel Van de Paar
Also in 5.0.91:

Slow: 10 rows in set (20.15 sec)
Fast: 10 rows in set (0.00 sec)
[29 Dec 2010 4:46] Roel Van de Paar
What is observed is that with the slow query, a large temp table gets created.
[29 Dec 2010 10:25] Roel Van de Paar
Similar issue for InnoDB - verified on 5.0.91 and 5.5.8
[29 Dec 2010 10:37] Roel Van de Paar
EXPLAIN's (note explain takes longer due to bug #44802)

|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
----------------------------------------------------------------------
|1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|3996001|              |
|2|DERIVED|<derived3>|ALL|NULL|NULL|NULL|NULL|1999|                 |
|2|DERIVED|<derived5>|ALL|NULL|NULL|NULL|NULL|1999|Using join buffer|
|5|DERIVED|t         |ALL|NULL|NULL|NULL|NULL|1999|                 |
|3|DERIVED|<derived4>|ALL|NULL|NULL|NULL|NULL|1999|                 |
|4|DERIVED|a         |ALL|NULL|NULL|NULL|NULL|1999|Using where      |
----------------------------------------------------------------------
QUERY: SLOW - 6 rows in set (18.26 sec)

|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
----------------------------------------------------------------------
|1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|1999|                 |
|1|PRIMARY|<derived4>|ALL|NULL|NULL|NULL|NULL|1999|Using join buffer|
|4|DERIVED|t         |ALL|NULL|NULL|NULL|NULL|1999|                 |
|2|DERIVED|<derived3>|ALL|NULL|NULL|NULL|NULL|1999|                 |
|3|DERIVED|a         |ALL|NULL|NULL|NULL|NULL|1999|Using where      |
----------------------------------------------------------------------
QUERY: FAST - 5 rows in set (0.00 sec)
[30 Dec 2010 0:42] Roel Van de Paar
Customer's use case is not an additional outer SELECT but an INSERT instead.
[30 Dec 2010 1:08] Roel Van de Paar
Minimal Testcase:

============
DROP DATABASE IF EXISTS testi;
CREATE DATABASE testi;
USE testi;

CREATE TABLE a (a int(10) unsigned NOT NULL) DEFAULT CHARSET=utf8;
CREATE TABLE b (b varchar(96)      NOT NULL) DEFAULT CHARSET=utf8;

DELIMITER //
CREATE PROCEDURE setuplarge()
BEGIN
 DECLARE a INT;
 SET a = 1;
 WHILE (a < 2000) DO
  INSERT INTO a VALUES (a);
  INSERT INTO b VALUES ('a');
  SET a=a+1; 
 END WHILE;
END;
//
DELIMITER ;
CALL setuplarge(); 

SELECT 'SLOW' AS 'QUERY'\G

SELECT * FROM (SELECT * FROM (SELECT * FROM ((SELECT * FROM a)) AS c) AS d JOIN (((SELECT * FROM b)) AS e)) AS f LIMIT 1;

SELECT 'FAST' AS 'QUERY'\G

               SELECT * FROM (SELECT * FROM ((SELECT * FROM a)) AS c) AS d JOIN (((SELECT * FROM b)) AS e)       LIMIT 1;
============

Output:

============
*************************** 1. row ***************************
QUERY: SLOW
1 row in set (0.00 sec)

+---+---+
| a | b |
+---+---+
| 1 | a |
+---+---+
1 row in set (16.27 sec)

*************************** 1. row ***************************
QUERY: FAST
1 row in set (0.00 sec)

+---+---+
| a | b |
+---+---+
| 1 | a |
+---+---+
1 row in set (0.01 sec)
============
[30 Dec 2010 1:13] Roel Van de Paar
Issue is less pronounced on latin1, though still there:

CREATE TABLE a (a int(10) unsigned NOT NULL) DEFAULT CHARSET=latin1;
CREATE TABLE b (b varchar(96)      NOT NULL) DEFAULT CHARSET=latin1;

===========
QUERY: SLOW

+---+---+
| a | b |
+---+---+
| 1 | a |
+---+---+
1 row in set (1.78 sec)

QUERY: FAST

+---+---+
| a | b |
+---+---+
| 1 | a |
+---+---+
1 row in set (0.01 sec)
===========
[31 Dec 2010 4:44] Roel Van de Paar
Interesting difference when LIMIT is removed/left:

Without LIMIT (just running the query, no the table generation):

===================
c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:34:00.27

c:\mysql558\bin>mysql -uroot -P558 < C:/mysql/bin/testcaseshortnolimit_fast.sql > out_fast.txt

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:34:04.85   [FAST: ~4.5 sec]

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:35:10.26

c:\mysql558\bin>mysql -uroot -P558 < C:/mysql/bin/testcaseshortnolimit_slow.sql > out_slow.txt

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:35:20.30   [SLOW: ~10 sec]
===================

With LIMIT (just running the query, no the table generation):

===================
c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:39:25.58

c:\mysql558\bin>mysql -uroot -P558 < C:/mysql/bin/testcaseshortnolimit_slowl.sql > out_slowl.txt

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:39:31.22   [SLOW: ~5.5 sec]

c:\mysql558\bin>mysql -uroot -P558 < C:/mysql/bin/testcaseshortnolimit_fastl.sql > out_fastl.txt

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:40:19.03

c:\mysql558\bin>mysql -uroot -P558 < C:/mysql/bin/testcaseshortnolimit_fastl.sql > out_fastl.txt

c:\mysql558\bin>echo.|time|grep "cur"
The current time is: 15:40:21.47   [FAST: ~1.5 sec (really 0 sec, just type delay)]
===================
[15 Feb 2011 1:22] Soichi Hayashi
We are seeing a similar issue with our production system. Vote+1..
[24 Feb 2015 16:10] Paul DuBois
Noted in 5.7.6 changelog.

The optimizer now minimizes differences in handling of views and
subqueries in the FROM clause. One effect of this is that subqueries
in the FROM clause, previously always materialized to an internal
temporary table, now may be merged into the outer query. This can
improve performance. Avoiding materialization also can enable the
optimizer to push down conditions to the subquery and produce a more
efficient execution plan.