MariaDB [federated]> analyze format=json DELETE FROM `OUTR1` USING ( -> -> ( -> -> `federated`.`B` `OUTR2` -> -> JOIN `federated`.`D` `OUTR3` -> -> ON (`federated`.`OUTR2`.`pk` > `federated`.`OUTR3`.`col_int_key`) -> -> ) -> -> LEFT JOIN `federated`.`D` `OUTR1` -> -> ON (`federated`.`OUTR1`.`col_int_nokey` = `federated`.`OUTR2`.`col_int_key`) -> -> ) -> -> WHERE -> -> `federated`.`OUTR1`.`col_int_key` IN ( -> -> SELECT -> -> `federated`.`INNR1`.`pk` -> -> FROM -> -> `federated`.`CC` `INNR1` -> -> WHERE -> -> `federated`.`INNR1`.`col_int_nokey` <= 1 -> -> OR `federated`.`INNR1`.`pk` <= 4 -> -> -> -> UNION -> -> -> -> SELECT -> -> `federated`.`INNR1`.`pk` -> -> FROM -> -> `federated`.`AA` `INNR1` -> -> GROUP BY -> -> `federated`.`INNR1`.`col_int_uniq` -> -> HAVING -> -> COUNT(0) >= 2 -> -> ); | { "query_optimization": { "r_total_time_ms": 0.265697814 }, "query_block": { "select_id": 1, "cost": 264.3341946, "r_loops": 1, "r_total_time_ms": 9571.648081, "nested_loop": [ { "table": { "table_name": "OUTR2", "access_type": "index", "possible_keys": ["PRIMARY"], "key": "k_int", "key_length": "5", "used_key_parts": ["col_int_key"], "loops": 1, "r_loops": 1, "rows": 100, "r_rows": 100, "cost": 0.02131442, "r_table_time_ms": 0.07246602, "r_other_time_ms": 0.713513641, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "range-checked-for-each-record": { "keys": ["k_int"], "r_keys": { "full_scan": 0, "index_merge": 0, "range": { "k_int": 100 } }, "table": { "table_name": "OUTR3", "access_type": "ALL", "possible_keys": ["k_int"], "loops": 100, "r_loops": 100, "rows": 100, "r_rows": 78.2, "cost": 0.95606102, "r_table_time_ms": 1.354422783, "r_other_time_ms": 2.502753283, "r_engine_stats": { "pages_accessed": 200 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } }, { "table": { "table_name": "OUTR1", "access_type": "ALL", "loops": 10000, "r_loops": 7820, "rows": 100, "r_rows": 100, "cost": 263.3568192, "r_table_time_ms": 194.3539843, "r_other_time_ms": 98.76671729, "r_engine_stats": { "pages_accessed": 7878, "pages_updated": 51 }, "filtered": 100, "r_total_filtered": 1.546803069, "attached_condition": "trigcond((OUTR1.col_int_key,(subquery#2))) and trigcond(OUTR1.col_int_nokey = OUTR2.col_int_key)", "r_filtered": 1.546803069 } } ], "subqueries": [ { "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 520257, "r_rows": 0.294427562, "query_specifications": [ { "query_block": { "select_id": 2, "cost": 0.00171364, "r_loops": 520257, "r_total_time_ms": 298.5959532, "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": 520257, "rows": 1, "r_rows": 1, "cost": 0.00171364, "r_table_time_ms": 163.155732, "r_other_time_ms": 54.53385411, "r_engine_stats": { "pages_accessed": 520257 }, "filtered": 100, "r_total_filtered": 14.5353162, "attached_condition": "INNR1.col_int_nokey <= 1 or INNR1.pk <= 4", "r_filtered": 14.5353162 } } ] } }, { "query_block": { "select_id": 3, "operation": "UNION", "cost": 0.02131442, "r_loops": 520257, "r_total_time_ms": 8818.218816, "having_condition": "count(0) >= 2 and 6 = (INNR1.pk)", "nested_loop": [ { "table": { "table_name": "INNR1", "access_type": "index", "key": "u_int", "key_length": "5", "used_key_parts": ["col_int_uniq"], "loops": 1, "r_loops": 520257, "rows": 100, "r_rows": 100, "cost": 0.02131442, "r_table_time_ms": 6870.627837, "r_other_time_ms": 1867.971324, "r_engine_stats": { "pages_accessed": 520257 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } } ] } } ] } } } ] } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (9.558 sec) MariaDB [federated]> notee