ANALYZE FORMAT=JSON SELECT * FROM User U LEFT JOIN UserReportTo RT ON (U.id = RT.supId) LEFT JOIN User S ON ((S.id = RT.subId) AND (S.deletedTime IS NULL) AND (S.purgedTime IS NULL)) LEFT JOIN vRecord R ON (U.id = R.uId) ORDER BY U.lastname ASC, U.id ASC, S.firstname ASC LIMIT 14000, 50\G { "query_optimization": { "r_total_time_ms": 0.328070873 }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 255.1474397, "const_condition": "1", "filesort": { "sort_key": "U.lastname, U.`id`, S.firstname", "r_loops": 1, "r_total_time_ms": 7.168844971, "r_limit": 14050, "r_used_priority_queue": false, "r_output_rows": 15000, "r_buffer_size": "2047Kb", "r_sort_mode": "packed_sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "U", "access_type": "ALL", "r_loops": 1, "rows": 15074, "r_rows": 15000, "r_table_time_ms": 2.798711776, "r_other_time_ms": 1.341987286, "r_engine_stats": { "pages_accessed": 49 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "RT", "access_type": "ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["supId"], "ref": ["MDEV32784.U.id"], "r_loops": 15000, "rows": 1, "r_rows": 0.999933333, "r_table_time_ms": 15.35787939, "r_other_time_ms": 1.985955509, "r_engine_stats": { "pages_accessed": 30060 }, "filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "S", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["MDEV32784.RT.subId"], "r_loops": 15000, "rows": 1, "r_rows": 0.999933333, "r_table_time_ms": 13.08156173, "r_other_time_ms": 2.514662936, "r_engine_stats": { "pages_accessed": 30092 }, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(S.deletedTime is null and S.purgedTime is null and trigcond(RT.subId is not null))" } }, { "table": { "table_name": "H1", "access_type": "ref", "possible_keys": ["PRIMARY", "fkHistory2User"], "key": "fkHistory2User", "key_length": "4", "used_key_parts": ["uId"], "ref": ["MDEV32784.U.id"], "r_loops": 15000, "rows": 1, "r_rows": 3.021266667, "r_table_time_ms": 52.02023741, "r_other_time_ms": 5.802762369, "r_engine_stats": { "pages_accessed": 120766 }, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(H1.uId is not null and H1.`id` is not null)" } }, { "table": { "table_name": "HE", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["MDEV32784.H1.eventId"], "r_loops": 45319, "rows": 1, "r_rows": 1, "r_table_time_ms": 20.43800379, "r_other_time_ms": 5.207206298, "r_engine_stats": { "pages_accessed": 45319 }, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(trigcond(H1.eventId is not null))" } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key1"], "key": "key1", "key_length": "13", "used_key_parts": ["uId", "id"], "ref": ["MDEV32784.H1.uId", "MDEV32784.H1.id"], "r_loops": 45319, "rows": 10, "r_rows": 0.330987003, "r_table_time_ms": 7.017335474, "r_other_time_ms": 21.26463525, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(H2.uId = U.`id`)", "materialized": { "query_block": { "select_id": 3, "r_loops": 1, "r_total_time_ms": 94.63827695, "filesort": { "sort_key": "`History`.uId", "r_loops": 1, "r_total_time_ms": 0.993103618, "r_used_priority_queue": false, "r_output_rows": 15000, "r_buffer_size": "293Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "r_loops": 1, "rows": 15974, "r_rows": 15000, "r_table_time_ms": 0.47011639, "r_other_time_ms": 3.392897493, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 5, "r_loops": 1, "r_total_time_ms": 31.59318878, "filesort": { "sort_key": "`History`.uId", "r_loops": 1, "r_total_time_ms": 0.95277694, "r_used_priority_queue": false, "r_output_rows": 15000, "r_buffer_size": "293Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "History", "access_type": "ALL", "possible_keys": ["fkHistory2User"], "r_loops": 1, "rows": 15974, "r_rows": 45319, "r_table_time_ms": 6.660467023, "r_other_time_ms": 21.8449568, "r_engine_stats": { "pages_accessed": 141 }, "filtered": 100, "r_filtered": 100, "attached_condition": "ifnull(`History`.eDate,'1970-01-01') <= '2024-02-10'" } } ] } } } } } }, { "table": { "table_name": "History", "access_type": "ref", "possible_keys": ["fkHistory2User"], "key": "fkHistory2User", "key_length": "4", "used_key_parts": ["uId"], "ref": ["SH2.uId"], "r_loops": 15000, "rows": 1, "r_rows": 3.021266667, "r_table_time_ms": 49.24232836, "r_other_time_ms": 8.046838829, "r_engine_stats": { "pages_accessed": 120766 }, "filtered": 100, "r_filtered": 33.09870032, "attached_condition": "ifnull(`History`.eDate,'1970-01-01') = SH2.eDate" } } ] } } } } } } ] } } } } ******************************************************************************** ANALYZE FORMAT=JSON SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 OFFSET 10000\G { "query_optimization": { "r_total_time_ms": 0.316159147 }, "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 898978.7908, "nested_loop": [ { "table": { "table_name": "H1", "access_type": "ref", "possible_keys": ["PRIMARY", "fkHistory2User", "fkHistory2HistoryEvent"], "key": "fkHistory2HistoryEvent", "key_length": "5", "used_key_parts": ["eventId"], "ref": ["const"], "r_loops": 1, "rows": 7987, "r_rows": 10220, "r_table_time_ms": 24.97029957, "r_other_time_ms": 31.41191507, "r_engine_stats": { "pages_accessed": 20463 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "HE", "access_type": "const", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "ref": ["const"], "r_loops": 10220, "rows": 1, "r_rows": 1, "r_table_time_ms": 0.0041686, "r_other_time_ms": 0.004843229, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "13", "used_key_parts": ["uId", "id"], "ref": ["MDEV32784.H1.uId", "MDEV32784.H1.id"], "r_loops": 10220, "rows": 2, "r_rows": 0.979452055, "r_table_time_ms": 9.556778424, "r_other_time_ms": 14.15472463, "filtered": 100, "r_filtered": 100, "materialized": { "lateral": 1, "query_block": { "select_id": 3, "r_loops": 10220, "r_total_time_ms": 898903.4889, "filesort": { "sort_key": "`History`.uId", "r_loops": 10220, "r_total_time_ms": 20.8269322, "r_used_priority_queue": false, "r_output_rows": 1, "r_buffer_size": "(varied across executions)", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "History", "access_type": "ref", "possible_keys": ["fkHistory2User"], "key": "fkHistory2User", "key_length": "4", "used_key_parts": ["uId"], "ref": ["MDEV32784.H1.uId"], "r_loops": 10220, "rows": 1, "r_rows": 3.020547945, "r_table_time_ms": 162.764577, "r_other_time_ms": 140.5697195, "r_engine_stats": { "pages_accessed": 82268 }, "filtered": 100, "r_filtered": 100, "attached_condition": "`History`.uId <=> H1.uId" } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "37", "used_key_parts": ["uId", "eDate"], "ref": ["MDEV32784.History.uId", "func"], "r_loops": 30870, "rows": 2, "r_rows": 0.33106576, "r_table_time_ms": 66323.36502, "r_other_time_ms": 59841.22544, "filtered": 100, "r_filtered": 100, "attached_condition": "ifnull(`History`.eDate,'1970-01-01') = SH2.eDate", "materialized": { "query_block": { "select_id": 5, "r_loops": 30870, "r_total_time_ms": 898278.5572, "filesort": { "sort_key": "`History`.uId", "r_loops": 30870, "r_total_time_ms": 29693.96638, "r_used_priority_queue": false, "r_output_rows": 15000, "r_buffer_size": "(varied across executions)", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "History", "access_type": "ALL", "possible_keys": ["fkHistory2User"], "r_loops": 30870, "rows": 15974, "r_rows": 45319, "r_table_time_ms": 171503.524, "r_other_time_ms": 570736.5886, "r_engine_stats": { "pages_accessed": 4352670 }, "filtered": 100, "r_filtered": 100, "attached_condition": "ifnull(`History`.eDate,'1970-01-01') <= '2024-02-10'" } } ] } } } } } } ] } } } } } } ] } }