MariaDB [m]> source Q7-analyze-json.sql +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.692831658 }, "query_block": { "select_id": 1, "cost": 12856.35759, "r_loops": 1, "r_total_time_ms": 1197.895215, "filesort": { "sort_key": "n1.N_NAME, n2.N_NAME, extract(year from LINEITEM.L_SHIPDATE)", "r_loops": 1, "r_total_time_ms": 0.022053468, "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": "ORDERS", "access_type": "index", "possible_keys": ["PRIMARY", "ORDERS_FK1"], "key": "ORDERS_FK1", "key_length": "4", "used_key_parts": ["O_CUSTKEY"], "loops": 1, "r_loops": 1, "rows": 1490801, "r_rows": 1500000, "cost": 219.9285246, "r_table_time_ms": 146.8825796, "r_other_time_ms": 259.8290352, "r_engine_stats": { "pages_accessed": 2085 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100, "using_index": true } }, { "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": 1490801, "r_loops": 1500000, "r_table_loops": 100000, "rows": 1, "r_rows": 1, "cost": 1334.929544, "r_table_time_ms": 87.58249182, "r_other_time_ms": 6.441861459, "r_engine_stats": { "pages_accessed": 302336 }, "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": 1490801, "r_loops": 1500000, "r_table_loops": 96069, "rows": 1, "r_rows": 1, "cost": 1333.432866, "r_table_time_ms": 37.37131734, "r_other_time_ms": 12.01160242, "r_engine_stats": { "pages_accessed": 96069 }, "filtered": 100, "r_total_filtered": 8.113133333, "attached_condition": "n2.N_NAME = 'INDIA' or n2.N_NAME = 'JAPAN'", "r_filtered": 8.113133333 } }, { "table": { "table_name": "LINEITEM", "access_type": "ref", "possible_keys": ["PRIMARY", "IDX_LINEITEM_ORDERKEY_FKIDX"], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["L_ORDERKEY"], "ref": ["m.ORDERS.O_ORDERKEY"], "loops": 1490801, "r_loops": 121697, "rows": 3, "r_rows": 3.998545568, "cost": 1967.341664, "r_table_time_ms": 399.5747261, "r_other_time_ms": 33.94309533, "r_engine_stats": { "pages_accessed": 370439 }, "filtered": 100, "r_total_filtered": 30.75228468, "attached_condition": "LINEITEM.L_SHIPDATE between DATE'1995-01-01' and DATE'1996-12-31'", "r_filtered": 30.75228468 } }, { "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": 4472403, "r_loops": 149644, "r_table_loops": 149631, "rows": 1, "r_rows": 1, "cost": 4000.428031, "r_table_time_ms": 124.7997357, "r_other_time_ms": 9.872028829, "r_engine_stats": { "pages_accessed": 302451 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "SUPPLIER.S_NATIONKEY is not null", "r_filtered": 100 } }, { "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": 4472403, "r_loops": 149644, "r_table_loops": 143561, "rows": 1, "r_rows": 1, "cost": 4000.296959, "r_table_time_ms": 54.43185561, "r_other_time_ms": 25.10592755, "r_engine_stats": { "pages_accessed": 143561 }, "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 (1.196 sec) MariaDB [m]> notee