|
SET max_recursive_iterations = 100000;
|
drop table if exists t;
|
create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte;
|
select max(a) over( ) from t;
|
The output from the client, connected to the server running with all defaults.
It's reproducible with MTR just as well, but I wanted to rule out all differences in server configuration between MTR and regular server as well as between MariaDB's MTR and MySQL's MTR.
|
11.0.1 release
|
MariaDB [test]> select max(a) over( ) from t;
|
...
|
+----------------+
|
30000 rows in set (31.360 sec)
|
Test case for MySQL differs in the name of the variable only:
SET cte_max_recursion_depth = 100000;
|
drop table if exists t;
|
create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte;
|
select max(a) over( ) from t;
|
|
8.0.28 release
|
mysql> select max(a) over( ) from t;
|
...
|
+----------------+
|
30000 rows in set (0.04 sec)
|
So, it's 30 sec vs 0.04 sec on a release bintar.
Execution plans:
|
MySQL
|
mysql> explain format=json select max(a) over( ) from t \G
|
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"cost_info": {
|
"query_cost": "3059.35"
|
},
|
"windowing": {
|
"windows": [
|
{
|
"name": "<unnamed window>",
|
"frame_buffer": {
|
"using_temporary_table": true,
|
"optimized_frame_evaluation": true
|
},
|
"functions": [
|
"max"
|
]
|
}
|
],
|
"table": {
|
"table_name": "t",
|
"access_type": "ALL",
|
"rows_examined_per_scan": 30351,
|
"rows_produced_per_join": 30351,
|
"filtered": "100.00",
|
"cost_info": {
|
"read_cost": "24.25",
|
"eval_cost": "3035.10",
|
"prefix_cost": "3059.35",
|
"data_read_per_join": "474K"
|
},
|
"used_columns": [
|
"a"
|
]
|
}
|
}
|
}
|
}
|
1 row in set, 1 warning (0.00 sec)
|
|
mysql> show warnings;
|
+-------+------+----------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+----------------------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select max(`test`.`t`.`a`) OVER () AS `max(a) over( )` from `test`.`t` |
|
+-------+------+----------------------------------------------------------------------------------------+
|
|
MariaDB
|
EXPLAIN: {
|
"query_block": {
|
"select_id": 1,
|
"cost": 4.9214646,
|
"window_functions_computation": {
|
"sorts": [
|
{
|
"filesort": {
|
"sort_key": "a"
|
}
|
}
|
],
|
"temporary_table": {
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "t",
|
"access_type": "ALL",
|
"loops": 1,
|
"rows": 29623,
|
"cost": 4.9214646,
|
"filtered": 100
|
}
|
}
|
]
|
}
|
}
|
}
|
}
|
1 row in set (0.000 sec)
|
|