I have a rather complex query, using many joins and a not in clause. I've tailored this query using the MySql console to be optimised, and I'm sure there is no DEPENDEND SUBQUERY in the execution plan. But, for my surprise, when I execute the exact same query in the same MySql instance, using the Node and Python clients, the query is much slower. Using the explain statement through such clients, two DEPENDENT SUBQUERY show up. How come the very same query is successfully optimised when issued directly in the console, but not through such clients? How can I debug this issue?
Edit: requested details.
The output from SELECT @@optimizer_switch is the same, from the console and the python client:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
The execution plan from the MySql console:
+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+
| 1 | PRIMARY | c | NULL | ALL | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL | NULL | NULL | 1912779 | 5.00 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 956388 | 1.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | c | NULL | ALL | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL | NULL | NULL | 1912779 | 50.00 | Using where; Using temporary; Using filesort |
| 4 | UNION | c | NULL | ALL | NULL | NULL | NULL | NULL | 1912779 | 10.00 | Using where |
| 4 | UNION | <derived7> | NULL | ALL | NULL | NULL | NULL | NULL | 956388 | 1.00 | Using where; Using join buffer (Block Nested Loop) |
| 4 | UNION | a | NULL | ref | idx_accounts_domain | idx_accounts_domain | 202 | func | 26 | 100.00 | Using where; Using index |
| 9 | SUBQUERY | c | NULL | ALL | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL | NULL | NULL | 1912779 | 5.00 | Using where |
| 9 | SUBQUERY | <derived11> | NULL | ALL | NULL | NULL | NULL | NULL | 956388 | 1.00 | Using where; Using join buffer (Block Nested Loop) |
| 11 | DERIVED | c | NULL | ALL | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL | NULL | NULL | 1912779 | 50.00 | Using where; Using temporary; Using filesort |
| 7 | DERIVED | c | NULL | ALL | config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key | NULL | NULL | NULL | 1912779 | 50.00 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------------+------+---------------------------------------------------------------------------+---------------------+---------+------+---------+----------+----------------------------------------------------+
The execution plan, from the python client:
[
(1, 'PRIMARY', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 5.0, 'Using where'),
(1, 'PRIMARY', '<derived2>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
(2, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort'),
(4, 'UNION', 'c', None, 'ALL', None, None, None, None, 1912779, 10.0, 'Using where'),
(4, 'UNION', '<derived7>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
(4, 'UNION', 'a', None, 'ref', 'idx_report_settings_domain', 'idx_report_settings_domain', '202', 'func', 26, 100.0, 'Using where; Using index'),
(9, 'DEPENDENT SUBQUERY', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 5.0, 'Using where'),
(9, 'DEPENDENT SUBQUERY', '<derived11>', None, 'ALL', None, None, None, None, 956388, 1.0, 'Using where; Using join buffer (Block Nested Loop)'),
(11, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort'),
(7, 'DERIVED', 'c', None, 'ALL', 'config_idx_path2,config_idx_path_key2,config_idx_path,config_idx_path_key', None, None, None, 1912779, 50.0, 'Using where; Using temporary; Using filesort')
]
The query is of the form:
(
<<account_report_settings>>
)
union all
(
select *
from (
<<inherited_report_settings>>
) as inherited
where inherited.account not in (
select direct.account
from (
<<account_report_settings>>
) as direct
)
)
where <<account_report_settings>> and <<inherited_report_settings>> are some rather long subqueries. I can provided the full query if necessary, but it's very complex and I don't know if it would be of much help.