MySQL Bugs: #59203: Serious optimizer failure when query is encapsulated in additional SELECT/INSERT

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

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;

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;

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)

Also in 5.0.91:

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

What is observed is that with the slow query, a large temp table gets created.

Similar issue for InnoDB - verified on 5.0.91 and 5.5.8

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)

Customer's use case is not an additional outer SELECT but an INSERT instead.

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)
============

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)
===========

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)]
===================

We are seeing a similar issue with our production system. Vote+1..

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.