Bug #91674 mysql use mariadb optimizer result sql is more effective。
Submitted: 17 Jul 2018 8:28 Modified: 25 Nov 2018 12:33
Reporter: 4 m Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.7.22 OS:Any
Assigned to: CPU Architecture:Any

[17 Jul 2018 8:28] 4 m
Description:
mysql> select count() from (SELECT i.FLOW_TYPE, f.EXAMPLE_ID, i.FLOW_TITLE, c.NAME, ft.LABEL, i.CREATE_DATE, f.CREATE_DATE AS fct, i.ID AS iid, f.UPDATE_BY, (SELECT f1.id FROM sys_exten_flow_task f1 WHERE f1.instance_id = f.INSTANCE_ID AND state = '1' ORDER BY f1.update_date DESC LIMIT 0, 1) AS pretaskid, CASE WHEN i.FLOW_DEFINE IS NULL THEN 'free' ELSE i.FLOW_DEFINE END AS fd, CASE WHEN f.TASK_DEFINE_ID IS NULL THEN 'free' ELSE f.TASK_DEFINE_ID END AS tdi, f.ID AS fid, f.ADD_SIGN_TASK, i.INSTANCE_VARIABLE, i.create_company_id, i.create_office_id, i.create_by, f.this_deal_man_id, f.state, f.is_del, dt.is_mobile, dt.is_Deal_Content, f.expire_Time, f.is_open FROM sys_exten_flow_task f, sys_exten_flow_instance i , sys_exten_flow_define_task dt, sys_user c, sys_dict ft WHERE i.ID =f.INSTANCE_ID AND i.CREATE_BY = c.ID AND i.FLOW_TYPE = ft.ID AND dt.id = f.task_define_id )a; +----------+ | count() | +----------+ | 3841309 | +----------+ 1 row in set (3 min 21.97 sec)

mysql> explain select count(*) from (SELECT i.FLOW_TYPE, f.EXAMPLE_ID, i.FLOW_TITLE, c.NAME, ft.LABEL, i.CREATE_DATE, f.CREATE_DATE AS fct, i.ID AS iid, f.UPDATE_BY, (SELECT f1.id FROM sys_exten_flow_task f1 WHERE f1.instance_id = f.INSTANCE_ID AND state = '1' ORDER BY f1.update_date DESC LIMIT 0, 1) AS pretaskid, CASE WHEN i.FLOW_DEFINE IS NULL THEN 'free' ELSE i.FLOW_DEFINE END AS fd, CASE WHEN f.TASK_DEFINE_ID IS NULL THEN 'free' ELSE f.TASK_DEFINE_ID END AS tdi, f.ID AS fid, f.ADD_SIGN_TASK, i.INSTANCE_VARIABLE, i.create_company_id, i.create_office_id, i.create_by, f.this_deal_man_id, f.state, f.is_del, dt.is_mobile, dt.is_Deal_Content, f.expire_Time, f.is_open FROM sys_exten_flow_task f, sys_exten_flow_instance i , sys_exten_flow_define_task dt, sys_user c, sys_dict ft WHERE i.ID =f.INSTANCE_ID AND i.CREATE_BY = c.ID AND i.FLOW_TYPE = ft.ID AND dt.id = f.task_define_id )a;

+----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 3640604 | 100.00 | NULL | | 2 | DERIVED | i | NULL | ALL | PRIMARY,CREATE_BY | NULL | NULL | NULL | 788059 | 100.00 | Using where | | 2 | DERIVED | ft | NULL | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.FLOW_TYPE | 1 | 100.00 | Using where | | 2 | DERIVED | c | NULL | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.CREATE_BY | 1 | 100.00 | NULL | | 2 | DERIVED | f | NULL | ref | INDEX_TASK_INSTANCE_ID,INDEX_TASK_TASK_DEFINE_ID | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.i.ID | 4 | 100.00 | Using index condition; Using where | | 2 | DERIVED | dt | NULL | eq_ref | PRIMARY | PRIMARY | 258 | scdxoa.f.TASK_DEFINE_ID | 1 | 100.00 | Using where | | 3 | DEPENDENT SUBQUERY | f1 | NULL | ref | INDEX_TASK_STATUS,INDEX_TASK_INSTANCE_ID,INDEX | INDEX | 408 | const,scdxoa.f.INSTANCE_ID | 3 | 100.00 | Using index condition; Using filesort | +----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+

7 rows in set, 2 warnings (0.01 sec)

