mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
75 lines
2.4 KiB
Text
75 lines
2.4 KiB
Text
set max_recursive_iterations=200000;
|
|
create table test_table (id int, random_data varchar(36), static_int int, static_varchar varchar(10));
|
|
insert into test_table(id, random_data, static_int, static_varchar)
|
|
select id, random_data, 42, 'Hello'
|
|
from (
|
|
with recursive data_generator(id, random_data) as (
|
|
select 1 as id, uuid() as random_data
|
|
union all
|
|
select id + 1, uuid() from data_generator where id < 1000
|
|
)
|
|
select * from data_generator
|
|
) as a;
|
|
commit;
|
|
analyze table test_table;
|
|
Table Op Msg_type Msg_text
|
|
test.test_table analyze status Engine-independent statistics collected
|
|
test.test_table analyze status OK
|
|
explain select * from (select id, lead(id) over(order by id) next_id from test_table order by id) a limit 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1000
|
|
2 DERIVED test_table ALL NULL NULL NULL NULL 1000 Using temporary; Using filesort
|
|
select * from (select id, lead(id) over(order by id) next_id from test_table order by id) a limit 10;
|
|
id next_id
|
|
1 2
|
|
2 3
|
|
3 4
|
|
4 5
|
|
5 6
|
|
6 7
|
|
7 8
|
|
8 9
|
|
9 10
|
|
10 11
|
|
drop table if exists test_table;
|
|
create table test_table (id int, random_data varchar(36), static_int int, static_varchar varchar(10));
|
|
insert into test_table(id, random_data, static_int, static_varchar)
|
|
select id, random_data, 42, 'Hello'
|
|
from (
|
|
with recursive data_generator(id, random_data) as (
|
|
select 1 as id, uuid() as random_data
|
|
union all
|
|
select id + 1, uuid() from data_generator where id < 100000
|
|
)
|
|
select * from data_generator
|
|
) as a;
|
|
commit;
|
|
analyze table test_table;
|
|
Table Op Msg_type Msg_text
|
|
test.test_table analyze status Engine-independent statistics collected
|
|
test.test_table analyze status OK
|
|
explain select * from (select id, lead(id) over(order by id) next_id from test_table order by id) a limit 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 100000
|
|
2 DERIVED test_table ALL NULL NULL NULL NULL 100000 Using temporary; Using filesort
|
|
flush status;
|
|
select * from (select id, lead(id) over(order by id) next_id from test_table order by id) a limit 10;
|
|
id next_id
|
|
1 2
|
|
2 3
|
|
3 4
|
|
4 5
|
|
5 6
|
|
6 7
|
|
7 8
|
|
8 9
|
|
9 10
|
|
10 11
|
|
select variable_name,
|
|
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as exp
|
|
from information_schema.session_status
|
|
where variable_name like 'Sort_merge_passes';
|
|
variable_name exp
|
|
SORT_MERGE_PASSES WITH PASSES
|
|
drop table test_table;
|
|
set max_recursive_iterations=default;
|