MariaDB [mariadb_bug_test]> analyze format=JSON SELECT /*+ index(b)*/ a.reference_number, b.validation_date FROM table_a a LEFT OUTER JOIN table_b b ON (a.id = b.fk_id);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ANALYZE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_optimization": {
    "r_total_time_ms": 0.126332639
  },
  "query_block": {
    "select_id": 1,
    "cost": 2458914.966,
    "r_loops": 1,
    "r_total_time_ms": 60.53665672,
    "const_condition": "1",
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "ALL",
          "loops": 1,
          "r_loops": 1,
          "rows": 24879,
          "r_rows": 25000,
          "cost": 4.1481926,
          "r_table_time_ms": 2.020415038,
          "r_other_time_ms": 0.960037411,
          "r_engine_stats": {
            "pages_accessed": 59
          },
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      },
      {
        "table": {
          "table_name": "b",
          "access_type": "ref",
          "possible_keys": ["idx_fk"],
          "key": "idx_fk",
          "key_length": "5",
          "used_key_parts": ["fk_id"],
          "ref": ["mariadb_bug_test.a.id"],
          "loops": 24879,
          "r_loops": 25000,
          "rows": 99484,
          "r_rows": 4,
          "cost": 2458910.818,
          "r_table_time_ms": 53.43877151,
          "r_other_time_ms": 4.107968207,
          "r_engine_stats": {
            "pages_accessed": 250089
          },
          "filtered": 100,
          "r_total_filtered": 100,
          "r_filtered": 100
        }
      }
    ]
  }
} |