MariaDB [m]> source Q2-analyze-json.sql; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 2.9399258 }, "query_block": { "select_id": 1, "cost": 399.2037031, "r_loops": 1, "r_total_time_ms": 60.84104151, "filesort": { "sort_key": "SUPPLIER.S_ACCTBAL desc, NATION.N_NAME, SUPPLIER.S_NAME, PART.P_PARTKEY", "r_loops": 1, "r_total_time_ms": 0.108728268, "r_limit": 100, "r_used_priority_queue": true, "r_output_rows": 101, "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": 198268, "r_rows": 200000, "cost": 34.05438, "r_table_time_ms": 36.33515934, "r_other_time_ms": 10.15923766, "r_engine_stats": { "pages_accessed": 1971 }, "filtered": 19, "r_total_filtered": 0.3865, "attached_condition": "PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL'", "r_filtered": 0.3865 } }, { "table": { "table_name": "PARTSUPP", "access_type": "ref", "possible_keys": [ "PRIMARY", "PARTSUPP_PART_FKIDX", "PARTSUPP_SUPPLIER_FKIDX" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["PS_PARTKEY"], "ref": ["m.PART.P_PARTKEY"], "loops": 37670.92, "r_loops": 773, "rows": 4, "r_rows": 4, "cost": 61.76639438, "r_table_time_ms": 2.715175107, "r_other_time_ms": 0.627430777, "r_engine_stats": { "pages_accessed": 2361 }, "filtered": 75, "r_total_filtered": 15.20051746, "attached_condition": "PARTSUPP.PS_SUPPLYCOST = (subquery#2)", "r_filtered": 15.20051746 } }, { "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": ["m.PARTSUPP.PS_SUPPKEY"], "loops": 113012.76, "r_loops": 470, "rows": 1, "r_rows": 1, "cost": 101.2150243, "r_table_time_ms": 0.49837365, "r_other_time_ms": 0.086528512, "r_engine_stats": { "pages_accessed": 949 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "NATION", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "NATION_REGIONKEY_FKIDX"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["m.SUPPLIER.S_NATIONKEY"], "loops": 113012.76, "r_loops": 470, "r_table_loops": 378, "rows": 1, "r_rows": 1, "cost": 101.0839523, "r_table_time_ms": 0.203311345, "r_other_time_ms": 0.209467569, "r_engine_stats": { "pages_accessed": 378 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "NATION.N_REGIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "REGION", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["R_REGIONKEY"], "ref": ["m.NATION.N_REGIONKEY"], "loops": 113012.76, "r_loops": 470, "r_table_loops": 3, "rows": 1, "r_rows": 1, "cost": 101.0839523, "r_table_time_ms": 0.002051628, "r_other_time_ms": 0.006523709, "r_engine_stats": { "pages_accessed": 3 }, "filtered": 100, "r_total_filtered": 99.78723404, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 99.78723404 } } ], "subqueries": [ { "subquery_cache": { "r_loops": 3092, "r_hit_ratio": 75, "query_block": { "select_id": 2, "cost": 0.01441873, "r_loops": 773, "r_total_time_ms": 9.558180853, "nested_loop": [ { "table": { "table_name": "PARTSUPP", "access_type": "ref", "possible_keys": [ "PRIMARY", "PARTSUPP_PART_FKIDX", "PARTSUPP_SUPPLIER_FKIDX" ], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["PS_PARTKEY"], "ref": ["m.PART.P_PARTKEY"], "loops": 1, "r_loops": 773, "rows": 4, "r_rows": 4, "cost": 0.00227277, "r_table_time_ms": 1.488255861, "r_other_time_ms": 0.574216291, "r_engine_stats": { "pages_accessed": 2361 }, "filtered": 75, "r_total_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": ["m.PARTSUPP.PS_SUPPKEY"], "loops": 3, "r_loops": 3092, "rows": 1, "r_rows": 1, "cost": 0.00514092, "r_table_time_ms": 3.485432682, "r_other_time_ms": 0.381015825, "r_engine_stats": { "pages_accessed": 6250 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "NATION", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "NATION_REGIONKEY_FKIDX"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["m.SUPPLIER.S_NATIONKEY"], "loops": 3, "r_loops": 3092, "r_table_loops": 3001, "rows": 1, "r_rows": 1, "cost": 0.00350252, "r_table_time_ms": 1.435624399, "r_other_time_ms": 0.428657781, "r_engine_stats": { "pages_accessed": 3001 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "NATION.N_REGIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "REGION", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["R_REGIONKEY"], "ref": ["m.NATION.N_REGIONKEY"], "loops": 3, "r_loops": 3092, "r_table_loops": 2630, "rows": 1, "r_rows": 1, "cost": 0.00350252, "r_table_time_ms": 1.097035444, "r_other_time_ms": 0.39923549, "r_engine_stats": { "pages_accessed": 2630 }, "filtered": 100, "r_total_filtered": 21.02199224, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 21.02199224 } } ] } } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.065 sec) MariaDB [m]> notee