MariaDB [m]> source Q7-analyze-json.sql +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.498855235 }, "query_block": { "select_id": 1, "cost": 1680.201861, "r_loops": 1, "r_total_time_ms": 3714.281104, "filesort": { "sort_key": "n1.N_NAME, n2.N_NAME, extract(year from LINEITEM.L_SHIPDATE)", "r_loops": 1, "r_total_time_ms": 0.010103856, "r_used_priority_queue": false, "r_output_rows": 4, "r_buffer_size": "1Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "LINEITEM", "access_type": "ALL", "possible_keys": ["PRIMARY", "IDX_LINEITEM_ORDERKEY_FKIDX"], "loops": 1, "r_loops": 1, "rows": 5082399, "r_rows": 6000281, "cost": 867.009613, "r_table_time_ms": 748.8038711, "r_other_time_ms": 266.4475825, "r_engine_stats": { "pages_accessed": 52829 }, "filtered": 100, "r_total_filtered": 30.41062577, "attached_condition": "LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'", "r_filtered": 30.41062577 } }, { "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.LINEITEM.L_SUPPKEY"], "loops": 5082399, "r_loops": 1824723, "r_table_loops": 1824544, "rows": 1, "r_rows": 1, "cost": 162.6384496, "r_table_time_ms": 1101.034115, "r_other_time_ms": 301.6022486, "r_engine_stats": { "pages_accessed": 3688466 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "ORDERS", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "ORDERS_FK1"], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["O_ORDERKEY"], "ref": ["m.LINEITEM.L_ORDERKEY"], "loops": 5082399, "r_loops": 1824723, "r_table_loops": 493943, "rows": 1, "r_rows": 1, "cost": 162.6384496, "r_table_time_ms": 361.6817398, "r_other_time_ms": 19.50717951, "r_engine_stats": { "pages_accessed": 1489883 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "CUSTOMER", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "CUSTOMER_NATION_FKIDX"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["C_CUSTKEY"], "ref": ["m.ORDERS.O_CUSTKEY"], "loops": 5082399, "r_loops": 1824723, "r_table_loops": 493933, "rows": 1, "r_rows": 1, "cost": 162.6384496, "r_table_time_ms": 620.5517229, "r_other_time_ms": 25.75029369, "r_engine_stats": { "pages_accessed": 1493363 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "CUSTOMER.C_NATIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "n2", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["m.CUSTOMER.C_NATIONKEY"], "loops": 5082399, "r_loops": 1824723, "r_table_loops": 474322, "rows": 1, "r_rows": 1, "cost": 162.6384496, "r_table_time_ms": 138.8559331, "r_other_time_ms": 60.83748125, "r_engine_stats": { "pages_accessed": 474322 }, "filtered": 100, "r_total_filtered": 8.200915975, "attached_condition": "n2.N_NAME = 'INDIA' or n2.N_NAME = 'JAPAN'", "r_filtered": 8.200915975 } }, { "table": { "table_name": "n1", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["m.SUPPLIER.S_NATIONKEY"], "loops": 5082399, "r_loops": 149644, "r_table_loops": 143486, "rows": 1, "r_rows": 1, "cost": 162.6384496, "r_table_time_ms": 42.42872878, "r_other_time_ms": 26.74454516, "r_engine_stats": { "pages_accessed": 143486 }, "filtered": 100, "r_total_filtered": 3.983454064, "attached_condition": "n1.N_NAME = 'JAPAN' and n2.N_NAME = 'INDIA' or n1.N_NAME = 'INDIA' and n2.N_NAME = 'JAPAN'", "r_filtered": 3.983454064 } } ] } } } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (3.706 sec) MariaDB [m]> notee