+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 3.216551367 }, "query_block": { "select_id": 1, "cost": 72055.92198, "r_loops": 1, "r_total_time_ms": 43.73082984, "nested_loop": [ { "table": { "table_name": "OUTR2", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "r_loops": 1, "rows": 70, "r_rows": 70, "cost": 1000.04019, "r_table_time_ms": 0.032557412, "r_other_time_ms": 17.89492853, "r_engine_stats": {}, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "OUTR1", "access_type": "ALL", "loops": 70, "r_loops": 70, "rows": 70, "r_rows": 70, "cost": 70002.81328, "r_table_time_ms": 4.928113997, "r_other_time_ms": 16.98506141, "r_engine_stats": {}, "filtered": 100, "r_total_filtered": 1.857142857, "attached_condition": "trigcond((OUTR1.col_int_key,(subquery#2))) and trigcond(OUTR1.col_int_nokey = OUTR2.col_int_key)", "r_filtered": 1.857142857 } }, { "range-checked-for-each-record": { "keys": ["k_int"], "r_keys": { "full_scan": 0, "index_merge": 0, "range": { "k_int": 50 } }, "table": { "table_name": "OUTR3", "access_type": "ALL", "possible_keys": ["k_int"], "loops": 4900, "r_loops": 50, "rows": 70, "r_rows": 53.18, "cost": 1053.068509, "r_engine_stats": {}, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } } ], "subqueries": [ { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 7, "r_rows": 0.285714286, "query_specifications": [ { "query_block": { "select_id": 2, "cost": 0.031379482, "r_loops": 7, "r_total_time_ms": 1.170767388, "nested_loop": [ { "table": { "table_name": "INNR1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["pk"], "ref": ["func"], "loops": 1, "r_loops": 7, "rows": 1, "r_rows": 1, "cost": 0.031379482, "r_table_time_ms": 1.140081545, "r_other_time_ms": 0.024060072, "r_engine_stats": {}, "filtered": 100, "r_total_filtered": 14.28571429, "attached_condition": "INNR1.col_int_nokey <= 1 or INNR1.pk <= 4", "r_filtered": 14.28571429 } } ] } }, { "query_block": { "select_id": 3, "operation": "UNION", "cost": 1000.04019, "r_loops": 7, "r_total_time_ms": 1.968434029, "having_condition": "count(0) >= 2 and NULL = (INNR1.pk)", "temporary_table": { "nested_loop": [ { "table": { "table_name": "INNR1", "access_type": "ALL", "loops": 1, "r_loops": 7, "rows": 70, "r_rows": 70, "cost": 1000.04019, "r_table_time_ms": 0.118242773, "r_other_time_ms": 0.280667833, "r_engine_stats": {}, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } ] } } } ] } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.050 sec) +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | 1 | PRIMARY | OUTR2 | ALL | PRIMARY | NULL | NULL | NULL | 70 | | | 1 | PRIMARY | OUTR1 | ALL | NULL | NULL | NULL | NULL | 64 | Using where | | 1 | PRIMARY | OUTR3 | ALL | k_int | NULL | NULL | NULL | 64 | Range checked for each record (index map: 0x4) | | 2 | DEPENDENT SUBQUERY | INNR1 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | 3 | DEPENDENT UNION | INNR1 | ALL | NULL | NULL | NULL | NULL | 70 | Using temporary | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ 6 rows in set (0.004 sec) +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | 1 | PRIMARY | OUTR2 | ALL | PRIMARY | NULL | NULL | NULL | 70 | | | 1 | PRIMARY | OUTR1 | ALL | NULL | NULL | NULL | NULL | 70 | Using where | | 1 | PRIMARY | OUTR3 | ALL | k_int | NULL | NULL | NULL | 70 | Range checked for each record (index map: 0x4) | | 2 | DEPENDENT SUBQUERY | INNR1 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | 3 | DEPENDENT UNION | INNR1 | ALL | NULL | NULL | NULL | NULL | 70 | Using temporary | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ 6 rows in set (0.006 sec) +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ | 1 | PRIMARY | OUTR2 | ALL | PRIMARY | NULL | NULL | NULL | 100 | | | 1 | PRIMARY | OUTR1 | ALL | NULL | NULL | NULL | NULL | 100 | Using where | | 1 | PRIMARY | OUTR3 | ALL | k_int | NULL | NULL | NULL | 100 | Range checked for each record (index map: 0x4) | | 2 | DEPENDENT SUBQUERY | INNR1 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where | | 3 | DEPENDENT UNION | INNR1 | ALL | NULL | NULL | NULL | NULL | 100 | Using temporary | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------------+------------+--------+---------------+---------+---------+------+------+------------------------------------------------+ 6 rows in set (0.008 sec)