MariaDB [m]> source Q2-analyze-json.sql +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.488095427 }, "query_block": { "select_id": 1, "cost": 1495.963562, "r_loops": 1, "r_total_time_ms": 477.7573406, "filesort": { "sort_key": "SUPPLIER.S_ACCTBAL desc, NATION.N_NAME, SUPPLIER.S_NAME, PART.P_PARTKEY", "r_loops": 1, "r_total_time_ms": 0.107322585, "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": "NATION", "access_type": "ALL", "possible_keys": ["PRIMARY", "NATION_REGIONKEY_FKIDX"], "loops": 1, "r_loops": 1, "rows": 25, "r_rows": 25, "cost": 0.0149298, "r_table_time_ms": 0.033586461, "r_other_time_ms": 0.021400525, "r_engine_stats": { "pages_accessed": 1 }, "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": 25, "r_loops": 25, "r_table_loops": 16, "rows": 1, "r_rows": 1, "cost": 0.0231802, "r_table_time_ms": 0.021003587, "r_other_time_ms": 0.008593411, "r_engine_stats": { "pages_accessed": 16 }, "filtered": 100, "r_total_filtered": 20, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 20 } }, { "table": { "table_name": "SUPPLIER", "access_type": "ref", "possible_keys": ["PRIMARY", "SUPPLIER_NATION_FKIDX"], "key": "SUPPLIER_NATION_FKIDX", "key_length": "5", "used_key_parts": ["S_NATIONKEY"], "ref": ["m.NATION.N_NATIONKEY"], "loops": 25, "r_loops": 5, "rows": 389, "r_rows": 409.6, "cost": 9.8203168, "r_table_time_ms": 2.414844836, "r_other_time_ms": 0.141580426, "r_engine_stats": { "pages_accessed": 4110 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "PARTSUPP", "access_type": "ref", "possible_keys": [ "PRIMARY", "PARTSUPP_PART_FKIDX", "PARTSUPP_SUPPLIER_FKIDX" ], "key": "PARTSUPP_SUPPLIER_FKIDX", "key_length": "4", "used_key_parts": ["PS_SUPPKEY"], "ref": ["m.SUPPLIER.S_SUPPKEY"], "loops": 9725, "r_loops": 2048, "rows": 80, "r_rows": 80, "cost": 788.5760312, "r_table_time_ms": 246.1800577, "r_other_time_ms": 10.73377191, "r_engine_stats": { "pages_accessed": 497908 }, "filtered": 100, "r_total_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": ["m.PARTSUPP.PS_PARTKEY"], "loops": 778000, "r_loops": 163840, "rows": 1, "r_rows": 1, "cost": 697.529104, "r_table_time_ms": 198.0713184, "r_other_time_ms": 10.72983666, "r_engine_stats": { "pages_accessed": 494802 }, "filtered": 100, "r_total_filtered": 0.286254883, "attached_condition": "PART.P_SIZE = 30 and PART.P_TYPE like '%STEEL' and PARTSUPP.PS_SUPPLYCOST = (subquery#2)", "r_filtered": 0.286254883 } } ], "subqueries": [ { "subquery_cache": { "state": "disabled", "r_loops": 221, "r_hit_ratio": 9.502262443, "query_block": { "select_id": 2, "cost": 0.01441873, "r_loops": 629, "r_total_time_ms": 8.763767613, "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": 629, "rows": 4, "r_rows": 4, "cost": 0.00227277, "r_table_time_ms": 1.344578958, "r_other_time_ms": 0.559533594, "r_engine_stats": { "pages_accessed": 1916 }, "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": 2516, "rows": 1, "r_rows": 1, "cost": 0.00514092, "r_table_time_ms": 3.161832997, "r_other_time_ms": 0.378059205, "r_engine_stats": { "pages_accessed": 5089 }, "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": 2516, "r_table_loops": 2441, "rows": 1, "r_rows": 1, "cost": 0.00350252, "r_table_time_ms": 1.212884502, "r_other_time_ms": 0.406886423, "r_engine_stats": { "pages_accessed": 2441 }, "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": 2516, "r_table_loops": 2150, "rows": 1, "r_rows": 1, "cost": 0.00350252, "r_table_time_ms": 0.907530587, "r_other_time_ms": 0.375857637, "r_engine_stats": { "pages_accessed": 2150 }, "filtered": 100, "r_total_filtered": 40.38155803, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 40.38155803 } } ] } } } ] } } } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.478 sec) MariaDB [m]> notee