MariaDB [m8]> analyze format=json select * from s1 join s2 on(1); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.129160914 }, "query_block": { "select_id": 1, "cost": 186.3112688, "r_loops": 1, "r_total_time_ms": 189.4777384, "nested_loop": [ { "table": { "table_name": "s1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 1000, "r_rows": 1000, "cost": 0.1764192, "r_table_time_ms": 0.272653457, "r_other_time_ms": 0.118454075, "r_engine_stats": { "pages_accessed": 4 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } }, { "block-nl-join": { "table": { "table_name": "s2", "access_type": "ALL", "loops": 1000, "r_loops": 1, "rows": 2000, "r_rows": 2000, "cost": 186.1348496, "r_table_time_ms": 0.336981606, "r_other_time_ms": 0.059067544, "r_engine_stats": { "pages_accessed": 7 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 }, "buffer_type": "flat", "buffer_size": "214KiB", "join_type": "BNL", "r_loops": 1000, "r_filtered": 100, "r_unpack_time_ms": 83.67562578, "r_other_time_ms": 105.0027352, "r_effective_rows": 2000 } } ] } } | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.190 sec) MariaDB [m8]> notee MariaDB [rqg]> analyze ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 WHERE true GROUP BY t1.col_int_key ) ; +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 30169 | 30000.00 | 100.00 | 12.22 | Using where; Using temporary; Using filesort | | 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 20.00 | 100.00 | 100.00 | Using temporary; Using filesort | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | 250.00 | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ 3 rows in set (0.012 sec) MariaDB [rqg]> notee MariaDB [rqg]> analyze ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 WHERE true GROUP BY t1.col_int_key ) ; +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 30169 | 30000.00 | 100.00 | 12.22 | Using where; Using temporary; Using filesort | | 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 20.00 | 100.00 | 100.00 | Using temporary; Using filesort | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | 250.00 | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ 3 rows in set (0.011 sec) MariaDB [rqg]> notee MariaDB [rqg]> analyze ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 WHERE true GROUP BY t1.col_int_key ) ; +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 30169 | 30000.00 | 100.00 | 12.22 | Using where; Using temporary; Using filesort | | 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 20.00 | 100.00 | 100.00 | Using temporary; Using filesort | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | 250.00 | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ 3 rows in set (0.011 sec) MariaDB [rqg]> notee MariaDB [rqg]> analyze ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 GROUP BY t1.col_int_key ) ; +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 30169 | 30000.00 | 100.00 | 12.22 | Using where; Using temporary; Using filesort | | 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 20.00 | 100.00 | 100.00 | Using temporary; Using filesort | | NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | 250.00 | NULL | NULL | | +------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------+------------+----------------------------------------------+ 3 rows in set (0.010 sec) MariaDB [rqg]> notee MariaDB [rqg]> analyze format=json ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 GROUP BY t1.col_int_key ) ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.442660985 }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 250, "query_specifications": [ { "query_block": { "select_id": 1, "cost": 25.1551693, "r_loops": 1, "r_total_time_ms": 10.00308016, "filesort": { "sort_key": "t1.col_int_key", "r_loops": 1, "r_total_time_ms": 0.067207144, "r_used_priority_queue": false, "r_output_rows": 238, "r_buffer_size": "5KiB", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 30169, "r_rows": 30000, "cost": 5.0104626, "r_table_time_ms": 6.969449898, "r_other_time_ms": 2.839146596, "r_engine_stats": { "pages_accessed": 95 }, "filtered": 100, "r_total_filtered": 12.22333333, "attached_condition": "t1.col_int <= 1", "r_filtered": 12.22333333 } } ] } } } }, { "query_block": { "select_id": 2, "operation": "UNION", "cost": 0.026902029, "r_loops": 1, "r_total_time_ms": 0.040529734, "filesort": { "sort_key": "t1.col_int_key", "r_loops": 1, "r_total_time_ms": 0.003391764, "r_used_priority_queue": false, "r_output_rows": 12, "r_buffer_size": "464", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 20, "r_rows": 20, "cost": 0.0141148, "r_table_time_ms": 0.011179762, "r_other_time_ms": 0.011514586, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } } ] } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.011 sec) MariaDB [rqg]> notee MariaDB [rqg]> analyze format=json ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM G t1 WHERE NOT t1.col_int > 1 GROUP BY t1.col_int_key ) UNION ( SELECT t1.col_int_key, COUNT(*), SUM( t1.col_int ) FROM C t1 GROUP BY t1.col_int_key ) ; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.263301308 }, "query_block": { "union_result": { "table_name": "", "access_type": "ALL", "r_loops": 1, "r_rows": 250, "query_specifications": [ { "query_block": { "select_id": 1, "cost": 25.1551693, "r_loops": 1, "r_total_time_ms": 9.333734514, "filesort": { "sort_key": "t1.col_int_key", "r_loops": 1, "r_total_time_ms": 0.059314011, "r_used_priority_queue": false, "r_output_rows": 238, "r_buffer_size": "5KiB", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 30169, "r_rows": 30000, "cost": 5.0104626, "r_table_time_ms": 6.499858042, "r_other_time_ms": 2.655197156, "r_engine_stats": { "pages_accessed": 95 }, "filtered": 100, "r_total_filtered": 12.22333333, "attached_condition": "t1.col_int <= 1", "r_filtered": 12.22333333 } } ] } } } }, { "query_block": { "select_id": 2, "operation": "UNION", "cost": 0.026902029, "r_loops": 1, "r_total_time_ms": 0.035451799, "filesort": { "sort_key": "t1.col_int_key", "r_loops": 1, "r_total_time_ms": 0.003387746, "r_used_priority_queue": false, "r_output_rows": 12, "r_buffer_size": "464", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "t1", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 20, "r_rows": 20, "cost": 0.0141148, "r_table_time_ms": 0.011210566, "r_other_time_ms": 0.010842929, "r_engine_stats": { "pages_accessed": 1 }, "filtered": 100, "r_total_filtered": 100, "r_filtered": 100 } } ] } } } } ] } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.010 sec) MariaDB [rqg]> notee MariaDB [m8]> analyze format=json select * from t2 where a<1000; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ANALYZE | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_optimization": { "r_total_time_ms": 0.200655395 }, "query_block": { "select_id": 1, "cost": 0.148975355, "r_loops": 1, "r_total_time_ms": 0.460343377, "nested_loop": [ { "table": { "table_name": "t2", "access_type": "range", "possible_keys": ["i1"], "key": "i1", "key_length": "5", "used_key_parts": ["a"], "loops": 1, "r_loops": 1, "rows": 999, "r_rows": 999, "cost": 0.148975355, "r_table_time_ms": 0.302892139, "r_other_time_ms": 0.143962778, "r_engine_stats": { "pages_accessed": 8 }, "filtered": 100, "r_total_filtered": 100, "attached_condition": "t2.a < 1000", "r_filtered": 100, "using_index": true } } ] } } | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [m8]> notee