| 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: | |
| 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 | ||
[26 Aug 2016 16:47]
MySQL Verification Team
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]
MySQL Verification Team
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)
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.