MariaDB [scdxoa]> select count() from (SELECT i.FLOW_TYPE, f.EXAMPLE_ID, i.FLOW_TITLE, c.NAME, ft.LABEL, i.CREATE_DATE, f.CREATE_DATE AS fct, i.ID AS iid, f.UPDATE_BY, (SELECT f1.id FROM sys_exten_flow_task f1 WHERE f1.instance_id = f.INSTANCE_ID AND state = '1' ORDER BY f1.update_date DESC LIMIT 0, 1) AS pretaskid, CASE WHEN i.FLOW_DEFINE IS NULL THEN 'free' ELSE i.FLOW_DEFINE END AS fd, CASE WHEN f.TASK_DEFINE_ID IS NULL THEN 'free' ELSE f.TASK_DEFINE_ID END AS tdi, f.ID AS fid, f.ADD_SIGN_TASK, i.INSTANCE_VARIABLE, i.create_company_id, i.create_office_id, i.create_by, f.this_deal_man_id, f.state, f.is_del, dt.is_mobile, dt.is_Deal_Content, f.expire_Time, f.is_open FROM sys_exten_flow_task f, sys_exten_flow_instance i , sys_exten_flow_define_task dt, sys_user c, sys_dict ft WHERE i.ID =f.INSTANCE_ID AND i.CREATE_BY = c.ID AND i.FLOW_TYPE = ft.ID AND dt.id = f.task_define_id )a; +----------+ | count() | +----------+ | 3912445 | +----------+ 1 row in set (56.54 sec)

MariaDB [scdxoa]> explain extended select count(*) from (SELECT i.FLOW_TYPE, f.EXAMPLE_ID, i.FLOW_TITLE, c.NAME, ft.LABEL, i.CREATE_DATE, f.CREATE_DATE AS fct, i.ID AS iid, f.UPDATE_BY, (SELECT f1.id FROM sys_exten_flow_task f1 WHERE f1.instance_id = f.INSTANCE_ID AND state = '1' ORDER BY f1.update_date DESC LIMIT 0, 1) AS pretaskid, CASE WHEN i.FLOW_DEFINE IS NULL THEN 'free' ELSE i.FLOW_DEFINE END AS fd, CASE WHEN f.TASK_DEFINE_ID IS NULL THEN 'free' ELSE f.TASK_DEFINE_ID END AS tdi, f.ID AS fid, f.ADD_SIGN_TASK, i.INSTANCE_VARIABLE, i.create_company_id, i.create_office_id, i.create_by, f.this_deal_man_id, f.state, f.is_del, dt.is_mobile, dt.is_Deal_Content, f.expire_Time, f.is_open FROM sys_exten_flow_task f, sys_exten_flow_instance i , sys_exten_flow_define_task dt, sys_user c, sys_dict ft WHERE i.ID =f.INSTANCE_ID AND i.CREATE_BY = c.ID AND i.FLOW_TYPE = ft.ID AND dt.id = f.task_define_id )a;

+------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+ | 1 | PRIMARY | c | index | PRIMARY,ID | group_login_name | 205 | NULL | 23047 | 100.00 | Using index | | 1 | PRIMARY | i | ref | PRIMARY,CREATE_BY | CREATE_BY | 259 | scdxoa.c.ID | 15 | 100.00 | | | 1 | PRIMARY | ft | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.FLOW_TYPE | 1 | 100.00 | Using where; Using index | | 1 | PRIMARY | f | ref | INDEX_TASK_INSTANCE_ID,INDEX_TASK_TASK_DEFINE_ID | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.i.ID | 2 | 100.00 | Using index condition; Using where | | 1 | PRIMARY | dt | eq_ref | PRIMARY | PRIMARY | 258 | scdxoa.f.TASK_DEFINE_ID | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT SUBQUERY | f1 | ref | INDEX_TASK_STATUS,INDEX_TASK_INSTANCE_ID,INDEX | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.f.INSTANCE_ID | 2 | 100.00 | Using where; Using filesort | +------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+

6 rows in set, 2 warnings (0.00 sec)

and I also try to execute

I also try to let Mysql to execute the result sql from mariadb optimized.

mysql> select count(0) AS "count()" from "scdxoa"."sys_exten_flow_task" "f" join "scdxoa"."sys_exten_flow_instance" "i" join "scdxoa"."sys_exten_flow_define_task" "dt" join "scdxoa"."sys_user" "c" join "scdxoa"."sys_dict" "ft" where (("scdxoa"."i"."CREATE_BY" = "scdxoa"."c"."ID") and ("scdxoa"."i"."ID" = "scdxoa"."f"."INSTANCE_ID") and ("scdxoa"."i"."FLOW_TYPE" = "scdxoa"."ft"."ID") and ("scdxoa"."dt"."ID" = "scdxoa"."f"."TASK_DEFINE_ID")); +----------+ | count() | +----------+ | 3841309 | +----------+ 1 row in set (21.83 sec)

How to repeat:
i am sorry
[25 Oct 2018 12:33] MySQL Verification Team
HI,

In order to process this feature request, we need to have all the tables, from these queries, with all of their data. This is because we can not process even a feature request unless we know the reasons behind choosing this part over another.

But, we need even more feedback from you.

Since we are not processing requests on other products, it would be necessary also to have extended EXPLAIN of the optimiser and the optimiser trace.

Last, but not least, have you tried running these queries with our latest 8.0.13 version. If you have not tried, please do .....
[26 Nov 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".