10.11-clean-analyze.txt

+------+-------------+----------+--------+---------------------------+-------------+---------+---------------------------+---------+------------+----------+------------+-------------------------------------------------+
| id   | select_type | table    | type   | possible_keys             | key         | key_len | ref                       | rows    | r_rows     | filtered | r_filtered | Extra                                           |
+------+-------------+----------+--------+---------------------------+-------------+---------+---------------------------+---------+------------+----------+------------+-------------------------------------------------+
|    1 | SIMPLE      | region   | ALL    | PRIMARY                   | NULL        | NULL    | NULL                      | 5       | 5.00       |    20.00 |      20.00 | Using where; Using temporary; Using filesort    |
|    1 | SIMPLE      | part     | ALL    | PRIMARY                   | NULL        | NULL    | NULL                      | 2000000 | 2000000.00 |    16.41 |       0.68 | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | lineitem | ref    | PRIMARY,i_l_suppkey_p.... | i_l_partkey | 5       | dbt3.part.p_partkey       | 29      | 30.06      |   100.00 |     100.00 | Using where                                     |
|    1 | SIMPLE      | supplier | eq_ref | PRIMARY,i_s_nationkey     | PRIMARY     | 4       | dbt3.lineitem.l_suppkey   | 1       | 1.00       |   100.00 |     100.00 | Using where                                     |
|    1 | SIMPLE      | orders   | eq_ref | PRIMARY,i_o_orderdate,... | PRIMARY     | 4       | dbt3.lineitem.l_orderkey  | 1       | 1.00       |    49.60 |      30.34 | Using where                                     |
|    1 | SIMPLE      | customer | eq_ref | PRIMARY,i_c_nationkey     | PRIMARY     | 4       | dbt3.orders.o_custkey     | 1       | 1.00       |   100.00 |     100.00 | Using where                                     |
|    1 | SIMPLE      | n1       | eq_ref | PRIMARY,i_n_regionkey     | PRIMARY     | 4       | dbt3.customer.c_nationkey | 1       | 1.00       |   100.00 |      19.84 | Using where                                     |
|    1 | SIMPLE      | n2       | eq_ref | PRIMARY                   | PRIMARY     | 4       | dbt3.supplier.s_nationkey | 1       | 1.00       |   100.00 |     100.00 |                                                 |
+------+-------------+----------+--------+---------------------------+-------------+---------+---------------------------+---------+------------+----------+------------+-------------------------------------------------+

{
  "query_optimization": {
    "r_total_time_ms": 6.51165352
  },
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 4567.734018,
    "filesort": {
      "sort_key": "year(orders.o_orderDATE)",
      "r_loops": 1,
      "r_total_time_ms": 0.011238554,
      "r_used_priority_queue": false,
      "r_output_rows": 2,
      "r_buffer_size": "320",
      "r_sort_mode": "sort_key,rowid",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "region",
              "access_type": "ALL",
              "possible_keys": ["PRIMARY"],
              "r_loops": 1,
              "rows": 5,
              "r_rows": 5,
              "r_table_time_ms": 0.026618002,
              "r_other_time_ms": 0.009449625,
              "filtered": 20,
              "r_filtered": 20,
              "attached_condition": "region.r_name = 'MIDDLE EAST'"
            }
          },
          {
            "block-nl-join": {
              "table": {
                "table_name": "part",
                "access_type": "ALL",
                "possible_keys": ["PRIMARY"],
                "r_loops": 1,
                "rows": 2000000,
                "r_rows": 2000000,
                "r_table_time_ms": 431.838558,
                "r_other_time_ms": 88.35411719,
                "filtered": 16.40625,
                "r_filtered": 0.6798,
                "attached_condition": "part.p_type = 'PROMO POLISHED BRASS'"
              },
              "buffer_type": "flat",
              "buffer_size": "352",
              "join_type": "BNL",
              "r_filtered": 100
            }
          },
          {
            "table": {
              "table_name": "lineitem",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "i_l_suppkey_partkey",
                "i_l_partkey",
                "i_l_suppkey",
                "i_l_orderkey",
                "i_l_orderkey_quantity"
              ],
              "key": "i_l_partkey",
              "key_length": "5",
              "used_key_parts": ["l_partkey"],
              "ref": ["dbt3.part.p_partkey"],
              "r_loops": 13596,
              "rows": 29,
              "r_rows": 30.05751692,
              "r_table_time_ms": 1484.102954,
              "r_other_time_ms": 46.35819604,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "lineitem.l_suppkey is not null"
            }
          },
          {
            "table": {
              "table_name": "supplier",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "i_s_nationkey"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["s_suppkey"],
              "ref": ["dbt3.lineitem.l_suppkey"],
              "r_loops": 408662,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 513.1091431,
              "r_other_time_ms": 37.65003189,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "supplier.s_nationkey is not null"
            }
          },
          {
            "table": {
              "table_name": "orders",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "i_o_orderdate", "i_o_custkey"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["o_orderkey"],
              "ref": ["dbt3.lineitem.l_orderkey"],
              "r_loops": 408662,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 1247.736177,
              "r_other_time_ms": 57.12060394,
              "filtered": 49.59550095,
              "r_filtered": 30.33851936,
              "attached_condition": "orders.o_orderDATE between '1995-01-01' and '1996-12-31' and orders.o_custkey is not null"
            }
          },
          {
            "table": {
              "table_name": "customer",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "i_c_nationkey"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["c_custkey"],
              "ref": ["dbt3.orders.o_custkey"],
              "r_loops": 123982,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 496.3957187,
              "r_other_time_ms": 15.46858372,
              "filtered": 100,
              "r_filtered": 100,
              "attached_condition": "customer.c_nationkey is not null"
            }
          },
          {
            "table": {
              "table_name": "n1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "i_n_regionkey"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["n_nationkey"],
              "ref": ["dbt3.customer.c_nationkey"],
              "r_loops": 123982,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 90.30976472,
              "r_other_time_ms": 22.57101505,
              "filtered": 100,
              "r_filtered": 19.84481618,
              "attached_condition": "n1.n_regionkey = region.r_regionkey"
            }
          },
          {
            "table": {
              "table_name": "n2",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["n_nationkey"],
              "ref": ["dbt3.supplier.s_nationkey"],
              "r_loops": 24604,
              "rows": 1,
              "r_rows": 1,
              "r_table_time_ms": 16.72933257,
              "r_other_time_ms": 19.90050324,
              "filtered": 100,
              "r_filtered": 100
            }
          }
        ]
      }
    }
  }
}