Bug #82725 Extremely slow query when optimizer_switch='derived_merge=ON'
Submitted: 25 Aug 2016 15:55 Modified: 14 Sep 2016 6:26
Reporter: Zaihua Ji Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.7.14, 5.7.15 OS:CentOS (Linux rda-db-rep.ucar.edu 3.10.0-327.28.2.el7.x86_64 #1 SMP Wed Aug 3 11:11:39 UTC 2016 x86_64 x86_6)
Assigned to: CPU Architecture:Any
Tags: derived_merge, optimizer_switch

[25 Aug 2016 15:55] Zaihua Ji
Description:
As stated in the document, the outer query block is used where the 'derived_merge' is set to 'ON' (default setting) for the global variable optimizer_switch, but we encountered an extremely slow query for complicated left joins involving 3 tables:

select p.mssID,x.mssfile from (select mssID from mytable) as p 
left join 
(select concat(m.mssfile,if(!isnull(h.hfile),'..m..',''),ifnull(h.hfile,'')) as mssfile 
   from dssdb.mssfile as m 
   left join
   dssdb.htarfile as h on h.mssid = m.mssid where m.dsid = 'ds093.0' and m.type = 'P'   and 
          m.status = 'P') as x
on x.mssfile = p.mssID
where isnull(x.mssfile);

Explain shows:
1. defrived_merge = ON
+----+-------------+---------+------------+-------+-------------------------------------------+----------------+---------+---------------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys                             | key            | key_len | ref           | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------+---------+---------------+--------+----------+--------------------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL                                      | code           | 4       | NULL          | 175856 |   100.00 | Using index              |
|  1 | SIMPLE      | m       | NULL       | ref   | mssfile_idx_1,mssfile_idx_4,mssfile_idx_2 | mssfile_idx_4  | 12      | const         |   1206 |   100.00 | Using where              |
|  1 | SIMPLE      | h       | NULL       | ref   | htarfile_idx_1                            | htarfile_idx_1 | 4       | dssdb.m.mssid |    357 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------+---------+---------------+--------+----------+--------------------------+

2. derived_merge = OFF
+----+-------------+------------+------------+-------+-------------------------------------------+----------------+---------+-------------------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys                             | key            | key_len | ref               | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+-------------------------------------------+----------------+---------+-------------------+--------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL                                      | NULL           | NULL    | NULL              | 175856 |   100.00 | NULL                     |
|  1 | PRIMARY     | <derived3> | NULL       | ref   | <auto_key0>                               | <auto_key0>    | 264     | p.mssID           |    555 |   100.00 | Using where; Using index |
|  3 | DERIVED     | m          | NULL       | ref   | mssfile_idx_1,mssfile_idx_4,mssfile_idx_2 | mssfile_idx_2  | 16      | const,const,const | 273358 |   100.00 | NULL                     |
|  3 | DERIVED     | h          | NULL       | ref   | htarfile_idx_1                            | htarfile_idx_1 | 4       | dssdb.m.mssid     |    357 |   100.00 | Using index              |
|  2 | DERIVED     | mytable    | NULL       | index | NULL                                      | code           | 4       | NULL              | 175856 |   100.00 | Using index              |
+----+-------------+------------+------------+-------+-------------------------------------------+----------------+---------+-------------------+--------+----------+--------------------------+

Item 1 shows much better optimized than Item 2, but actually Item 2 takes 1 to 2 seconds while Item 1 seems hung for ever.

 

How to repeat:
I can dump the tables for you to download if you need them.

Suggested fix:
I suspect that the outer query bock does not work well for two levels of outer joins with derived tables.
[26 Aug 2016 16:47] Umesh Shastry
Hello Zaihua Ji,

Thank you for the report.
Please attach repeatable test case(create table statements for all the tables involved in the query, and data) to the bug report(you may want to mark it as private after attaching here).

Thanks,
Umesh
[12 Sep 2016 17:11] Zaihua Ji
I have uploaded mysql-bug-data-82725.tar.gz onto sftp.oracle.com:/support/incoming/ for 3 tables you can use to repeat the query:

select p.mssID,x.mssfile from (select mssID from mytable) as p left join (select concat(m.mssfile,if(!isnull(h.hfile),'..m..',''),ifnull(h.hfile,'')) as mssfile from dssdb.filetable as m left join htarfile as h on h.mssid = m.mssid where m.dsid = 'ds093.0' and m.type = 'P'   and           m.status = 'P') as x on x.mssfile = p.mssID where isnull(x.mssfile);

Please run the query with 

set session optimizer_switch="derived_merge=oo"

and 

set session optimizer_switch="derived_merge=off";;

and see the difference.

Thanks,

Hua
[12 Sep 2016 17:13] Zaihua Ji
Correction:
set session optimizer_switch="derived_merge=oo";

should be

set session optimizer_switch="derived_merge=on";
[14 Sep 2016 6:26] Umesh Shastry
Thank you for providing the requested test case.

-- 5.7.15

mysql> set session optimizer_switch="derived_merge=on";
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> explain select p.mssID,x.mssfile from (select mssID from mytable) as p left join (select concat(m.mssfile,if(!isnull(h.hfile),'..m..',''),ifnull(h.hfile,'')) as mssfile from test.filetable as m left join htarfile as h on h.mssid = m.mssid where m.dsid = 'ds093.0' and m.type = 'P'   and           m.status = 'P') as x on x.mssfile = p.mssID where isnull(x.mssfile);
+----+-------------+---------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys  | key            | key_len | ref          | rows   | filtered | Extra                    |
+----+-------------+---------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
|  1 | SIMPLE      | mytable | NULL       | index | NULL           | code           | 4       | NULL         | 155644 |   100.00 | Using index              |
|  1 | SIMPLE      | m       | NULL       | ALL   | NULL           | NULL           | NULL    | NULL         | 404909 |   100.00 | Using where              |
|  1 | SIMPLE      | h       | NULL       | ref   | htarfile_idx_1 | htarfile_idx_1 | 4       | test.m.mssid |    185 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> set session optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.00 sec)

mysql> explain select p.mssID,x.mssfile from (select mssID from mytable) as p left join (select concat(m.mssfile,if(!isnull(h.hfile),'..m..',''),ifnull(h.hfile,'')) as mssfile from test.filetable as m left join htarfile as h on h.mssid = m.mssid where m.dsid = 'ds093.0' and m.type = 'P'   and           m.status = 'P') as x on x.mssfile = p.mssID where isnull(x.mssfile);
+----+-------------+------------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref          | rows   | filtered | Extra                    |
+----+-------------+------------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL           | NULL           | NULL    | NULL         | 155644 |   100.00 | NULL                     |
|  1 | PRIMARY     | <derived3> | NULL       | ref   | <auto_key0>    | <auto_key0>    | 264     | p.mssID      |     10 |   100.00 | Using where; Using index |
|  3 | DERIVED     | m          | NULL       | ALL   | NULL           | NULL           | NULL    | NULL         | 404909 |     0.10 | Using where              |
|  3 | DERIVED     | h          | NULL       | ref   | htarfile_idx_1 | htarfile_idx_1 | 4       | test.m.mssid |    185 |   100.00 | Using index              |
|  2 | DERIVED     | mytable    | NULL       | index | NULL           | code           | 4       | NULL         | 155644 |   100.00 | Using index              |
+----+-------------+------------+------------+-------+----------------+----------------+---------+--------------+--------+----------+--------------------------+
5 rows in set, 1 warning (0.00 sec)