MariaDB [demo]> source Q8.sql; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.755993798 }, "query_block": { "select_id": 1, "cost": 16727.57074, "r_loops": 1, "r_total_time_ms": 1870.929429, "filesort": { "sort_key": "extract(year from ORDERS.O_ORDERDATE)", "r_loops": 1, "r_total_time_ms": 0.008938389, "r_used_priority_queue": false, "r_output_rows": 2, "r_buffer_size": "320", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "ORDERS", "access_type": "ALL", "possible_keys": ["PRIMARY", "ORDERS_FK1"], "loops": 1, "r_loops": 1, "rows": 1487077, "r_rows": 1500000, "cost": 242.4142052, "r_table_time_ms": 179.898426, "r_other_time_ms": 96.59149435, "r_engine_stats": { "pages_accessed": 12415 }, "filtered": 100, "r_total_filtered": 30.41466667, "attached_condition": "ORDERS.O_ORDERDATE between DATE'1995-01-01' and DATE'1996-12-31'", "r_filtered": 30.41466667 } }, { "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": ["demo.ORDERS.O_CUSTKEY"], "loops": 1487077, "r_loops": 456220, "r_table_loops": 456212, "rows": 1, "r_rows": 1, "cost": 1277.215648, "r_table_time_ms": 588.2601696, "r_other_time_ms": 26.16339417, "r_engine_stats": { "pages_accessed": 1379251 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "CUSTOMER.C_NATIONKEY is not null", "r_filtered": 100 } }, { "table": { "table_name": "n1", "access_type": "eq_ref", "possible_keys": ["PRIMARY", "NATION_REGIONKEY_FKIDX"], "key": "PRIMARY", "key_length": "4", "used_key_parts": ["N_NATIONKEY"], "ref": ["demo.CUSTOMER.C_NATIONKEY"], "loops": 1487077, "r_loops": 456220, "r_table_loops": 437991, "rows": 1, "r_rows": 1, "cost": 1277.214151, "r_table_time_ms": 159.9631954, "r_other_time_ms": 49.68111216, "r_engine_stats": { "pages_accessed": 437991 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "REGION", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 1487077, "r_loops": 60, "rows": 5, "r_rows": 5, "cost": 241.4678215, "r_table_time_ms": 0.230802214, "r_other_time_ms": 0.110403625, "r_engine_stats": { "pages_accessed": 60 }, "filtered": 20, "r_total_filtered": 20, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 20 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "attached_condition": "REGION.R_REGIONKEY = n1.N_REGIONKEY", "r_loops": 456220, "r_filtered": 20.2511946, "r_unpack_time_ms": 33.20335318, "r_other_time_ms": 21.6038127, "r_effective_rows": 1 } }, { "table": { "table_name": "LINEITEM", "access_type": "ref", "possible_keys": [ "PRIMARY", "LINEITEM_PART_SUPP_FKIDX", "IDX_LINEITEM_ORDERKEY_FKIDX" ], "key": "PRIMARY", "key_length": "8", "used_key_parts": ["L_ORDERKEY"], "ref": ["demo.ORDERS.O_ORDERKEY"], "loops": 1487077, "r_loops": 92390, "rows": 4, "r_rows": 4.00217556, "cost": 2108.726872, "r_table_time_ms": 183.6298549, "r_other_time_ms": 21.88067258, "r_engine_stats": { "pages_accessed": 281098 }, "filtered": 75, "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": ["demo.LINEITEM.L_PARTKEY"], "loops": 4461231, "r_loops": 369761, "r_table_loops": 369759, "rows": 1, "r_rows": 1, "cost": 3831.644107, "r_table_time_ms": 471.5921153, "r_other_time_ms": 21.96035191, "r_engine_stats": { "pages_accessed": 1116475 }, "filtered": 100, "r_total_filtered": 0.65934482, "attached_condition": "PART.P_TYPE = 'SMALL PLATED COPPER'", "r_filtered": 0.65934482 } }, { "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": ["demo.LINEITEM.L_SUPPKEY"], "loops": 4461231, "r_loops": 2438, "r_table_loops": 2436, "rows": 1, "r_rows": 1, "cost": 3831.642584, "r_table_time_ms": 4.27992525, "r_other_time_ms": 0.843625877, "r_engine_stats": { "pages_accessed": 4921 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "n2", "access_type": "ALL", "possible_keys": ["PRIMARY"], "loops": 4461231, "r_loops": 2, "rows": 25, "r_rows": 25, "cost": 3647.923417, "r_table_time_ms": 0.009902622, "r_other_time_ms": 0.00959214, "r_engine_stats": { "pages_accessed": 2 }, "filtered": 4, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "256Kb", "join_type": "BNL", "attached_condition": "n2.N_NATIONKEY = SUPPLIER.S_NATIONKEY", "r_loops": 2438, "r_filtered": 4, "r_unpack_time_ms": 8.874286005, "r_other_time_ms": 2.102119341, "r_effective_rows": 25 } } ] } } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (1.869 sec) MariaDB [demo]> notee