MariaDB [test]> analyze format=json SELECT S.*, B.* FROM Teilnehmer_1 S INNER JOIN Buchholzpunkte B ON B.TerID = S.TerID AND B.TnNr = S.TnNr; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 4.501102874 }, "query_block": { "select_id": 1, "cost": 0.7566634, "r_loops": 1, "r_total_time_ms": 19075.93232, "nested_loop": [ { "table": { "table_name": "S", "access_type": "ALL", "possible_keys": [ "Key_TeaID_TerID_Teilnehmer_1", "Key_TnNr_TerID_Teilnehmer_1", "TerID_Teilnehmer_1", "Ter_Tea_Teilnehmer_1" ], "loops": 1, "r_loops": 1, "rows": 1700, "r_rows": 1700, "cost": 0.2930836, "r_table_time_ms": 0.98086321, "r_other_time_ms": 0.550719658, "r_engine_stats": { "pages_accessed": 7 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "8", "used_key_parts": ["TerID", "TnNr"], "ref": ["test.S.TerID", "test.S.TnNr"], "loops": 1700, "r_loops": 1700, "rows": 1, "r_rows": 0.078823529, "cost": 0.4635798, "r_table_time_ms": 0.322312188, "r_other_time_ms": 0.826678656, "filtered": 100, "r_filtered": 100, "materialized": { "lateral": 1, "query_block": { "select_id": 2, "cost": 0.713259577, "r_loops": 1700, "r_total_time_ms": 19073.26515, "nested_loop": [ { "table": { "table_name": "Tn", "access_type": "eq_ref", "possible_keys": [ "Key_TeaID_TerID_Teilnehmer_1", "Key_TnNr_TerID_Teilnehmer_1", "TerID_Teilnehmer_1", "Ter_Tea_Teilnehmer_1" ], "key": "Key_TnNr_TerID_Teilnehmer_1", "key_length": "8", "used_key_parts": ["TerID", "TnNr"], "ref": ["test.S.TerID", "test.S.TnNr"], "loops": 1, "r_loops": 1700, "rows": 1, "r_rows": 1, "cost": 0.00335956, "r_table_time_ms": 5.069037896, "r_other_time_ms": 2.368814453, "r_engine_stats": { "pages_accessed": 6802 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "Te", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["TerID"], "ref": ["test.Tn.TerID"], "loops": 1, "r_loops": 1700, "r_table_loops": 35, "rows": 1, "r_rows": 1, "cost": 0.00171364, "r_table_time_ms": 0.070242214, "r_other_time_ms": 0.061434474, "r_engine_stats": { "pages_accessed": 70 }, "filtered": 100, "r_filtered": 7.882352941, "attached_condition": "Te.TuOptionen & 24 > 0" } }, { "table": { "table_name": "P", "access_type": "ref", "possible_keys": ["PRIMARY", "TerID", "TerRunde"], "key": "TerID", "key_length": "4", "used_key_parts": ["TerID"], "ref": ["test.Tn.TerID"], "loops": 1, "r_loops": 134, "rows": 39, "r_rows": 62.80597015, "cost": 0.07233888, "r_table_time_ms": 9.424363039, "r_other_time_ms": 2.438097674, "r_engine_stats": { "pages_accessed": 17100 }, "filtered": 100, "r_filtered": 68.22718631, "attached_condition": "P.PaOption & 1 = 1" } }, { "table": { "table_name": "M1", "access_type": "ref", "possible_keys": [ "PaID_LiMaNr_MaNr_TnNr", "PaID", "TnNr", "idx_PaID_TnNr", "idx_PaID_MaNr" ], "key": "idx_PaID_TnNr", "key_length": "8", "used_key_parts": ["PaID", "TnNr"], "ref": ["test.P.PaID", "test.Tn.TnNr"], "loops": 39, "r_loops": 5742, "rows": 1, "r_rows": 0.070010449, "cost": 0.069911595, "r_table_time_ms": 7.22062324, "r_other_time_ms": 0.647470886, "r_engine_stats": { "pages_accessed": 11501 }, "filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "M2", "access_type": "ref", "possible_keys": [ "PaID_LiMaNr_MaNr_TnNr", "PaID", "idx_PaID_TnNr", "idx_PaID_MaNr" ], "key": "PaID_LiMaNr_MaNr_TnNr", "key_length": "4", "used_key_parts": ["PaID"], "ref": ["test.P.PaID"], "loops": 39, "r_loops": 402, "rows": 4, "r_rows": 1.985074627, "cost": 0.08707374, "r_table_time_ms": 1.528717916, "r_other_time_ms": 1.59819919, "r_engine_stats": { "pages_accessed": 804 }, "filtered": 100, "r_filtered": 49.62406015, "attached_condition": "trigcond(M2.TnNr <> Tn.TnNr)", "using_index": true } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "5", "used_key_parts": ["TerID"], "ref": ["test.Tn.TerID"], "loops": 156, "r_loops": 402, "rows": 3, "r_rows": 28.07462687, "cost": 0.081890952, "r_table_time_ms": 311.5234726, "r_other_time_ms": 1406.332191, "filtered": 100, "r_filtered": 3.50877193, "attached_condition": "trigcond(S.TnNr = M2.TnNr)", "materialized": { "query_block": { "select_id": 3, "cost": 0.297718301, "r_loops": 402, "r_total_time_ms": 19035.11143, "filesort": { "sort_key": "P.TerID desc, M.TnNr", "r_loops": 402, "r_total_time_ms": 499.9599232, "r_used_priority_queue": false, "r_output_rows": 13736, "r_buffer_size": "(varied across executions)", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "P", "access_type": "ALL", "possible_keys": ["PRIMARY", "TerID", "TerRunde"], "loops": 1, "r_loops": 402, "rows": 10135, "r_rows": 9983, "cost": 1.7449206, "r_table_time_ms": 856.307117, "r_other_time_ms": 449.1434242, "r_engine_stats": { "pages_accessed": 14874 }, "filtered": 100, "r_filtered": 62.32595412, "attached_condition": "P.PaOption & 1 = 1" } }, { "table": { "table_name": "T", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["TerID"], "ref": ["test.P.TerID"], "loops": 10135, "r_loops": 2501244, "r_table_loops": 82812, "rows": 1, "r_rows": 1, "cost": 9.067607, "r_table_time_ms": 85.49486255, "r_other_time_ms": 7.028499937, "r_engine_stats": { "pages_accessed": 166428 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "M", "access_type": "ref", "possible_keys": [ "PaID_LiMaNr_MaNr_TnNr", "PaID", "TnNr", "idx_PaID_TnNr", "idx_PaID_MaNr" ], "key": "PaID_LiMaNr_MaNr_TnNr", "key_length": "4", "used_key_parts": ["PaID"], "ref": ["test.P.PaID"], "loops": 10135, "r_loops": 2501244, "rows": 4, "r_rows": 4.986981678, "cost": 14.3762075, "r_table_time_ms": 5079.721687, "r_other_time_ms": 10339.14591, "r_engine_stats": { "pages_accessed": 5027010 }, "filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } } } ] } } } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (19.069 sec) MariaDB [test]> tee /tmp/optimizer_trace_second_run.txt