Skip to content

Instantly share code, notes, and snippets.

@idoqo

idoqo/st.result

Created August 20, 2021 05:07
Show Gist options
  • Save idoqo/21148eaa3236ab7a024b9c16c1428c47 to your computer and use it in GitHub Desktop.
Save idoqo/21148eaa3236ab7a024b9c16c1428c47 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
st.result
create table users (
city varchar(100)
);
insert into users select 'Moscow' from seq_1_to_99;
insert into users select 'Helsinki' from seq_1_to_2;
analyze table users persistent for all;
Table Op Msg_type Msg_text
test.users analyze status Engine-independent statistics collected
test.users analyze status OK
select decode_histogram(hist_type, histogram) from mysql.column_stats where table_name='users';
decode_histogram(hist_type, histogram)
0.00000,0.00000,1.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000,0.00000
explain extended select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 99.22 Using where
Warnings:
Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` < 'Moscow'
analyze select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 99.22 1.98 Using where
delete from mysql.column_stats where table_name='users';
set histogram_type=json;
analyze table users persistent for all;
Table Op Msg_type Msg_text
test.users analyze status Engine-independent statistics collected
test.users analyze status Table is already up to date
select histogram from mysql.column_stats where table_name='users';
histogram
[
"Helsinki",
"Helsinki",
"Helsinki",
"Helsinki",
"Helsinki",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow"
]
explain extended select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 100.00 Using where
Warnings:
Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` < 'Moscow'
analyze select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 100.00 1.98 Using where
delete from mysql.column_stats where table_name='users';
set histogram_size=100;
analyze table users persistent for all;
Table Op Msg_type Msg_text
test.users analyze status Engine-independent statistics collected
test.users analyze status Table is already up to date
select histogram from mysql.column_stats where table_name='users';
histogram
[
"Helsinki",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow"
]
explain extended select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 100.00 Using where
Warnings:
Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` < 'Moscow'
analyze select * from users where city < 'Moscow';
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 100.00 1.98 Using where
delete from mysql.column_stats where table_name='users';
set histogram_size=50;
analyze table users persistent for all;
Table Op Msg_type Msg_text
test.users analyze status Engine-independent statistics collected
test.users analyze status Table is already up to date
select histogram from mysql.column_stats where table_name='users';
histogram
[
"Helsinki",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow"
]
explain extended select * from users where city <= 'Moscow';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 100.00 Using where
Warnings:
Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` <= 'Moscow'
analyze select * from users where city <= 'Moscow';
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 100.00 100.00 Using where
delete from mysql.column_stats where table_name='users';
set histogram_size=49;
analyze table users persistent for all;
Table Op Msg_type Msg_text
test.users analyze status Engine-independent statistics collected
test.users analyze status Table is already up to date
select histogram from mysql.column_stats where table_name='users';
histogram
[
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow",
"Moscow"
]
explain extended select * from users where city <= 'Moscow';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 100.00 Using where
Warnings:
Note 1003 select `test`.`users`.`city` AS `city` from `test`.`users` where `test`.`users`.`city` <= 'Moscow'
analyze select * from users where city <= 'Moscow';
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 SIMPLE users ALL NULL NULL NULL NULL 101 101.00 100.00 100.00 Using where
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment