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