MariaDB [test]> select * from information_schema.optimizer_trace limit 1\G *************************** 1. row *************************** QUERY: analyze format=json SELECT S.*, B.* FROM Teilnehmer_1 S INNER JOIN Buchholzpunkte B ON B.TerID = S.TerID AND B.TnNr = S.TnNr TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "view": { "table": "S", "select_id": 3, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 3, "steps": [ { "expanded_query": "/* select#3 */ select P.TerID AS TerID,M.TnNr AS TnNr,sum(case when ifnull(P.PaSiegMa,0) = 0 then 0 else case when P.PaSiegMa = M.MaNr then 1 else 0 end end) AS Siege,sum(case when ifnull(P.PaSiegMa,0) = 0 then 0 else case when P.PaSiegMa = M.MaNr then P.PaSiegPunkte - P.PaPunkte else P.PaPunkte - P.PaSiegPunkte end end) AS Differenz,sum(case when ifnull(P.PaSiegMa,0) <> 0 then 1 else 0 end) AS Spiele,max(case when ifnull(P.PaSiegMa,0) = M.MaNr then P.PaRunde else 0 end) AS lastWinRound,max(case when ifnull(P.PaSiegMa,0) > 0 and ifnull(P.PaSiegMa,0) <> M.MaNr then P.PaRunde else 0 end) AS lastLoseRound from ((Manschaft M join Partien P on(M.PaID = P.PaID)) join Termin T on(T.TerID = P.TerID)) where P.PaOption & 1 = 1 group by P.TerID,M.TnNr order by P.TerID desc" } ] } }, { "view": { "table": "B", "select_id": 2, "algorithm": "materialized" } }, { "join_preparation": { "select_id": 2, "steps": [ { "expanded_query": "/* select#2 */ select Tn.TerID AS TerID,Tn.TnNr AS TnNr,sum(ifnull(S.Siege,0)) - case when Te.TuOptionen & 1 << 38 = 1 << 38 and Tn.TnOption & 4 = 0 then ifnull(min(S.Siege),0) else 0 end AS Buchholzpunkte from (((((Teilnehmer_1 Tn join Termin Te on(Tn.TerID = Te.TerID and Te.TuOptionen & (8 | 16) > 0)) left join Manschaft M1 on(M1.TnNr = Tn.TnNr)) join Partien P on(M1.PaID = P.PaID and P.TerID = Tn.TerID and P.PaOption & 1 = 1)) left join Manschaft M2 on(M2.TnNr <> Tn.TnNr and P.PaID = M2.PaID)) left join TeamSiegeVorrunde S on(S.TerID = Tn.TerID and M2.TnNr = S.TnNr)) group by Tn.TerID,Tn.TnNr order by Tn.TerID,Tn.TnNr" } ] } }, { "expanded_query": "/* select#1 */ select S.TNID AS TNID,S.TnNr AS TnNr,S.TnName AS TnName,S.TeaID AS TeaID,S.TerID AS TerID,S.TnOption AS TnOption,S.TnEMail AS TnEMail,S.TnHandy AS TnHandy,S.TnGewichtung AS TnGewichtung,S.TnCreate AS TnCreate,B.TerID AS TerID,B.TnNr AS TnNr,B.Buchholzpunkte AS Buchholzpunkte from (Teilnehmer_1 S join Buchholzpunkte B on(B.TerID = S.TerID and B.TnNr = S.TnNr))" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "B.TerID = S.TerID and B.TnNr = S.TnNr", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(B.TerID, S.TerID) and multiple equal(B.TnNr, S.TnNr)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(B.TerID, S.TerID) and multiple equal(B.TnNr, S.TnNr)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(B.TerID, S.TerID) and multiple equal(B.TnNr, S.TnNr)" } ] } }, { "join_optimization": { "select_id": 2, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "M1.PaID = P.PaID and P.TerID = Tn.TerID and P.PaOption & 1 = 1 and M1.TnNr = Tn.TnNr and Tn.TerID = Te.TerID and Te.TuOptionen & (8 | 16) > 0", "steps": [ { "build_equal_items": { "condition": "ON expr", "attached_to": "S", "resulting_condition": "multiple equal(P.TerID, Tn.TerID, Te.TerID, S.TerID) and multiple equal(M2.TnNr, S.TnNr)" } }, { "build_equal_items": { "condition": "ON expr", "attached_to": "M2", "resulting_condition": "M2.TnNr <> Tn.TnNr and multiple equal(M1.PaID, P.PaID, M2.PaID)" } }, { "transformation": "equality_propagation", "resulting_condition": "P.PaOption & 1 = 1 and Te.TuOptionen & (8 | 16) > 0 and multiple equal(M1.PaID, P.PaID) and multiple equal(P.TerID, Tn.TerID, Te.TerID) and multiple equal(M1.TnNr, Tn.TnNr)" }, { "transformation": "constant_propagation", "resulting_condition": "P.PaOption & 1 = 1 and Te.TuOptionen & (8 | 16) > 0 and multiple equal(M1.PaID, P.PaID) and multiple equal(P.TerID, Tn.TerID, Te.TerID) and multiple equal(M1.TnNr, Tn.TnNr)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "P.PaOption & 1 = 1 and Te.TuOptionen & (8 | 16) > 0 and multiple equal(M1.PaID, P.PaID) and multiple equal(P.TerID, Tn.TerID, Te.TerID) and multiple equal(M1.TnNr, Tn.TnNr)" } ] } }, { "join_optimization": { "select_id": 3, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "P.PaOption & 1 = 1 and T.TerID = P.TerID and M.PaID = P.PaID", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "P.PaOption & 1 = 1 and multiple equal(T.TerID, P.TerID) and multiple equal(M.PaID, P.PaID)" }, { "transformation": "constant_propagation", "resulting_condition": "P.PaOption & 1 = 1 and multiple equal(T.TerID, P.TerID) and multiple equal(M.PaID, P.PaID)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "P.PaOption & 1 = 1 and multiple equal(T.TerID, P.TerID) and multiple equal(M.PaID, P.PaID)" } ] } }, { "table_dependencies": [ { "table": "M", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "P", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "T", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "M", "index": "PaID_LiMaNr_MaNr_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": true }, { "table": "M", "index": "PaID", "field": "PaID", "equals": "P.PaID", "null_rejecting": true }, { "table": "M", "index": "idx_PaID_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": true }, { "table": "M", "index": "idx_PaID_MaNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": true }, { "table": "P", "index": "PRIMARY", "field": "PaID", "equals": "M.PaID", "null_rejecting": true }, { "table": "P", "index": "TerID", "field": "TerID", "equals": "T.TerID", "null_rejecting": true }, { "table": "P", "index": "TerID", "field": "PaID", "equals": "M.PaID", "null_rejecting": true }, { "table": "P", "index": "TerRunde", "field": "TerID", "equals": "T.TerID", "null_rejecting": true }, { "table": "T", "index": "PRIMARY", "field": "TerID", "equals": "P.TerID", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "M", "range_analysis": { "table_scan": { "rows": 22506, "cost": 3.7613936 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "PaID_LiMaNr_MaNr_TnNr", "usable": true, "key_parts": ["PaID", "MaNr", "LiMaNr", "TnNr"] }, { "index": "PaID", "usable": false, "cause": "not applicable" }, { "index": "MaNr", "usable": false, "cause": "not applicable" }, { "index": "TnNr", "usable": false, "cause": "not applicable" }, { "index": "MaOpt_Manschaft_IDX", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_TnNr", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_MaNr", "usable": false, "cause": "not applicable" } ], "best_covering_index_scan": { "index": "PaID_LiMaNr_MaNr_TnNr", "cost": 3.33529213, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "P", "range_analysis": { "table_scan": { "rows": 10135, "cost": 1.7449206 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "TerID", "usable": true, "key_parts": ["TerID", "PaID"] }, { "index": "Runde", "usable": false, "cause": "not applicable" }, { "index": "TerRunde", "usable": false, "cause": "not applicable" }, { "index": "PaOption_Partien_IDX", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "T", "range_analysis": { "table_scan": { "rows": 1, "cost": 0.0110178 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "TuSID", "usable": true, "key_parts": ["TuSID", "TerID"] }, { "index": "test_idx_idx", "usable": false, "cause": "not applicable" } ], "best_covering_index_scan": { "index": "TuSID", "cost": 0.006792605, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "T", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.006792605, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.006792605, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 10135, "rows_after_filter": 10135, "rows_out": 10135, "cost": 1.7449206, "index_only": false, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "rows_read": 10135, "rows_out": 10135, "cost": 1.7449206, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.33529213, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "T", "rows_for_plan": 1, "cost_for_plan": 0.006792605, "rest_of_plan": [ { "plan_prefix": "T", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 5.425395192, "cost_without_join_buffer": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 5.425395192, "uses_join_buffering": true } } } ] }, { "plan_prefix": "T", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.079131485, "rest_of_plan": [ { "plan_prefix": "T,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.28470352, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.08215854, "uses_join_buffering": false } } } ] }, { "plan_prefix": "T,P", "table": "M", "rows_for_plan": 156, "cost_for_plan": 0.161290025, "cost_for_sorting": 0.100964921 } ] }, { "plan_prefix": "T", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 5.432187797, "pruned_by_cost": true, "current_cost": 5.432187797, "best_cost": 0.262254946 } ] }, { "plan_prefix": "", "table": "P", "rows_for_plan": 10135, "cost_for_plan": 1.7449206, "pruned_by_cost": true, "current_cost": 1.7449206, "best_cost": 0.262254946 }, { "plan_prefix": "", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 3.33529213, "pruned_by_cost": true, "current_cost": 3.33529213, "best_cost": 0.262254946 } ] }, { "check_split_materialized": { "split_candidates": ["P.TerID", "M.TnNr"] } } ] } }, { "table_dependencies": [ { "table": "Tn", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "Te", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "M1", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "P", "row_may_be_null": false, "map_bit": 3, "depends_on_map_bits": [] }, { "table": "M2", "row_may_be_null": true, "map_bit": 4, "depends_on_map_bits": ["0", "3"] }, { "table": "", "row_may_be_null": true, "map_bit": 5, "depends_on_map_bits": ["0", "3", "4"] } ] }, { "ref_optimizer_key_uses": [ { "table": "Tn", "index": "Key_TeaID_TerID_Teilnehmer_1", "field": "TerID", "equals": "P.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Key_TeaID_TerID_Teilnehmer_1", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Key_TnNr_TerID_Teilnehmer_1", "field": "TerID", "equals": "P.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Key_TnNr_TerID_Teilnehmer_1", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Key_TnNr_TerID_Teilnehmer_1", "field": "TnNr", "equals": "M1.TnNr", "null_rejecting": true }, { "table": "Tn", "index": "TerID_Teilnehmer_1", "field": "TerID", "equals": "P.TerID", "null_rejecting": false }, { "table": "Tn", "index": "TerID_Teilnehmer_1", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Ter_Tea_Teilnehmer_1", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "Tn", "index": "Ter_Tea_Teilnehmer_1", "field": "TerID", "equals": "P.TerID", "null_rejecting": false }, { "table": "Te", "index": "PRIMARY", "field": "TerID", "equals": "P.TerID", "null_rejecting": false }, { "table": "Te", "index": "PRIMARY", "field": "TerID", "equals": "Tn.TerID", "null_rejecting": false }, { "table": "M1", "index": "PaID_LiMaNr_MaNr_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M1", "index": "PaID", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M1", "index": "TnNr", "field": "TnNr", "equals": "Tn.TnNr", "null_rejecting": false }, { "table": "M1", "index": "idx_PaID_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M1", "index": "idx_PaID_TnNr", "field": "TnNr", "equals": "Tn.TnNr", "null_rejecting": false }, { "table": "M1", "index": "idx_PaID_MaNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "P", "index": "PRIMARY", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "P", "index": "TerID", "field": "TerID", "equals": "Tn.TerID", "null_rejecting": false }, { "table": "P", "index": "TerID", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "P", "index": "TerID", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "P", "index": "TerRunde", "field": "TerID", "equals": "Te.TerID", "null_rejecting": false }, { "table": "P", "index": "TerRunde", "field": "TerID", "equals": "Tn.TerID", "null_rejecting": false }, { "table": "M2", "index": "PaID_LiMaNr_MaNr_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M2", "index": "PaID_LiMaNr_MaNr_TnNr", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "M2", "index": "PaID", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "M2", "index": "PaID", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M2", "index": "idx_PaID_TnNr", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "M2", "index": "idx_PaID_TnNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M2", "index": "idx_PaID_MaNr", "field": "PaID", "equals": "P.PaID", "null_rejecting": false }, { "table": "M2", "index": "idx_PaID_MaNr", "field": "PaID", "equals": "M1.PaID", "null_rejecting": true }, { "table": "", "index": "key0", "field": "TerID", "equals": "Tn.TerID", "null_rejecting": true }, { "table": "", "index": "key1", "field": "TerID", "equals": "Te.TerID", "null_rejecting": true }, { "table": "", "index": "key2", "field": "TerID", "equals": "P.TerID", "null_rejecting": true }, { "table": "", "index": "key3", "field": "TnNr", "equals": "M2.TnNr", "null_rejecting": true } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "table": "Tn", "range_analysis": { "table_scan": { "rows": 1700, "cost": 0.2930836 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "Key_TeaID_TerID_Teilnehmer_1", "usable": false, "cause": "not applicable" }, { "index": "Key_TnNr_TerID_Teilnehmer_1", "usable": true, "key_parts": ["TerID", "TnNr"] }, { "index": "TerID_Teilnehmer_1", "usable": false, "cause": "not applicable" }, { "index": "Ter_Tea_Teilnehmer_1", "usable": false, "cause": "not applicable" }, { "index": "FK_TeaID_Teams_idx", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "Te", "range_analysis": { "table_scan": { "rows": 1, "cost": 0.0110178 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "TuSID", "usable": false, "cause": "not applicable" }, { "index": "test_idx_idx", "usable": true, "key_parts": ["ClID", "TerID"] } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "M1", "range_analysis": { "table_scan": { "rows": 22506, "cost": 3.7613936 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "PaID_LiMaNr_MaNr_TnNr", "usable": false, "cause": "not applicable" }, { "index": "PaID", "usable": true, "key_parts": ["PaID", "MaID"] }, { "index": "MaNr", "usable": false, "cause": "not applicable" }, { "index": "TnNr", "usable": false, "cause": "not applicable" }, { "index": "MaOpt_Manschaft_IDX", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_TnNr", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_MaNr", "usable": false, "cause": "not applicable" } ], "best_covering_index_scan": { "index": "idx_PaID_TnNr", "cost": 3.32588933, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "P", "range_analysis": { "table_scan": { "rows": 10135, "cost": 1.7449206 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "TerID", "usable": false, "cause": "not applicable" }, { "index": "Runde", "usable": true, "key_parts": ["PaRunde", "PaID"] }, { "index": "TerRunde", "usable": false, "cause": "not applicable" }, { "index": "PaOption_Partien_IDX", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "M2", "range_analysis": { "table_scan": { "rows": 22506, "cost": 3.7613936 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "PaID_LiMaNr_MaNr_TnNr", "usable": false, "cause": "not applicable" }, { "index": "PaID", "usable": true, "key_parts": ["PaID", "MaID"] }, { "index": "MaNr", "usable": false, "cause": "not applicable" }, { "index": "TnNr", "usable": false, "cause": "not applicable" }, { "index": "MaOpt_Manschaft_IDX", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_TnNr", "usable": false, "cause": "not applicable" }, { "index": "idx_PaID_MaNr", "usable": false, "cause": "not applicable" } ], "best_covering_index_scan": { "index": "idx_PaID_TnNr", "cost": 3.32588933, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "", "table_scan": { "rows": 156, "read_cost": 0.01358459, "read_and_compare_cost": 0.018940694 } } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "Te", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.0110178, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.0110178, "uses_join_buffering": false } } }, { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1700, "rows_after_filter": 1700, "rows_out": 1700, "cost": 0.2930836, "index_only": false, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1700, "rows_out": 1700, "cost": 0.2930836, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 10135, "rows_after_filter": 10135, "rows_out": 10135, "cost": 1.7449206, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 10135, "rows_out": 10135, "cost": 1.7449206, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.32588933, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.32588933, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "Te", "rows_for_plan": 1, "cost_for_plan": 0.0110178, "rest_of_plan": [ { "plan_prefix": "Te", "get_costs_for_tables": [ { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "Key_TeaID_TerID_Teilnehmer_1", "rows": 48, "cost": 0.05506544, "chosen": true }, { "access_type": "ref", "index": "Key_TnNr_TerID_Teilnehmer_1", "rows": 48, "cost": 0.05506544, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TerID_Teilnehmer_1", "rows": 48, "cost": 0.05506544, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "Ter_Tea_Teilnehmer_1", "rows": 48, "cost": 0.05506544, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 48, "rows_out": 48, "cost": 0.05506544, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 5.415992392, "cost_without_join_buffer": 3.32588933, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 5.415992392, "uses_join_buffering": true } } } ] }, { "plan_prefix": "Te", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.08335668, "rest_of_plan": [ { "plan_prefix": "Te,P", "get_costs_for_tables": [ { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "Key_TeaID_TerID_Teilnehmer_1", "rows": 48, "cost": 1.89933456, "chosen": true }, { "access_type": "ref", "index": "Key_TnNr_TerID_Teilnehmer_1", "rows": 48, "cost": 1.89933456, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TerID_Teilnehmer_1", "rows": 48, "cost": 1.89933456, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "Ter_Tea_Teilnehmer_1", "rows": 48, "cost": 1.89933456, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 1700, "rows_after_filter": 1700, "rows_out": 48, "cost": 10.48788897, "cost_without_join_buffer": 11.2123532, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 48, "rows_out": 48, "cost": 1.89933456, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.07314734, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.07314734, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P", "table": "M1", "rows_for_plan": 156, "cost_for_plan": 0.15650402, "rest_of_plan": [ { "plan_prefix": "Te,P,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 156 }, "considered_access_paths": [ { "access_type": "ref", "index": "Key_TeaID_TerID_Teilnehmer_1", "rows": 48, "cost": 7.57521984, "chosen": true }, { "access_type": "eq_ref", "index": "Key_TnNr_TerID_Teilnehmer_1", "rows": 1, "cost": 0.27587376, "chosen": true }, { "access_type": "ref", "index": "TerID_Teilnehmer_1", "rows": 48, "cost": 7.57521984, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "Ter_Tea_Teilnehmer_1", "rows": 48, "cost": 7.57521984, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 1700, "rows_after_filter": 1700, "rows_out": 1, "cost": 57.2139683, "cost_without_join_buffer": 44.8322096, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 0.27587376, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P,M1", "table": "Tn", "rows_for_plan": 156, "cost_for_plan": 0.43237778, "rest_of_plan": [ { "plan_prefix": "Te,P,M1,Tn", "get_costs_for_tables": [ { "best_access_path": { "table": "M2", "plan_details": { "record_count": 156 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.24753336, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.84062528, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.23852216, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.84226368, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.23852216, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P,M1,Tn", "table": "M2", "rows_for_plan": 624, "cost_for_plan": 0.67089994, "rest_of_plan": [ { "plan_prefix": "Te,P,M1,Tn,M2", "get_costs_for_tables": [ { "best_access_path": { "table": "", "choose_best_splitting": { "considered_keys": [ { "table_name": "M", "index": "TnNr", "rec_per_key": 169, "param_tables": 16 }, { "table_name": "P", "index": "TerID", "rec_per_key": 39, "param_tables": 8 } ], "refills": 39, "spl_pd_boundary": 4, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "T", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.006792605, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.006792605, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.33529213, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "T", "rows_for_plan": 1, "cost_for_plan": 0.006792605, "rest_of_plan": [ { "plan_prefix": "T", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 5.425395192, "cost_without_join_buffer": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 5.425395192, "uses_join_buffering": true } } } ] }, { "plan_prefix": "T", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.079131485, "rest_of_plan": [ { "plan_prefix": "T,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.28470352, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.08215854, "uses_join_buffering": false } } } ] }, { "plan_prefix": "T,P", "table": "M", "rows_for_plan": 156, "cost_for_plan": 0.161290025, "cost_for_sorting": 0.100964921 } ] }, { "plan_prefix": "T", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 5.432187797, "pruned_by_cost": true, "current_cost": 5.432187797, "best_cost": 0.262254946 } ] }, { "plan_prefix": "", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.07233888, "pruned_by_heuristic": true }, { "plan_prefix": "", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 3.33529213, "pruned_by_cost": true, "current_cost": 3.33529213, "best_cost": 0.262254946 } ] } ], "split_plan_discarded": "constructed unapplicable query plan" }, "plan_details": { "record_count": 624 }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.80559024, "chosen": true }, { "access_type": "ref", "index": "key1", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.80559024, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key2", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.80559024, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key3", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.80559024, "chosen": false, "cause": "cost" }, { "access_type": "scan", "rows": 156, "rows_after_filter": 156, "rows_out": 10.4, "cost": 11.96501423, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 10.4, "rows_out": 10.4, "cost": 0.80559024, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P,M1,Tn,M2", "table": "", "rows_for_plan": 6489.6, "cost_for_plan": 1.47649018, "cost_for_sorting": 4.294362328 } ] } ] } ] }, { "plan_prefix": "Te,P", "table": "Tn", "rows_for_plan": 1872, "cost_for_plan": 1.98269124, "rest_of_plan": [ { "plan_prefix": "Te,P,Tn", "get_costs_for_tables": [ { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TnNr", "rows": 169, "cost": 315.9319962, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 1, "cost": 1.84023656, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 1, "rows_out": 1, "cost": 1.84023656, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M2", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 2.66401952, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 2.66401952, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P,Tn", "table": "M1", "rows_for_plan": 1872, "cost_for_plan": 3.8229278, "rest_of_plan": [ { "plan_prefix": "Te,P,Tn,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "M2", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 2.66401952, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 2.66401952, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,P,Tn,M1", "table": "M2", "rows_for_plan": 7488, "cost_for_plan": 6.48694732, "pruned_by_cost": true, "current_cost": 6.48694732, "best_cost": 5.770852508 } ] }, { "plan_prefix": "Te,P,Tn", "table": "M2", "rows_for_plan": 7488, "cost_for_plan": 4.64671076, "pruned_by_heuristic": true } ] } ] }, { "plan_prefix": "Te", "table": "Tn", "rows_for_plan": 48, "cost_for_plan": 0.06608324, "rest_of_plan": [ { "plan_prefix": "Te,Tn", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 48 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 1.98459904, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 1.98541824, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 10135, "rows_after_filter": 10135, "rows_out": 39, "cost": 76.53538553, "cost_without_join_buffer": 80.021456, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 1.98459904, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 48 }, "considered_access_paths": [ { "access_type": "ref", "index": "TnNr", "rows": 169, "cost": 8.19021824, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_TnNr", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 169, "rows_out": 169, "cost": 8.19021824, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,Tn", "table": "P", "rows_for_plan": 1872, "cost_for_plan": 2.05068228, "rest_of_plan": [ { "plan_prefix": "Te,Tn,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TnNr", "rows": 169, "cost": 315.9319962, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 1, "cost": 1.84023656, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 1, "rows_out": 1, "cost": 1.84023656, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M2", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 2.66401952, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 2.66401952, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,Tn,P", "table": "M1", "rows_for_plan": 1872, "cost_for_plan": 3.89091884, "rest_of_plan": [ { "plan_prefix": "Te,Tn,P,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "M2", "plan_details": { "record_count": 1872 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 2.67303072, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 9.07824896, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 2.66401952, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 9.07988736, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 2.66401952, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,Tn,P,M1", "table": "M2", "rows_for_plan": 7488, "cost_for_plan": 6.55493836, "pruned_by_cost": true, "current_cost": 6.55493836, "best_cost": 5.770852508 } ] }, { "plan_prefix": "Te,Tn,P", "table": "M2", "rows_for_plan": 7488, "cost_for_plan": 4.7147018, "pruned_by_heuristic": true } ] }, { "plan_prefix": "Te,Tn", "table": "M1", "rows_for_plan": 8112, "cost_for_plan": 8.25630148, "pruned_by_cost": true, "current_cost": 8.25630148, "best_cost": 5.770852508 } ] }, { "plan_prefix": "Te", "table": "M1", "rows_for_plan": 22506, "cost_for_plan": 5.427010192, "rest_of_plan": [ { "plan_prefix": "Te,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 22506 }, "considered_access_paths": [ { "access_type": "ref", "index": "Key_TeaID_TerID_Teilnehmer_1", "rows": 48, "cost": 1091.814844, "chosen": true }, { "access_type": "eq_ref", "index": "Key_TnNr_TerID_Teilnehmer_1", "rows": 1, "cost": 38.74379976, "chosen": true }, { "access_type": "ref", "index": "TerID_Teilnehmer_1", "rows": 48, "cost": 1091.814844, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "Ter_Tea_Teilnehmer_1", "rows": 48, "cost": 1091.814844, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 1700, "rows_after_filter": 1700, "rows_out": 1, "cost": 5883.767349, "cost_without_join_buffer": 6467.08683, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 38.74379976, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 22506 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20.20972904, "chosen": true }, { "access_type": "eq_ref", "index": "TerID", "rows": 1, "cost": 38.82162376, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 890.6682413, "chosen": false, "cause": "cost" }, { "access_type": "scan_with_join_cache", "rows": 10135, "rows_after_filter": 10135, "rows_out": 1, "cost": 35070.78962, "cost_without_join_buffer": 37482.88171, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 20.20972904, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Te,M1", "table": "Tn", "rows_for_plan": 22506, "cost_for_plan": 44.17080995, "pruned_by_cost": true, "current_cost": 44.17080995, "best_cost": 5.770852508 }, { "plan_prefix": "Te,M1", "table": "P", "rows_for_plan": 22506, "cost_for_plan": 25.63673923, "pruned_by_cost": true, "current_cost": 25.63673923, "best_cost": 5.770852508 } ] } ] }, { "plan_prefix": "", "table": "Tn", "rows_for_plan": 1700, "cost_for_plan": 0.2930836, "pruned_by_heuristic": true }, { "plan_prefix": "", "table": "P", "rows_for_plan": 10135, "cost_for_plan": 1.7449206, "pruned_by_heuristic": true }, { "plan_prefix": "", "table": "M1", "rows_for_plan": 22506, "cost_for_plan": 3.32588933, "pruned_by_heuristic": true } ] }, { "check_split_materialized": { "split_candidates": ["Tn.TerID", "Tn.TnNr"] } } ] } }, { "table_dependencies": [ { "table": "S", "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": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "S", "index": "Key_TeaID_TerID_Teilnehmer_1", "field": "TerID", "equals": "B.TerID", "null_rejecting": false }, { "table": "S", "index": "Key_TnNr_TerID_Teilnehmer_1", "field": "TerID", "equals": "B.TerID", "null_rejecting": false }, { "table": "S", "index": "Key_TnNr_TerID_Teilnehmer_1", "field": "TnNr", "equals": "B.TnNr", "null_rejecting": false }, { "table": "S", "index": "TerID_Teilnehmer_1", "field": "TerID", "equals": "B.TerID", "null_rejecting": false }, { "table": "S", "index": "Ter_Tea_Teilnehmer_1", "field": "TerID", "equals": "B.TerID", "null_rejecting": false }, { "table": "", "index": "key0", "field": "TerID", "equals": "S.TerID", "null_rejecting": false }, { "table": "", "index": "key0", "field": "TnNr", "equals": "S.TnNr", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "S", "table_scan": { "rows": 1700, "read_cost": 0.2386836, "read_and_compare_cost": 0.2930836 } }, { "table": "", "table_scan": { "rows": 6489, "read_cost": 0.077165043, "read_and_compare_cost": 0.299958369 } } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "S", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1700, "rows_after_filter": 1700, "rows_out": 1700, "cost": 0.2930836, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1700, "rows_out": 1700, "cost": 0.2930836, "uses_join_buffering": false } } }, { "best_access_path": { "table": "", "choose_best_splitting": { "considered_keys": [] }, "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 6489, "rows_after_filter": 6489, "rows_out": 6489, "cost": 8.017628236, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 6489, "rows_out": 6489, "cost": 8.017628236, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "S", "rows_for_plan": 1700, "cost_for_plan": 0.2930836, "rest_of_plan": [ { "plan_prefix": "S", "get_costs_for_tables": [ { "best_access_path": { "table": "", "choose_best_splitting": { "considered_keys": [ { "table_name": "Tn", "index": "Key_TeaID_TerID_Teilnehmer_1", "rec_per_key": 48, "param_tables": 1 }, { "table_name": "Tn", "index": "Key_TnNr_TerID_Teilnehmer_1", "rec_per_key": 1, "param_tables": 1 } ], "refills": 1700, "spl_pd_boundary": 2, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "Te", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.0110178, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.0110178, "uses_join_buffering": false } } }, { "best_access_path": { "table": "Tn", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "Key_TnNr_TerID_Teilnehmer_1", "rows": 1, "cost": 0.00335956, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 0.00335956, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 10135, "rows_after_filter": 10135, "rows_out": 10135, "cost": 1.7449206, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 10135, "rows_out": 10135, "cost": 1.7449206, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.32588933, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.32588933, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "Tn", "rows_for_plan": 1, "cost_for_plan": 0.00335956, "rest_of_plan": [ { "plan_prefix": "Tn", "get_costs_for_tables": [ { "best_access_path": { "table": "Te", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.00171364, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.011171532, "cost_without_join_buffer": 0.0110178, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 0.00171364, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn", "table": "Te", "rows_for_plan": 1, "cost_for_plan": 0.0050732, "rest_of_plan": [ { "plan_prefix": "Tn,Te", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M1", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TnNr", "rows": 169, "cost": 0.24899968, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_TnNr", "chosen": false, "cause": "no predicate for first keypart" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 169, "rows_out": 169, "cost": 0.24899968, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.07741208, "rest_of_plan": [ { "plan_prefix": "Tn,Te,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M1", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TnNr", "rows": 169, "cost": 6.67175552, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 1, "cost": 0.055985195, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 1, "rows_out": 1, "cost": 0.055985195, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M2", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.07314734, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.07314734, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,P", "table": "M1", "rows_for_plan": 39, "cost_for_plan": 0.133397275, "rest_of_plan": [ { "plan_prefix": "Tn,Te,P,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "M2", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.07314734, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.07314734, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,P,M1", "table": "M2", "rows_for_plan": 156, "cost_for_plan": 0.206544615, "rest_of_plan": [ { "plan_prefix": "Tn,Te,P,M1,M2", "get_costs_for_tables": [ { "best_access_path": { "table": "", "choose_best_splitting": { "considered_keys": [ { "table_name": "M", "index": "TnNr", "rec_per_key": 169, "param_tables": 16 }, { "table_name": "P", "index": "TerID", "rec_per_key": 39, "param_tables": 8 } ], "refills": 39, "spl_pd_boundary": 16, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "T", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.006792605, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.006792605, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 10135, "rows_after_filter": 10135, "rows_out": 10135, "cost": 1.7449206, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 10135, "rows_out": 10135, "cost": 1.7449206, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.33529213, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "T", "rows_for_plan": 1, "cost_for_plan": 0.006792605, "rest_of_plan": [ { "plan_prefix": "T", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 5.425395192, "cost_without_join_buffer": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 5.425395192, "uses_join_buffering": true } } } ] }, { "plan_prefix": "T", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.079131485, "rest_of_plan": [ { "plan_prefix": "T,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.28470352, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.08215854, "uses_join_buffering": false } } } ] }, { "plan_prefix": "T,P", "table": "M", "rows_for_plan": 156, "cost_for_plan": 0.161290025, "cost_for_sorting": 0.100964921 } ] }, { "plan_prefix": "T", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 5.432187797, "pruned_by_cost": true, "current_cost": 5.432187797, "best_cost": 0.262254946 } ] }, { "plan_prefix": "", "table": "P", "rows_for_plan": 10135, "cost_for_plan": 1.7449206, "pruned_by_cost": true, "current_cost": 1.7449206, "best_cost": 0.262254946 }, { "plan_prefix": "", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 3.33529213, "pruned_by_cost": true, "current_cost": 3.33529213, "best_cost": 0.262254946 } ] } ], "split_plan_discarded": "constructed unapplicable query plan" }, "plan_details": { "record_count": 156 }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.20139756, "chosen": true }, { "access_type": "ref", "index": "key1", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.20139756, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key2", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.20139756, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key3", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.20139756, "chosen": false, "cause": "cost" }, { "access_type": "scan", "rows": 156, "rows_after_filter": 156, "rows_out": 10.4, "cost": 3.100769625, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 10.4, "rows_out": 10.4, "cost": 0.20139756, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,P,M1,M2", "table": "", "rows_for_plan": 1622.4, "cost_for_plan": 0.407942175, "cost_for_sorting": 1.064810247 } ] } ] }, { "plan_prefix": "Tn,Te,P", "table": "M2", "rows_for_plan": 156, "cost_for_plan": 0.15055942, "pruned_by_heuristic": true } ] }, { "plan_prefix": "Tn,Te", "table": "M1", "rows_for_plan": 169, "cost_for_plan": 0.25407288, "rest_of_plan": [ { "plan_prefix": "Tn,Te,M1", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 169 }, "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 0.23062276, "chosen": true }, { "access_type": "eq_ref", "index": "TerID", "rows": 1, "cost": 0.37607284, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 6.77349472, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "rows_read": 1, "rows_out": 1, "cost": 0.23062276, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,M1", "table": "P", "rows_for_plan": 169, "cost_for_plan": 0.48469564, "rest_of_plan": [ { "plan_prefix": "Tn,Te,M1,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M2", "plan_details": { "record_count": 169 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.26590834, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.90303152, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.25689714, "chosen": true }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.90466992, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.25689714, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,M1,P", "table": "M2", "rows_for_plan": 676, "cost_for_plan": 0.74159278, "rest_of_plan": [ { "plan_prefix": "Tn,Te,M1,P,M2", "get_costs_for_tables": [ { "best_access_path": { "table": "", "choose_best_splitting": { "considered_keys": [ { "table_name": "M", "index": "TnNr", "rec_per_key": 169, "param_tables": 16 }, { "table_name": "P", "index": "TerID", "rec_per_key": 39, "param_tables": 8 } ], "refills": 169, "spl_pd_boundary": 16, "split_plan_search": [ { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "T", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 1, "rows_after_filter": 1, "rows_out": 1, "cost": 0.006792605, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 1, "rows_out": 1, "cost": 0.006792605, "uses_join_buffering": false } } }, { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 10135, "rows_after_filter": 10135, "rows_out": 10135, "cost": 1.7449206, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 10135, "rows_out": 10135, "cost": 1.7449206, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 3.33529213, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "T", "rows_for_plan": 1, "cost_for_plan": 0.006792605, "rest_of_plan": [ { "plan_prefix": "T", "get_costs_for_tables": [ { "best_access_path": { "table": "P", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "TerID", "rows": 39, "cost": 0.07233888, "chosen": true }, { "access_type": "ref", "index": "TerRunde", "rows": 39, "cost": 0.07233888, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 39, "rows_out": 39, "cost": 0.07233888, "uses_join_buffering": false } } }, { "best_access_path": { "table": "M", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", "rows": 22506, "rows_after_filter": 22506, "rows_out": 22506, "cost": 5.425395192, "cost_without_join_buffer": 3.33529213, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 22506, "rows_out": 22506, "cost": 5.425395192, "uses_join_buffering": true } } } ] }, { "plan_prefix": "T", "table": "P", "rows_for_plan": 39, "cost_for_plan": 0.079131485, "rest_of_plan": [ { "plan_prefix": "T,P", "get_costs_for_tables": [ { "best_access_path": { "table": "M", "plan_details": { "record_count": 39 }, "considered_access_paths": [ { "access_type": "ref", "index": "PaID_LiMaNr_MaNr_TnNr", "rows": 4, "cost": 0.08215854, "chosen": true }, { "access_type": "ref", "index": "PaID", "rows": 4, "cost": 0.27896912, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_TnNr", "rows": 4, "cost": 0.28470352, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "idx_PaID_MaNr", "rows": 4, "cost": 0.28060752, "chosen": false, "cause": "cost" }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "rows_read": 4, "rows_out": 4, "cost": 0.08215854, "uses_join_buffering": false } } } ] }, { "plan_prefix": "T,P", "table": "M", "rows_for_plan": 156, "cost_for_plan": 0.161290025, "cost_for_sorting": 0.100964921 } ] }, { "plan_prefix": "T", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 5.432187797, "pruned_by_cost": true, "current_cost": 5.432187797, "best_cost": 0.262254946 } ] }, { "plan_prefix": "", "table": "P", "rows_for_plan": 10135, "cost_for_plan": 1.7449206, "pruned_by_cost": true, "current_cost": 1.7449206, "best_cost": 0.262254946 }, { "plan_prefix": "", "table": "M", "rows_for_plan": 22506, "cost_for_plan": 3.33529213, "pruned_by_cost": true, "current_cost": 3.33529213, "best_cost": 0.262254946 } ] } ], "split_plan_discarded": "constructed unapplicable query plan" }, "plan_details": { "record_count": 676 }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.87272276, "chosen": true }, { "access_type": "ref", "index": "key1", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.87272276, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key2", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.87272276, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "key3", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.4, "cost": 0.87272276, "chosen": false, "cause": "cost" }, { "access_type": "scan", "rows": 156, "rows_after_filter": 156, "rows_out": 10.4, "cost": 12.9499303, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 10.4, "rows_out": 10.4, "cost": 0.87272276, "uses_join_buffering": false } } } ] }, { "plan_prefix": "Tn,Te,M1,P,M2", "table": "", "rows_for_plan": 7030.4, "cost_for_plan": 1.61431554, "pruned_by_cost": true, "current_cost": 1.61431554, "best_cost": 1.472752422 } ] } ] } ] } ] } ] }, { "plan_prefix": "", "table": "P", "rows_for_plan": 10135, "cost_for_plan": 1.7449206, "pruned_by_cost": true, "current_cost": 1.7449206, "best_cost": 1.472752422 }, { "plan_prefix": "", "table": "M1", "rows_for_plan": 22506, "cost_for_plan": 3.32588933, "pruned_by_cost": true, "current_cost": 3.32588933, "best_cost": 1.472752422 } ] } ], "split_materialized": { "table": "Tn", "key": "Key_TnNr_TerID_Teilnehmer_1", "org_cost": 0.20139756, "postjoin_cost": 0.000326608, "one_splitting_cost": 0.201724168, "unsplit_postjoin_cost": 3.053244693, "unsplit_cost": 3.858834933, "rows": 1, "refills": 1700, "total_splitting_cost": 342.9310856, "chosen": false } }, "plan_details": { "record_count": 1700 }, "considered_access_paths": [ { "access_type": "ref", "index": "key0", "rec_per_key_stats_missing": true, "used_range_estimates": false, "reason": "not available", "rows": 10.01388889, "cost": 6.032547489, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 6489, "rows_after_filter": 6489, "rows_out": 10.01388889, "cost": 1032.553806, "cost_without_join_buffer": 509.929228, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", "rows_read": 10.01388889, "rows_out": 10.01388889, "cost": 6.032547489, "uses_join_buffering": false } } } ] }, { "plan_prefix": "S", "table": "", "rows_for_plan": 17023.61111, "cost_for_plan": 6.325631089 } ] }, { "plan_prefix": "", "table": "", "rows_for_plan": 6489, "cost_for_plan": 8.017628236, "pruned_by_cost": true, "current_cost": 8.017628236, "best_cost": 6.325631089 } ] }, { "best_join_order": ["S", ""], "rows": 17023.61111, "cost": 6.325631089 }, { "best_join_order": ["T", "P", "M"], "rows": 156, "cost": 0.262254946 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "P.TerID = T.TerID and M.PaID = P.PaID and P.PaOption & 1 = 1" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "T", "attached_condition": null }, { "table": "P", "attached_condition": "P.PaOption & 1 = 1" }, { "table": "M", "attached_condition": null } ] } }, { "make_join_readinfo": [] }, { "prepare_sum_aggregators": { "function": "sum(case when ifnull(P.PaSiegMa,0) = 0 then 0 else case when P.PaSiegMa = M.MaNr then 1 else 0 end end)", "aggregator_type": "simple" } }, { "prepare_sum_aggregators": { "function": "sum(case when ifnull(P.PaSiegMa,0) = 0 then 0 else case when P.PaSiegMa = M.MaNr then P.PaSiegPunkte - P.PaPunkte else P.PaPunkte - P.PaSiegPunkte end end)", "aggregator_type": "simple" } }, { "prepare_sum_aggregators": { "function": "sum(case when ifnull(P.PaSiegMa,0) <> 0 then 1 else 0 end)", "aggregator_type": "simple" } }, { "prepare_sum_aggregators": { "function": "max(case when ifnull(P.PaSiegMa,0) = M.MaNr then P.PaRunde else 0 end)", "aggregator_type": "simple" } }, { "prepare_sum_aggregators": { "function": "max(case when ifnull(P.PaSiegMa,0) > 0 and ifnull(P.PaSiegMa,0) <> M.MaNr then P.PaRunde else 0 end)", "aggregator_type": "simple" } }, { "best_join_order": ["Te", "P", "M1", "Tn", "M2", ""], "rows": 1622.4, "cost": 1.472752422 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "M1.PaID = P.PaID and P.TerID = Te.TerID and Tn.TerID = Te.TerID and Tn.TnNr = M1.TnNr and P.PaOption & 1 = 1 and Te.TuOptionen & (8 | 16) > 0" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "M2", "resulting_condition": "M2.PaID = P.PaID and M2.TnNr <> M1.TnNr" } }, { "substitute_best_equal": { "condition": "ON expr", "attached_to": "S", "resulting_condition": "S.TerID = Te.TerID and S.TnNr = M2.TnNr" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "Te", "attached_condition": "Te.TuOptionen & (8 | 16) > 0" }, { "table": "P", "attached_condition": "P.PaOption & 1 = 1" }, { "table": "M1", "attached_condition": null }, { "table": "Tn", "attached_condition": null }, { "table": "M2", "attached_condition": "trigcond(M2.TnNr <> M1.TnNr)" }, { "table": "", "attached_condition": "trigcond(S.TnNr = M2.TnNr)" } ] } }, { "make_join_readinfo": [] }, { "prepare_sum_aggregators": { "function": "min(S.Siege)", "aggregator_type": "simple" } }, { "prepare_sum_aggregators": { "function": "sum(ifnull(S.Siege,0))", "aggregator_type": "simple" } }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "B.TerID = S.TerID and B.TnNr = S.TnNr" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "S", "attached_condition": null }, { "table": "", "attached_condition": null } ] } }, { "make_join_readinfo": [] } ] } }, { "join_execution": { "select_id": 1, "steps": [ { "join_execution": { "select_id": 2, "steps": [ { "join_execution": { "select_id": 3, "steps": [] } } ] } } ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.003 sec) MariaDB [test]> \t