MariaDB [demo]> source Q8.sql +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.84836743 }, "query_block": { "select_id": 1, "cost": 21568.92037, "r_loops": 1, "r_total_time_ms": 1903.249372, "filesort": { "sort_key": "extract(year from ORDERS.O_ORDERDATE)", "r_loops": 1, "r_total_time_ms": 0.045190275, "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": 1492663, "r_rows": 1500000, "cost": 253.9682962, "r_table_time_ms": 172.9155631, "r_other_time_ms": 90.54136517, "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": 1492663, "r_loops": 456220, "r_table_loops": 456212, "rows": 1, "r_rows": 1, "cost": 1336.594991, "r_table_time_ms": 569.7011485, "r_other_time_ms": 26.02803613, "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": 1492663, "r_loops": 456220, "r_table_loops": 437991, "rows": 1, "r_rows": 1, "cost": 1335.098313, "r_table_time_ms": 155.0275109, "r_other_time_ms": 33.02588981, "r_engine_stats": { "pages_accessed": 437991 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "n1.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": ["demo.n1.N_REGIONKEY"], "loops": 1492663, "r_loops": 456220, "r_table_loops": 363798, "rows": 1, "r_rows": 1, "cost": 1335.098313, "r_table_time_ms": 110.9455436, "r_other_time_ms": 30.15960008, "r_engine_stats": { "pages_accessed": 363798 }, "filtered": 100, "r_total_filtered": 20.2511946, "attached_condition": "REGION.R_NAME = 'ASIA'", "r_filtered": 20.2511946 } }, { "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": 1492663, "r_loops": 92390, "rows": 4, "r_rows": 4.00217556, "cost": 2213.048775, "r_table_time_ms": 181.8840533, "r_other_time_ms": 26.01651831, "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": 4477989, "r_loops": 369761, "r_table_loops": 369759, "rows": 1, "r_rows": 1, "cost": 4006.947265, "r_table_time_ms": 478.7651481, "r_other_time_ms": 19.26261898, "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": 4477989, "r_loops": 2438, "r_table_loops": 2436, "rows": 1, "r_rows": 1, "cost": 4005.424372, "r_table_time_ms": 4.168301854, "r_other_time_ms": 0.284589241, "r_engine_stats": { "pages_accessed": 4921 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "SUPPLIER.S_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": ["demo.SUPPLIER.S_NATIONKEY"], "loops": 4477989, "r_loops": 2438, "r_table_loops": 2343, "rows": 1, "r_rows": 1, "cost": 4005.2933, "r_table_time_ms": 1.102376399, "r_other_time_ms": 3.326019196, "r_engine_stats": { "pages_accessed": 2343 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } } | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (1.903 sec) MariaDB [demo]> notee