MariaDB [test]> EXPLAIN EXTENDED DELETE `tc` FROM `COMPLETED_TXN_COMP` `tc` INNER JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `highestWriteId` FROM `COMPLETED_TXN_COMP` GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c` ON `tc`.`TEST_DATABASE` = `c`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c`.`TEST_PARTITION` IS NULL)) LEFT JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `updateWriteId` FROM `COMPLETED_TXN_COMP` WHERE `TEST_UPDATE_DELETE` = 'Y' GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c2` ON `tc`.`TEST_DATABASE` = `c2`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c2`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c2`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c2`.`TEST_PARTITION` IS NULL)) WHERE `tc`.`TEST_WRITEID` < `c`.`highestWriteId` AND NOT `tc`.`TEST_WRITEID` <=> `c2`.`updateWriteId`; +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------+----------------------------------------------+ | 1 | PRIMARY | tc | ALL | COMPLETED_TXN_COMP_IDX | NULL | NULL | NULL | 137197 | 100.00 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 389 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 2 | 100.00 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 10 | 100.00 | Using where | | 3 | DERIVED | COMPLETED_TXN_COMP | ALL | COMPLETED_TXN_COMP_IDX | NULL | NULL | NULL | 137197 | 100.00 | Using where; Using temporary; Using filesort | | 2 | LATERAL DERIVED | COMPLETED_TXN_COMP | ref | COMPLETED_TXN_COMP_IDX | COMPLETED_TXN_COMP_IDX | 389 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 1 | 100.00 | Using where; Using temporary; Using filesort | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------+----------------------------------------------+ 5 rows in set, 1 warning (0.015 sec) MariaDB [test]> set session optimizer_trace_max_mem_size=10*1024*1024; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> set session optimizer_trace='enabled=on'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> EXPLAIN DELETE `tc` FROM `COMPLETED_TXN_COMP` `tc` INNER JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `highestWriteId` FROM `COMPLETED_TXN_COMP` GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c` ON `tc`.`TEST_DATABASE` = `c`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c`.`TEST_PARTITION` IS NULL)) LEFT JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `updateWriteId` FROM `COMPLETED_TXN_COMP` WHERE `TEST_UPDATE_DELETE` = 'Y' GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c2` ON `tc`.`TEST_DATABASE` = `c2`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c2`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c2`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c2`.`TEST_PARTITION` IS NULL)) WHERE `tc`.`TEST_WRITEID` < `c`.`highestWriteId` AND NOT `tc`.`TEST_WRITEID` <=> `c2`.`updateWriteId`; +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------------------------------------------+ | 1 | PRIMARY | tc | ALL | COMPLETED_TXN_COMP_IDX | NULL | NULL | NULL | 137197 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 389 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 2 | Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 10 | Using where | | 3 | DERIVED | COMPLETED_TXN_COMP | ALL | COMPLETED_TXN_COMP_IDX | NULL | NULL | NULL | 137197 | Using where; Using temporary; Using filesort | | 2 | LATERAL DERIVED | COMPLETED_TXN_COMP | ref | COMPLETED_TXN_COMP_IDX | COMPLETED_TXN_COMP_IDX | 389 | test.tc.TEST_DATABASE,test.tc.TEST_TABLE | 1 | Using where; Using temporary; Using filesort | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+----------------------------------------+--------+----------------------------------------------+ 5 rows in set (0.006 sec) MariaDB [test]> select * from information_schema.optimizer_trace limit 1\G *************************** 1. row *************************** QUERY: EXPLAIN DELETE `tc` FROM `COMPLETED_TXN_COMP` `tc` INNER JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `highestWriteId` FROM `COMPLETED_TXN_COMP` GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c` ON `tc`.`TEST_DATABASE` = `c`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c`.`TEST_PARTITION` IS NULL)) LEFT JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `updateWriteId` FROM `COMPLETED_TXN_COMP` WHERE `TEST_UPDATE_DELETE` = 'Y' GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c2` ON `tc`.`TEST_DATABASE` = `c2`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c2`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c2`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c2`.`TEST_PARTITION` IS NULL)) WHERE `tc`.`TEST_WRITEID` < `c`.`highestWriteId` AND NOT `tc`.`TEST_WRITEID` <=> `c2`.`updateWriteId` TRACE: { "steps": [ { "derived": { "table": "c", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select COMPLETED_TXN_COMP.TEST_DATABASE AS TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE AS TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION AS TEST_PARTITION,max(COMPLETED_TXN_COMP.TEST_WRITEID) AS highestWriteId from COMPLETED_TXN_COMP group by COMPLETED_TXN_COMP.TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION" } ] } }, { "derived": { "table": "c2", "select_id": 3, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 3, "steps": [ { "expanded_query": "/* select#3 */ select COMPLETED_TXN_COMP.TEST_DATABASE AS TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE AS TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION AS TEST_PARTITION,max(COMPLETED_TXN_COMP.TEST_WRITEID) AS updateWriteId from COMPLETED_TXN_COMP where COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y' group by COMPLETED_TXN_COMP.TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION" } ] } }, { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "/* select#1 */ delete from COMPLETED_TXN_COMP tc using ((COMPLETED_TXN_COMP tc join (/* select#2 */ select COMPLETED_TXN_COMP.TEST_DATABASE AS TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE AS TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION AS TEST_PARTITION,max(COMPLETED_TXN_COMP.TEST_WRITEID) AS highestWriteId from COMPLETED_TXN_COMP group by COMPLETED_TXN_COMP.TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION) c on(tc.TEST_DATABASE = c.TEST_DATABASE and tc.TEST_TABLE = c.TEST_TABLE and (tc.TEST_PARTITION = c.TEST_PARTITION or tc.TEST_PARTITION is null and c.TEST_PARTITION is null))) left join (/* select#3 */ select COMPLETED_TXN_COMP.TEST_DATABASE AS TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE AS TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION AS TEST_PARTITION,max(COMPLETED_TXN_COMP.TEST_WRITEID) AS updateWriteId from COMPLETED_TXN_COMP where COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y' group by COMPLETED_TXN_COMP.TEST_DATABASE,COMPLETED_TXN_COMP.TEST_TABLE,COMPLETED_TXN_COMP.TEST_PARTITION) c2 on(tc.TEST_DATABASE = c2.TEST_DATABASE and tc.TEST_TABLE = c2.TEST_TABLE and (tc.TEST_PARTITION = c2.TEST_PARTITION or tc.TEST_PARTITION is null and c2.TEST_PARTITION is null))) where tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId)" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId) and tc.TEST_DATABASE = c.TEST_DATABASE and tc.TEST_TABLE = c.TEST_TABLE and (tc.TEST_PARTITION = c.TEST_PARTITION or tc.TEST_PARTITION is null and c.TEST_PARTITION is null)", "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "c2", "resulting_condition": "(multiple equal(tc.TEST_PARTITION, c2.TEST_PARTITION) or tc.TEST_PARTITION is null and c2.TEST_PARTITION is null) and multiple equal(tc.TEST_DATABASE, c.TEST_DATABASE, c2.TEST_DATABASE) and multiple equal(tc.TEST_TABLE, c.TEST_TABLE, c2.TEST_TABLE)" } }, { "transformation": "equality_propagation", "resulting_condition": "tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId) and (multiple equal(tc.TEST_PARTITION, c.TEST_PARTITION) or tc.TEST_PARTITION is null and c.TEST_PARTITION is null) and multiple equal(tc.TEST_DATABASE, c.TEST_DATABASE) and multiple equal(tc.TEST_TABLE, c.TEST_TABLE)" }, { "transformation": "constant_propagation", "resulting_condition": "tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId) and (multiple equal(tc.TEST_PARTITION, c.TEST_PARTITION) or tc.TEST_PARTITION is null and c.TEST_PARTITION is null) and multiple equal(tc.TEST_DATABASE, c.TEST_DATABASE) and multiple equal(tc.TEST_TABLE, c.TEST_TABLE)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId) and (multiple equal(tc.TEST_PARTITION, c.TEST_PARTITION) or tc.TEST_PARTITION is null and c.TEST_PARTITION is null) and multiple equal(tc.TEST_DATABASE, c.TEST_DATABASE) and multiple equal(tc.TEST_TABLE, c.TEST_TABLE)" } ] } }, { "join_optimization": { "select_id": 2, "steps": [ { "table_dependencies": [ { "table": "COMPLETED_TXN_COMP", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "rows_estimation": [ { "table": "COMPLETED_TXN_COMP", "range_analysis": { "table_scan": { "rows": 137197, "cost": 28434.4 }, "potential_range_indexes": [ { "index": "COMPLETED_TXN_COMP_IDX", "usable": true, "key_parts": [ "TEST_DATABASE", "TEST_TABLE", "TEST_PARTITION" ] } ], "group_index_range": { "potential_group_range_indexes": [ { "index": "COMPLETED_TXN_COMP_IDX", "usable": false, "cause": "not covering" } ] } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "COMPLETED_TXN_COMP", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 137197, "cost": 993, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 137197, "cost": 993, "uses_join_buffering": false } }, "rows_for_plan": 137197, "cost_for_plan": 28432.4, "cost_for_sorting": 137197 } ] }, { "check_split_materialized": { "split_candidates": [ "COMPLETED_TXN_COMP.TEST_DATABASE", "COMPLETED_TXN_COMP.TEST_TABLE", "COMPLETED_TXN_COMP.TEST_PARTITION" ] } } ] } }, { "join_optimization": { "select_id": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('Y', COMPLETED_TXN_COMP.TEST_UPDATE_DELETE)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('Y', COMPLETED_TXN_COMP.TEST_UPDATE_DELETE)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('Y', COMPLETED_TXN_COMP.TEST_UPDATE_DELETE)" } ] } }, { "table_dependencies": [ { "table": "COMPLETED_TXN_COMP", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [] }, { "rows_estimation": [ { "table": "COMPLETED_TXN_COMP", "range_analysis": { "table_scan": { "rows": 137197, "cost": 28434.4 }, "potential_range_indexes": [ { "index": "COMPLETED_TXN_COMP_IDX", "usable": true, "key_parts": [ "TEST_DATABASE", "TEST_TABLE", "TEST_PARTITION" ] } ], "setup_range_conditions": [], "group_index_range": { "potential_group_range_indexes": [ { "index": "COMPLETED_TXN_COMP_IDX", "usable": false, "cause": "not covering" } ] } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "COMPLETED_TXN_COMP", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 137197, "cost": 993, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 137197, "cost": 993, "uses_join_buffering": false } }, "rows_for_plan": 137197, "cost_for_plan": 28432.4, "cost_for_sorting": 137197 } ] }, { "check_split_materialized": { "split_candidates": [ "COMPLETED_TXN_COMP.TEST_DATABASE", "COMPLETED_TXN_COMP.TEST_TABLE", "COMPLETED_TXN_COMP.TEST_PARTITION" ] } } ] } }, { "table_dependencies": [ { "table": "tc", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "", "row_may_be_null": true, "map_bit": 2, "depends_on_map_bits": ["0"] } ] }, { "ref_optimizer_key_uses": [ { "table": "tc", "field": "TEST_DATABASE", "equals": "c.TEST_DATABASE", "null_rejecting": false }, { "table": "tc", "field": "TEST_TABLE", "equals": "c.TEST_TABLE", "null_rejecting": true }, { "table": "tc", "field": "TEST_PARTITION", "equals": "c.TEST_PARTITION", "null_rejecting": false }, { "table": "", "field": "TEST_DATABASE", "equals": "tc.TEST_DATABASE", "null_rejecting": false }, { "table": "", "field": "TEST_TABLE", "equals": "tc.TEST_TABLE", "null_rejecting": true }, { "table": "", "field": "TEST_DATABASE", "equals": "tc.TEST_DATABASE", "null_rejecting": true }, { "table": "", "field": "TEST_TABLE", "equals": "tc.TEST_TABLE", "null_rejecting": true }, { "table": "", "field": "TEST_DATABASE", "equals": "c.TEST_DATABASE", "null_rejecting": true }, { "table": "", "field": "TEST_TABLE", "equals": "c.TEST_TABLE", "null_rejecting": true } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "tc", "table_scan": { "rows": 137197, "cost": 993 } }, { "table": "", "table_scan": { "rows": 137197, "cost": 137197 } }, { "table": "", "table_scan": { "rows": 137197, "cost": 137197 } } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "tc", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 137197, "cost": 993, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 137197, "cost": 993, "uses_join_buffering": false } }, "rows_for_plan": 137197, "cost_for_plan": 28432.4, "rest_of_plan": [ { "plan_prefix": ["tc"], "table": "", "best_access_path": { "choose_best_splitting": { "considered_keys": [ { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 699, "param_tables": 1 }, { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 1, "param_tables": 1 } ], "refills": 137197, "spl_pd_boundary": 2, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": [], "table": "COMPLETED_TXN_COMP", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "COMPLETED_TXN_COMP_IDX", "rows": 1, "cost": 2.014220323, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 2.014220323, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 2.214220323, "cost_for_sorting": 1, "pruned_by_hanging_leaf": true } ] } ], "lead_table": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "parts": 2, "split_sel": 7.288789e-6, "cost": 2.164220323, "unsplit_cost": 322215.7766, "records": 1, "chosen": true }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 2, "cost": 331223.7857, "chosen": true }, { "access_type": "scan", "resulting_rows": 1, "cost": 8855868.977, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 2, "cost": 331223.7857, "uses_join_buffering": false } }, "rows_for_plan": 274394, "cost_for_plan": 414534.9857, "rest_of_plan": [ { "plan_prefix": ["tc", ""], "table": "", "best_access_path": { "choose_best_splitting": { "considered_keys": [ { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 699, "param_tables": 1 }, { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 1, "param_tables": 3 } ], "refills": 274394, "spl_pd_boundary": 4, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": [], "table": "COMPLETED_TXN_COMP", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "COMPLETED_TXN_COMP_IDX", "rows": 1, "cost": 2.014220323, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 2.014220323, "uses_join_buffering": false } }, "rows_for_plan": 1, "cost_for_plan": 2.214220323, "cost_for_sorting": 1, "pruned_by_hanging_leaf": true } ] } ], "lead_table": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "parts": 2, "split_sel": 7.288789e-6, "cost": 2.164220323, "unsplit_cost": 322215.7766, "records": 1, "chosen": false }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 10.00051024, "cost": 610329.4766, "chosen": true }, { "access_type": "ref", "index": "key1", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 10.00051024, "cost": 610329.4766, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 137197, "cost": 37646355834, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 10.00051024, "cost": 610329.4766, "uses_join_buffering": false } }, "rows_for_plan": 2744080.007, "cost_for_plan": 1573680.464 } ] }, { "plan_prefix": ["tc"], "table": "", "best_access_path": { "choose_best_splitting": { "considered_keys": [ { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 699, "param_tables": 1 }, { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 1, "param_tables": 1 } ], "refills": 137197, "spl_pd_boundary": 4, "cached_plan_found": 1, "lead_table": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "parts": 2, "split_sel": 7.288789e-6, "cost": 2.164220323, "unsplit_cost": 322215.7766, "records": 1, "chosen": true }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 2, "cost": 331223.7857, "chosen": true }, { "access_type": "scan", "resulting_rows": 1, "cost": 22587914947, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 2, "cost": 331223.7857, "uses_join_buffering": false } }, "rows_for_plan": 274394, "cost_for_plan": 414534.9857, "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "", "best_access_path": { "choose_best_splitting": { "considered_keys": [] }, "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 137197, "cost": 459412.7766, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 137197, "cost": 459412.7766, "uses_join_buffering": false } }, "rows_for_plan": 137197, "cost_for_plan": 486852.1766, "rest_of_plan": [ { "plan_prefix": [""], "table": "tc", "best_access_path": { "considered_access_paths": [ { "access_type": "ref_or_null", "index": "COMPLETED_TXN_COMP_IDX", "rows": 2, "cost": 415492.9714, "chosen": true }, { "access_type": "scan", "resulting_rows": 137197, "cost": 605730, "chosen": false } ], "chosen_access_method": { "type": "ref_or_null", "records": 2, "cost": 415492.9714, "uses_join_buffering": false } }, "rows_for_plan": 274394, "cost_for_plan": 957223.948, "rest_of_plan": [ { "plan_prefix": ["", "tc"], "table": "", "best_access_path": { "choose_best_splitting": { "considered_keys": [ { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 699, "param_tables": 1 }, { "table_name": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "rec_per_key": 1, "param_tables": 3 } ], "refills": 274394, "spl_pd_boundary": 4, "cached_plan_found": 1, "lead_table": "COMPLETED_TXN_COMP", "index": "COMPLETED_TXN_COMP_IDX", "parts": 2, "split_sel": 7.288789e-6, "cost": 2.164220323, "unsplit_cost": 322215.7766, "records": 1, "chosen": false }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 10.00051024, "cost": 610329.4766, "chosen": true }, { "access_type": "ref", "index": "key1", "rec_per_key_stats_missing": true, "used_range_estimates": false, "cause": "not available", "rows": 10.00051024, "cost": 610329.4766, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 137197, "cost": 37646355834, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 10.00051024, "cost": 610329.4766, "uses_join_buffering": false } }, "rows_for_plan": 2744080.007, "cost_for_plan": 2116369.426, "pruned_by_cost": true } ] } ] } ] }, { "best_join_order": ["tc", "", ""] }, { "best_join_order": ["COMPLETED_TXN_COMP"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "COMPLETED_TXN_COMP.TEST_DATABASE = tc.TEST_DATABASE and COMPLETED_TXN_COMP.TEST_TABLE = tc.TEST_TABLE" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "COMPLETED_TXN_COMP", "attached": null } ] } }, { "best_join_order": ["COMPLETED_TXN_COMP"] }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y'" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "COMPLETED_TXN_COMP", "attached": "COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y'" } ] } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "fanout": 1, "read_time": 993.001, "table": "COMPLETED_TXN_COMP", "rows_estimation": 137197, "possible_keys": [ { "index": "COMPLETED_TXN_COMP_IDX", "can_resolve_order": false, "cause": "order can not be resolved by key" } ] } }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "c.TEST_DATABASE = tc.TEST_DATABASE and c.TEST_TABLE = tc.TEST_TABLE and tc.TEST_WRITEID < c.highestWriteId and !(tc.TEST_WRITEID <=> c2.updateWriteId) and (c.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c.TEST_PARTITION is null)" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "c2", "resulting_condition": "c2.TEST_DATABASE = tc.TEST_DATABASE and c2.TEST_TABLE = tc.TEST_TABLE and (c2.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c2.TEST_PARTITION is null)" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "tc", "attached": "tc.TEST_TABLE is not null" }, { "table": "", "attached": "tc.TEST_WRITEID < c.highestWriteId and (c.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c.TEST_PARTITION is null)" }, { "table": "", "attached": "trigcond(!(tc.TEST_WRITEID <=> c2.updateWriteId)) and trigcond((c2.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c2.TEST_PARTITION is null) and trigcond(tc.TEST_TABLE is not null))" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 3, "steps": [] } }, { "join_execution": { "select_id": 2, "steps": [] } } ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.009 sec) MariaDB [test]> set session optimizer_trace='enabled=off'; Query OK, 0 rows affected (0.001 sec) MariaDB [test]> ANALYZE FORMAT=JSON DELETE `tc` FROM `COMPLETED_TXN_COMP` `tc` INNER JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `highestWriteId` FROM `COMPLETED_TXN_COMP` GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c` ON `tc`.`TEST_DATABASE` = `c`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c`.`TEST_PARTITION` IS NULL)) LEFT JOIN ( SELECT `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`, max(`TEST_WRITEID`) `updateWriteId` FROM `COMPLETED_TXN_COMP` WHERE `TEST_UPDATE_DELETE` = 'Y' GROUP BY `TEST_DATABASE`, `TEST_TABLE`, `TEST_PARTITION`) `c2` ON `tc`.`TEST_DATABASE` = `c2`.`TEST_DATABASE` AND `tc`.`TEST_TABLE` = `c2`.`TEST_TABLE` AND (`tc`.`TEST_PARTITION` = `c2`.`TEST_PARTITION` OR (`tc`.`TEST_PARTITION` IS NULL AND `c2`.`TEST_PARTITION` IS NULL)) WHERE `tc`.`TEST_WRITEID` < `c`.`highestWriteId` AND NOT `tc`.`TEST_WRITEID` <=> `c2`.`updateWriteId`; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 180567.4824, "table": { "table_name": "tc", "access_type": "ALL", "possible_keys": ["COMPLETED_TXN_COMP_IDX"], "r_loops": 1, "rows": 137197, "r_rows": 141021, "r_table_time_ms": 90.73075805, "r_other_time_ms": 24437.09889, "filtered": 100, "r_filtered": 100, "attached_condition": "tc.TEST_TABLE is not null" }, "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "389", "used_key_parts": ["TEST_DATABASE", "TEST_TABLE"], "ref": ["test.tc.TEST_DATABASE", "test.tc.TEST_TABLE"], "r_loops": 141021, "rows": 2, "r_rows": 392.0611186, "r_table_time_ms": 10886.55378, "r_other_time_ms": 18915.64526, "filtered": 100, "r_filtered": 0.001721866, "attached_condition": "tc.TEST_WRITEID < c.highestWriteId and (c.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c.TEST_PARTITION is null)", "materialized": { "lateral": 1, "query_block": { "select_id": 2, "r_loops": 141021, "r_total_time_ms": 149140.1531, "outer_ref_condition": "tc.TEST_TABLE is not null", "filesort": { "sort_key": "COMPLETED_TXN_COMP.TEST_PARTITION", "r_loops": 141021, "r_total_time_ms": 13723.70463, "r_used_priority_queue": false, "r_output_rows": 392, "r_buffer_size": "(varied across executions)", "r_sort_mode": "packed_sort_key,rowid", "temporary_table": { "table": { "table_name": "COMPLETED_TXN_COMP", "access_type": "ref", "possible_keys": ["COMPLETED_TXN_COMP_IDX"], "key": "COMPLETED_TXN_COMP_IDX", "key_length": "389", "used_key_parts": ["TEST_DATABASE", "TEST_TABLE"], "ref": ["test.tc.TEST_DATABASE", "test.tc.TEST_TABLE"], "r_loops": 141021, "rows": 1, "r_rows": 397.1914892, "r_table_time_ms": 35847.99929, "r_other_time_ms": 75430.95441, "filtered": 100, "r_filtered": 100, "attached_condition": "COMPLETED_TXN_COMP.TEST_DATABASE <=> tc.TEST_DATABASE and COMPLETED_TXN_COMP.TEST_TABLE <=> tc.TEST_TABLE" } } } } } }, "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "390", "used_key_parts": ["TEST_DATABASE", "TEST_TABLE"], "ref": ["test.tc.TEST_DATABASE", "test.tc.TEST_TABLE"], "r_loops": 952, "rows": 10, "r_rows": 248.8413866, "r_table_time_ms": 76.4013922, "r_other_time_ms": 379.5898221, "filtered": 100, "r_filtered": 0.401862413, "attached_condition": "trigcond(!(tc.TEST_WRITEID <=> c2.updateWriteId)) and trigcond((c2.TEST_PARTITION = tc.TEST_PARTITION or tc.TEST_PARTITION is null and c2.TEST_PARTITION is null) and trigcond(tc.TEST_TABLE is not null))", "materialized": { "query_block": { "select_id": 3, "r_loops": 1, "r_total_time_ms": 743.0759134, "filesort": { "sort_key": "COMPLETED_TXN_COMP.TEST_DATABASE, COMPLETED_TXN_COMP.TEST_TABLE, COMPLETED_TXN_COMP.TEST_PARTITION", "r_loops": 1, "r_total_time_ms": 69.15281653, "r_used_priority_queue": false, "r_output_rows": 100380, "r_sort_passes": 1, "r_buffer_size": "2047Kb", "r_sort_mode": "packed_sort_key,rowid", "temporary_table": { "table": { "table_name": "COMPLETED_TXN_COMP", "access_type": "ALL", "possible_keys": ["COMPLETED_TXN_COMP_IDX"], "r_loops": 1, "rows": 137197, "r_rows": 141021, "r_table_time_ms": 53.85216652, "r_other_time_ms": 163.9514537, "filtered": 100, "r_filtered": 71.18088795, "attached_condition": "COMPLETED_TXN_COMP.TEST_UPDATE_DELETE = 'Y'" } } } } } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (3 min 0.101 sec)