MariaDB [tpch]> source Q7-json.sql; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.582818165 }, "query_block": { "select_id": 1, "cost": 696.1899911, "r_loops": 1, "r_total_time_ms": 663.9698508, "filesort": { "sort_key": "n1.N_NAME, n2.N_NAME, extract(year from LINEITEM.L_SHIPDATE)", "r_loops": 1, "r_total_time_ms": 0.015217509, "r_used_priority_queue": false, "r_output_rows": 4, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "n2", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "r_loops": 1, "rows": 25, "r_rows": 25, "cost": 0.0149298, "r_table_time_ms": 0.032036932, "r_other_time_ms": 0.018964676, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 8, "r_total_filtered": 8, "attached_condition": "n2.N_NAME = 'INDIA' or n2.N_NAME = 'JAPAN'", "r_filtered": 8 } }, { "table": { "table_name": "CUSTOMER", "access_type": "ref", "possible_keys": ["PRIMARY", "CUSTOMER_NATION_FKIDX"], "key": "CUSTOMER_NATION_FKIDX", "key_length": "5", "used_key_parts": ["C_NATIONKEY"], "ref": ["tpch.n2.N_NATIONKEY"], "loops": 2, "r_loops": 2, "rows": 6000, "r_rows": 6050, "cost": 1.77035024, "r_table_time_ms": 1.649376075, "r_other_time_ms": 0.742753238, "r_engine_stats": { "pages_accessed": 18 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "ORDERS", "access_type": "ref", "possible_keys": ["PRIMARY", "ORDERS_FK1"], "key": "ORDERS_FK1", "key_length": "4", "used_key_parts": ["O_CUSTKEY"], "ref": ["tpch.CUSTOMER.C_CUSTKEY"], "loops": 12000, "r_loops": 12100, "rows": 15, "r_rows": 10.05760331, "cost": 37.5142376, "r_table_time_ms": 34.47149632, "r_other_time_ms": 6.653618782, "r_engine_stats": { "pages_accessed": 36527 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "table": { "table_name": "LINEITEM", "access_type": "ref", "possible_keys": ["PRIMARY", "IDX_LINEITEM_ORDERKEY_FKIDX"], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["L_ORDERKEY"], "ref": ["tpch.ORDERS.O_ORDERKEY"], "loops": 180000, "r_loops": 121697, "rows": 4, "r_rows": 3.998545568, "cost": 305.0023696, "r_table_time_ms": 370.0296675, "r_other_time_ms": 33.33873468, "r_engine_stats": { "pages_accessed": 370439 }, "filtered": 29.92282104, "r_total_filtered": 30.75228468, "attached_condition": "LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'", "r_filtered": 30.75228468 } }, { "table": { "table_name": "SUPPLIER", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "SUPPLIER_NATION_FKIDX"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["S_SUPPKEY"], "ref": ["tpch.LINEITEM.L_SUPPKEY"], "loops": 215455.0776, "r_loops": 149644, "r_table_loops": 149632, "rows": 1, "r_rows": 1, "cost": 192.8435308, "r_table_time_ms": 124.7374349, "r_other_time_ms": 28.63137594, "r_engine_stats": { "pages_accessed": 302453 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "n1", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 215455.0776, "r_loops": 42, "rows": 25, "r_rows": 25, "cost": 159.0445731, "r_table_time_ms": 0.256127506, "r_other_time_ms": 0.230075333, "r_engine_stats": { "pages_accessed": 42 }, "filtered": 4, "r_total_filtered": 8, "attached_condition": "n1.N_NAME = 'JAPAN' or n1.N_NAME = 'INDIA'", "r_filtered": 8 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "attached_condition": "n1.N_NATIONKEY = SUPPLIER.S_NATIONKEY and (n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN')", "r_loops": 149644, "r_filtered": 1.991727032, "r_unpack_time_ms": 45.26747681, "r_other_time_ms": 17.86810754, "r_effective_rows": 2 } } ] } } } } | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.664 sec) MariaDB [tpch]> notee