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_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 452.14,
    "const_condition": "1",
    "filesort": {
      "sort_key": "U.lastname, U.`id`, S.firstname",
      "r_loops": 1,
      "r_total_time_ms": 17.517,
      "r_limit": 14050,
      "r_used_priority_queue": false,
      "r_output_rows": 15000,
      "r_sort_passes": 1,
      "r_buffer_size": "2047Kb",
      "temporary_table": {
        "table": {
          "table_name": "U",
          "access_type": "ALL",
          "r_loops": 1,
          "rows": 15497,
          "r_rows": 15000,
          "r_total_time_ms": 3.7186,
          "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.9999,
          "r_total_time_ms": 14.535,
          "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.9999,
          "r_total_time_ms": 9.5196,
          "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.0213,
          "r_total_time_ms": 40.55,
          "filtered": 100,
          "r_filtered": 100
        },
        "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_total_time_ms": 20.833,
          "filtered": 100,
          "r_filtered": 100,
          "attached_condition": "trigcond(trigcond(H1.eventId is not null))"
        },
        "table": {
          "table_name": "<derived3>",
          "access_type": "ref",
          "possible_keys": ["key0"],
          "key": "key0",
          "key_length": "4",
          "used_key_parts": ["uId"],
          "ref": ["MDEV32784.U.id"],
          "r_loops": 45319,
          "rows": 2,
          "r_rows": 1,
          "r_total_time_ms": 11.679,
          "filtered": 100,
          "r_filtered": 33.099,
          "attached_condition": "trigcond(H2.`id` = H1.`id`)",
          "materialized": {
            "lateral": 1,
            "query_block": {
              "select_id": 3,
              "r_loops": 45319,
              "r_total_time_ms": 277.43,
              "filesort": {
                "sort_key": "`History`.uId",
                "r_loops": 45319,
                "r_total_time_ms": 18.473,
                "r_used_priority_queue": false,
                "r_output_rows": 1,
                "r_buffer_size": "(varied across executions)",
                "temporary_table": {
                  "table": {
                    "table_name": "History",
                    "access_type": "ref",
                    "possible_keys": ["fkHistory2User"],
                    "key": "fkHistory2User",
                    "key_length": "4",
                    "used_key_parts": ["uId"],
                    "ref": ["MDEV32784.U.id"],
                    "r_loops": 45319,
                    "rows": 1,
                    "r_rows": 3.0282,
                    "r_total_time_ms": 90.582,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "`History`.uId <=> U.`id`"
                  },
                  "table": {
                    "table_name": "<derived5>",
                    "access_type": "ref",
                    "possible_keys": ["key0"],
                    "key": "key0",
                    "key_length": "37",
                    "used_key_parts": ["uId", "eDate"],
                    "ref": ["MDEV32784.History.uId", "func"],
                    "r_loops": 137233,
                    "rows": 2,
                    "r_rows": 0.3302,
                    "r_total_time_ms": 33.44,
                    "filtered": 100,
                    "r_filtered": 100,
                    "attached_condition": "ifnull(`History`.eDate,'1970-01-01') = SH2.eDate",
                    "materialized": {
                      "query_block": {
                        "select_id": 5,
                        "r_loops": 1,
                        "r_total_time_ms": 34.425,
                        "filesort": {
                          "sort_key": "`History`.uId",
                          "r_loops": 1,
                          "r_total_time_ms": 1.0312,
                          "r_used_priority_queue": false,
                          "r_output_rows": 15000,
                          "r_buffer_size": "293Kb",
                          "temporary_table": {
                            "table": {
                              "table_name": "History",
                              "access_type": "ALL",
                              "possible_keys": ["fkHistory2User"],
                              "r_loops": 1,
                              "rows": 45782,
                              "r_rows": 45319,
                              "r_total_time_ms": 7.3346,
                              "filtered": 100,
                              "r_filtered": 100,
                              "attached_condition": "ifnull(`History`.eDate,'1970-01-01') <= '2024-02-10'"
                            }
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}




********************************************************************************
ANALYZE FORMAT=JSON SELECT * FROM `vRecord` WHERE (`eventId` = 5) LIMIT 10 OFFSET 10000\G


{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 107.16,
    "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": 22891,
      "r_rows": 10220,
      "r_total_time_ms": 6.5491,
      "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_total_time_ms": 0.0057,
      "filtered": 100,
      "r_filtered": 100
    },
    "table": {
      "table_name": "<derived3>",
      "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.9795,
      "r_total_time_ms": 2.6591,
      "filtered": 100,
      "r_filtered": 100,
      "materialized": {
        "lateral": 1,
        "query_block": {
          "select_id": 3,
          "r_loops": 10220,
          "r_total_time_ms": 93.991,
          "filesort": {
            "sort_key": "`History`.uId",
            "r_loops": 10220,
            "r_total_time_ms": 4.1774,
            "r_used_priority_queue": false,
            "r_output_rows": 1,
            "r_buffer_size": "(varied across executions)",
            "temporary_table": {
              "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.0205,
                "r_total_time_ms": 23.689,
                "filtered": 100,
                "r_filtered": 100,
                "attached_condition": "`History`.uId <=> H1.uId"
              },
              "table": {
                "table_name": "<derived5>",
                "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.3311,
                "r_total_time_ms": 9.3876,
                "filtered": 100,
                "r_filtered": 100,
                "attached_condition": "ifnull(`History`.eDate,'1970-01-01') = SH2.eDate",
                "materialized": {
                  "query_block": {
                    "select_id": 5,
                    "r_loops": 1,
                    "r_total_time_ms": 35.376,
                    "filesort": {
                      "sort_key": "`History`.uId",
                      "r_loops": 1,
                      "r_total_time_ms": 1.218,
                      "r_used_priority_queue": false,
                      "r_output_rows": 15000,
                      "r_buffer_size": "293Kb",
                      "temporary_table": {
                        "table": {
                          "table_name": "History",
                          "access_type": "ALL",
                          "possible_keys": ["fkHistory2User"],
                          "r_loops": 1,
                          "rows": 45782,
                          "r_rows": 45319,
                          "r_total_time_ms": 7.6039,
                          "filtered": 100,
                          "r_filtered": 100,
                          "attached_condition": "ifnull(`History`.eDate,'1970-01-01') <= '2024-02-10'"
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}