MariaDB [tpch]> source Q9-json.sql +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.855975148 }, "query_block": { "select_id": 1, "cost": 18268.80338, "r_loops": 1, "r_total_time_ms": 12580.56745, "filesort": { "sort_key": "NATION.N_NAME, extract(year from ORDERS.O_ORDERDATE) desc", "r_loops": 1, "r_total_time_ms": 0.049055872, "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": "ORDERS", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1, "r_loops": 1, "rows": 1493904, "r_rows": 1500000, "cost": 254.1705792, "r_table_time_ms": 173.7302746, "r_other_time_ms": 72.46473681, "r_engine_stats": { "pages_accessed": 12415 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "LINEITEM", "access_type": "ref", "possible_keys": [ "PRIMARY", "LINEITEM_PART_SUPP_FKIDX", "IDX_LINEITEM_ORDERKEY_FKIDX" ], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["L_ORDERKEY"], "ref": ["tpch.ORDERS.O_ORDERKEY"], "loops": 1493904, "r_loops": 1500000, "rows": 3, "r_rows": 4.000187333, "cost": 1971.346303, "r_table_time_ms": 2377.879456, "r_other_time_ms": 320.1719853, "r_engine_stats": { "pages_accessed": 4565966 }, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "PART", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["P_PARTKEY"], "ref": ["tpch.LINEITEM.L_PARTKEY"], "loops": 4481712, "r_loops": 6000281, "r_table_loops": 6000261, "rows": 1, "r_rows": 1, "cost": 4010.277265, "r_table_time_ms": 7491.650094, "r_other_time_ms": 604.1597833, "r_engine_stats": { "pages_accessed": 18118191 }, "filtered": 100, "r_filtered": 5.314084457, "attached_condition": "PART.P_NAME like '%slate%'" } }, { "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.LINEITEM.L_PARTKEY", "tpch.LINEITEM.L_SUPPKEY"], "loops": 4481712, "r_loops": 318860, "r_table_loops": 318824, "rows": 1, "r_rows": 1, "cost": 4015.631556, "r_table_time_ms": 803.0147677, "r_other_time_ms": 14.95513848, "r_engine_stats": { "pages_accessed": 963116 }, "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": 4481712, "r_loops": 318860, "r_table_loops": 318798, "rows": 1, "r_rows": 1, "cost": 4008.754372, "r_table_time_ms": 351.1098146, "r_other_time_ms": 28.07483896, "r_engine_stats": { "pages_accessed": 644106 }, "filtered": 100, "r_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null" } }, { "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": 4481712, "r_loops": 318860, "r_table_loops": 306016, "rows": 1, "r_rows": 1, "cost": 4008.6233, "r_table_time_ms": 126.7909294, "r_other_time_ms": 216.4671688, "r_engine_stats": { "pages_accessed": 306016 }, "filtered": 100, "r_filtered": 100 } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (12.563 sec) MariaDB [tpch]> notee