MariaDB [tpch]> source Q9-json.sql +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.541379709 }, "query_block": { "select_id": 1, "cost": 1228.605973, "r_loops": 1, "r_total_time_ms": 1845.026336, "filesort": { "sort_key": "NATION.N_NAME, extract(year from ORDERS.O_ORDERDATE) desc", "r_loops": 1, "r_total_time_ms": 0.038852236, "r_used_priority_queue": false, "r_output_rows": 175, "r_buffer_size": "8Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "PART", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "r_loops": 1, "rows": 197777, "r_rows": 200000, "cost": 33.974347, "r_table_time_ms": 30.10973796, "r_other_time_ms": 23.47528928, "r_engine_stats": { "pages_accessed": 1971 }, "filtered": 4, "r_filtered": 5.3175, "attached_condition": "PART.P_NAME like '%slate%'" } }, { "table": { "table_name": "LINEITEM", "access_type": "ref", "possible_keys": [ "PRIMARY", "LINEITEM_PART_SUPP_FKIDX", "IDX_LINEITEM_ORDERKEY_FKIDX" ], "key": "LINEITEM_PART_SUPP_FKIDX", "key_length": "4", "used_key_parts": ["L_PARTKEY"], "ref": ["tpch.PART.P_PARTKEY"], "loops": 7911.08, "r_loops": 10635, "rows": 31, "r_rows": 29.98213446, "cost": 299.8570188, "r_table_time_ms": 788.2830736, "r_other_time_ms": 52.1178965, "r_engine_stats": { "pages_accessed": 989379 }, "filtered": 100, "r_filtered": 100 } }, { "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": 245243.48, "r_loops": 318860, "r_table_loops": 13869, "rows": 1, "r_rows": 1, "cost": 219.4874695, "r_table_time_ms": 18.90031199, "r_other_time_ms": 1.29032134, "r_engine_stats": { "pages_accessed": 28027 }, "filtered": 100, "r_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null" } }, { "table": { "table_name": "PARTSUPP", "access_type": "eq_ref", "possible_keys": [ "PRIMARY", "PARTSUPP_PART_FKIDX", "PARTSUPP_SUPPLIER_FKIDX" ], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["PS_PARTKEY", "PS_SUPPKEY"], "ref": ["tpch.PART.P_PARTKEY", "tpch.LINEITEM.L_SUPPKEY"], "loops": 245243.48, "r_loops": 318860, "r_table_loops": 13869, "rows": 1, "r_rows": 1, "cost": 226.3646535, "r_table_time_ms": 23.34284003, "r_other_time_ms": 0.895620332, "r_engine_stats": { "pages_accessed": 41899 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "ORDERS", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["O_ORDERKEY"], "ref": ["tpch.LINEITEM.L_ORDERKEY"], "loops": 245243.48, "r_loops": 318860, "r_table_loops": 318857, "rows": 1, "r_rows": 1, "cost": 229.5660871, "r_table_time_ms": 654.9710987, "r_other_time_ms": 232.8370956, "r_engine_stats": { "pages_accessed": 961934 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "NATION", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["tpch.SUPPLIER.S_NATIONKEY"], "loops": 245243.48, "r_loops": 318860, "r_table_loops": 13292, "rows": 1, "r_rows": 1, "cost": 219.3563975, "r_table_time_ms": 7.153107906, "r_other_time_ms": 11.57992164, "r_engine_stats": { "pages_accessed": 13292 }, "filtered": 100, "r_filtered": 100 } } ] } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (1.840 sec) MariaDB [tpch]> notee