mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01:00
ec277a70e8
The intentention was always to not create histograms for single value unique keys (as histograms is not useful in this case), but because of a bug in the code this was still done. The changes in the test cases was mainly because hist_size is now NULL for these kind of columns.
1045 lines
44 KiB
Text
1045 lines
44 KiB
Text
SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB';
|
|
set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch;
|
|
set optimizer_switch='extended_keys=on';
|
|
set @innodb_stats_persistent_save= @@innodb_stats_persistent;
|
|
set @innodb_stats_persistent_sample_pages_save=
|
|
@@innodb_stats_persistent_sample_pages;
|
|
set global innodb_stats_persistent= 1;
|
|
set global innodb_stats_persistent_sample_pages=100;
|
|
select @@global.use_stat_tables;
|
|
@@global.use_stat_tables
|
|
COMPLEMENTARY
|
|
select @@session.use_stat_tables;
|
|
@@session.use_stat_tables
|
|
COMPLEMENTARY
|
|
set @save_use_stat_tables=@@use_stat_tables;
|
|
set @save_histogram_size=@@global.histogram_size;
|
|
set @@global.histogram_size=0,@@local.histogram_size=0;
|
|
set optimizer_use_condition_selectivity=4;
|
|
set use_stat_tables='preferably';
|
|
set @save_histogram_type=@@histogram_type;
|
|
set histogram_type='single_prec_hb';
|
|
DROP DATABASE IF EXISTS dbt3_s001;
|
|
CREATE DATABASE dbt3_s001;
|
|
use dbt3_s001;
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='extended_keys=off';
|
|
select * from mysql.table_stats;
|
|
db_name table_name cardinality
|
|
dbt3_s001 customer 150
|
|
dbt3_s001 lineitem 6005
|
|
dbt3_s001 nation 25
|
|
dbt3_s001 orders 1500
|
|
dbt3_s001 part 200
|
|
dbt3_s001 partsupp 700
|
|
dbt3_s001 region 5
|
|
dbt3_s001 supplier 10
|
|
select * from mysql.index_stats;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
dbt3_s001 customer PRIMARY 1 1.0000
|
|
dbt3_s001 customer i_c_nationkey 1 6.0000
|
|
dbt3_s001 lineitem PRIMARY 1 4.0033
|
|
dbt3_s001 lineitem PRIMARY 2 1.0000
|
|
dbt3_s001 lineitem i_l_shipdate 1 2.6500
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
|
|
dbt3_s001 lineitem i_l_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_suppkey 1 600.5000
|
|
dbt3_s001 lineitem i_l_receiptdate 1 2.6477
|
|
dbt3_s001 lineitem i_l_orderkey 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
|
|
dbt3_s001 lineitem i_l_commitdate 1 2.7160
|
|
dbt3_s001 nation PRIMARY 1 1.0000
|
|
dbt3_s001 nation i_n_regionkey 1 5.0000
|
|
dbt3_s001 orders PRIMARY 1 1.0000
|
|
dbt3_s001 orders i_o_orderdate 1 1.3321
|
|
dbt3_s001 orders i_o_custkey 1 15.0000
|
|
dbt3_s001 part PRIMARY 1 1.0000
|
|
dbt3_s001 part i_p_retailprice 1 1.0000
|
|
dbt3_s001 partsupp PRIMARY 1 3.5000
|
|
dbt3_s001 partsupp PRIMARY 2 1.0000
|
|
dbt3_s001 partsupp i_ps_partkey 1 3.5000
|
|
dbt3_s001 partsupp i_ps_suppkey 1 70.0000
|
|
dbt3_s001 region PRIMARY 1 1.0000
|
|
dbt3_s001 supplier PRIMARY 1 1.0000
|
|
dbt3_s001 supplier i_s_nationkey 1 1.1111
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='index_condition_pushdown=off';
|
|
EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
|
|
from customer, orders, lineitem, supplier, nation, region
|
|
where c_custkey = o_custkey and l_orderkey = o_orderkey
|
|
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
|
|
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
|
|
and o_orderdate < date '1995-01-01' + interval '1' year
|
|
group by n_name
|
|
order by revenue desc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5
|
|
1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
|
1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index
|
|
1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using rowid filter
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 9 dbt3_s001.supplier.s_suppkey,dbt3_s001.orders.o_orderkey 1
|
|
select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
|
|
from customer, orders, lineitem, supplier, nation, region
|
|
where c_custkey = o_custkey and l_orderkey = o_orderkey
|
|
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
|
|
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
|
|
and o_orderdate < date '1995-01-01' + interval '1' year
|
|
group by n_name
|
|
order by revenue desc;
|
|
n_name revenue
|
|
PERU 321915.87150000007
|
|
ARGENTINA 69817.1451
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
delete from mysql.index_stats;
|
|
select * from mysql.table_stats;
|
|
db_name table_name cardinality
|
|
dbt3_s001 customer 150
|
|
dbt3_s001 lineitem 6005
|
|
dbt3_s001 nation 25
|
|
dbt3_s001 orders 1500
|
|
dbt3_s001 part 200
|
|
dbt3_s001 partsupp 700
|
|
dbt3_s001 region 5
|
|
dbt3_s001 supplier 10
|
|
select * from mysql.index_stats;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
dbt3_s001 customer PRIMARY 1 1.0000
|
|
dbt3_s001 customer i_c_nationkey 1 6.0000
|
|
dbt3_s001 customer i_c_nationkey 2 1.0000
|
|
dbt3_s001 lineitem PRIMARY 1 4.0033
|
|
dbt3_s001 lineitem PRIMARY 2 1.0000
|
|
dbt3_s001 lineitem i_l_shipdate 1 2.6500
|
|
dbt3_s001 lineitem i_l_shipdate 2 1.0149
|
|
dbt3_s001 lineitem i_l_shipdate 3 1.0000
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 3 1.0030
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 4 1.0000
|
|
dbt3_s001 lineitem i_l_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_partkey 2 1.0089
|
|
dbt3_s001 lineitem i_l_partkey 3 1.0000
|
|
dbt3_s001 lineitem i_l_suppkey 1 600.5000
|
|
dbt3_s001 lineitem i_l_suppkey 2 1.2073
|
|
dbt3_s001 lineitem i_l_suppkey 3 1.0000
|
|
dbt3_s001 lineitem i_l_receiptdate 1 2.6477
|
|
dbt3_s001 lineitem i_l_receiptdate 2 1.0152
|
|
dbt3_s001 lineitem i_l_receiptdate 3 1.0000
|
|
dbt3_s001 lineitem i_l_orderkey 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey 2 1.0000
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 3 1.0000
|
|
dbt3_s001 lineitem i_l_commitdate 1 2.7160
|
|
dbt3_s001 lineitem i_l_commitdate 2 1.0364
|
|
dbt3_s001 lineitem i_l_commitdate 3 1.0000
|
|
dbt3_s001 nation PRIMARY 1 1.0000
|
|
dbt3_s001 nation i_n_regionkey 1 5.0000
|
|
dbt3_s001 nation i_n_regionkey 2 1.0000
|
|
dbt3_s001 orders PRIMARY 1 1.0000
|
|
dbt3_s001 orders i_o_orderdate 1 1.3321
|
|
dbt3_s001 orders i_o_orderdate 2 1.0000
|
|
dbt3_s001 orders i_o_custkey 1 15.0000
|
|
dbt3_s001 orders i_o_custkey 2 1.0000
|
|
dbt3_s001 part PRIMARY 1 1.0000
|
|
dbt3_s001 part i_p_retailprice 1 1.0000
|
|
dbt3_s001 part i_p_retailprice 2 1.0000
|
|
dbt3_s001 partsupp PRIMARY 1 3.5000
|
|
dbt3_s001 partsupp PRIMARY 2 1.0000
|
|
dbt3_s001 partsupp i_ps_partkey 1 3.5000
|
|
dbt3_s001 partsupp i_ps_partkey 2 1.0000
|
|
dbt3_s001 partsupp i_ps_suppkey 1 70.0000
|
|
dbt3_s001 partsupp i_ps_suppkey 2 1.0000
|
|
dbt3_s001 region PRIMARY 1 1.0000
|
|
dbt3_s001 supplier PRIMARY 1 1.0000
|
|
dbt3_s001 supplier i_s_nationkey 1 1.1111
|
|
dbt3_s001 supplier i_s_nationkey 2 1.0000
|
|
select * from mysql.table_stats where table_name='orders';
|
|
db_name table_name cardinality
|
|
dbt3_s001 orders 1500
|
|
select * from mysql.index_stats where table_name='orders';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
dbt3_s001 orders PRIMARY 1 1.0000
|
|
dbt3_s001 orders i_o_orderdate 1 1.3321
|
|
dbt3_s001 orders i_o_orderdate 2 1.0000
|
|
dbt3_s001 orders i_o_custkey 1 15.0000
|
|
dbt3_s001 orders i_o_custkey 2 1.0000
|
|
select (select cardinality from mysql.table_stats where table_name='orders') /
|
|
(select avg_frequency from mysql.index_stats
|
|
where index_name='i_o_orderdate' and prefix_arity=1) as n_distinct;
|
|
n_distinct
|
|
1126.0416
|
|
select count(distinct o_orderdate) from orders;
|
|
count(distinct o_orderdate)
|
|
1126
|
|
select (select cardinality from mysql.table_stats where table_name='orders') /
|
|
(select avg_frequency from mysql.index_stats
|
|
where index_name='i_o_custkey' and prefix_arity=1) as n_distinct;
|
|
n_distinct
|
|
100.0000
|
|
select count(distinct o_custkey) from orders;
|
|
count(distinct o_custkey)
|
|
100
|
|
show index from orders;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE NO
|
|
orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE NO
|
|
orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE NO
|
|
select index_name, column_name, cardinality from information_schema.statistics
|
|
where table_name='orders';
|
|
index_name column_name cardinality
|
|
PRIMARY o_orderkey 1500
|
|
i_o_orderdate o_orderDATE 1126
|
|
i_o_custkey o_custkey 100
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='index_condition_pushdown=off';
|
|
EXPLAIN select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
|
|
from customer, orders, lineitem, supplier, nation, region
|
|
where c_custkey = o_custkey and l_orderkey = o_orderkey
|
|
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
|
|
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
|
|
and o_orderdate < date '1995-01-01' + interval '1' year
|
|
group by n_name
|
|
order by revenue desc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5
|
|
1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index
|
|
1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index
|
|
1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using rowid filter
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey 9 dbt3_s001.supplier.s_suppkey,dbt3_s001.orders.o_orderkey 1
|
|
select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue
|
|
from customer, orders, lineitem, supplier, nation, region
|
|
where c_custkey = o_custkey and l_orderkey = o_orderkey
|
|
and l_suppkey = s_suppkey and c_nationkey = s_nationkey
|
|
and s_nationkey = n_nationkey and n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01'
|
|
and o_orderdate < date '1995-01-01' + interval '1' year
|
|
group by n_name
|
|
order by revenue desc;
|
|
n_name revenue
|
|
PERU 321915.87150000007
|
|
ARGENTINA 69817.1451
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
EXPLAIN select o_year,
|
|
sum(case when nation = 'UNITED STATES' then volume else 0 end) /
|
|
sum(volume) as mkt_share
|
|
from (select extract(year from o_orderdate) as o_year,
|
|
l_extendedprice * (1-l_discount) as volume,
|
|
n2.n_name as nation
|
|
from part, supplier, lineitem, orders, customer,
|
|
nation n1, nation n2, region
|
|
where p_partkey = l_partkey and s_suppkey = l_suppkey
|
|
and l_orderkey = o_orderkey and o_custkey = c_custkey
|
|
and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
|
|
and o_orderdate between date '1995-01-01' and date '1996-12-31'
|
|
and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
|
|
group by o_year
|
|
order by o_year;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using temporary; Using filesort
|
|
1 SIMPLE part ALL PRIMARY NULL NULL NULL 200 Using where; Using join buffer (flat, BNL join)
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 5 dbt3_s001.part.p_partkey 30 Using where
|
|
1 SIMPLE supplier eq_ref PRIMARY,i_s_nationkey PRIMARY 4 dbt3_s001.lineitem.l_suppkey 1 Using where
|
|
1 SIMPLE n2 eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
|
|
1 SIMPLE orders eq_ref PRIMARY,i_o_orderdate,i_o_custkey PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where
|
|
1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where
|
|
1 SIMPLE n1 eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 Using where
|
|
select o_year,
|
|
sum(case when nation = 'UNITED STATES' then volume else 0 end) /
|
|
sum(volume) as mkt_share
|
|
from (select extract(year from o_orderdate) as o_year,
|
|
l_extendedprice * (1-l_discount) as volume,
|
|
n2.n_name as nation
|
|
from part, supplier, lineitem, orders, customer,
|
|
nation n1, nation n2, region
|
|
where p_partkey = l_partkey and s_suppkey = l_suppkey
|
|
and l_orderkey = o_orderkey and o_custkey = c_custkey
|
|
and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey
|
|
and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey
|
|
and o_orderdate between date '1995-01-01' and date '1996-12-31'
|
|
and p_type = 'STANDARD BRUSHED STEEL' ) as all_nations
|
|
group by o_year
|
|
order by o_year;
|
|
o_year mkt_share
|
|
1995 0.4495521838895718
|
|
1996 0.024585468215352495
|
|
EXPLAIN select nation, o_year, sum(amount) as sum_profit
|
|
from (select n_name as nation,
|
|
extract(year from o_orderdate) as o_year,
|
|
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
|
|
from part, supplier, lineitem, partsupp, orders, nation
|
|
where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
|
|
and ps_partkey = l_partkey and p_partkey = l_partkey
|
|
and o_orderkey = l_orderkey and s_nationkey = n_nationkey
|
|
and p_name like '%green%') as profit
|
|
group by nation, o_year
|
|
order by nation, o_year desc;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE supplier index PRIMARY,i_s_nationkey i_s_nationkey 5 NULL 10 Using where; Using index; Using temporary; Using filesort
|
|
1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1
|
|
1 SIMPLE partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 70
|
|
1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.partsupp.ps_partkey 1 Using where
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey 8
|
|
1 SIMPLE orders eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1
|
|
select nation, o_year, sum(amount) as sum_profit
|
|
from (select n_name as nation,
|
|
extract(year from o_orderdate) as o_year,
|
|
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
|
|
from part, supplier, lineitem, partsupp, orders, nation
|
|
where s_suppkey = l_suppkey and ps_suppkey = l_suppkey
|
|
and ps_partkey = l_partkey and p_partkey = l_partkey
|
|
and o_orderkey = l_orderkey and s_nationkey = n_nationkey
|
|
and p_name like '%green%') as profit
|
|
group by nation, o_year
|
|
order by nation, o_year desc;
|
|
nation o_year sum_profit
|
|
ARGENTINA 1997 18247.873399999993
|
|
ARGENTINA 1996 7731.089399999995
|
|
ARGENTINA 1995 134490.5697
|
|
ARGENTINA 1994 36767.101500000004
|
|
ARGENTINA 1993 35857.08
|
|
ARGENTINA 1992 35740
|
|
ETHIOPIA 1998 2758.7801999999992
|
|
ETHIOPIA 1997 19419.294599999997
|
|
ETHIOPIA 1995 51231.87439999999
|
|
ETHIOPIA 1994 3578.9478999999974
|
|
ETHIOPIA 1992 1525.8234999999986
|
|
IRAN 1998 37817.229600000006
|
|
IRAN 1997 52643.77359999999
|
|
IRAN 1996 70143.7761
|
|
IRAN 1995 84094.58260000001
|
|
IRAN 1994 18140.925599999995
|
|
IRAN 1993 78655.1676
|
|
IRAN 1992 87142.23960000002
|
|
IRAQ 1998 22860.8082
|
|
IRAQ 1997 93676.24359999999
|
|
IRAQ 1996 45103.3242
|
|
IRAQ 1994 36010.728599999995
|
|
IRAQ 1993 33221.9399
|
|
IRAQ 1992 47755.05900000001
|
|
KENYA 1998 44194.831999999995
|
|
KENYA 1997 57578.36259999999
|
|
KENYA 1996 59195.90210000001
|
|
KENYA 1995 79262.6278
|
|
KENYA 1994 102360.66609999999
|
|
KENYA 1993 128422.0196
|
|
KENYA 1992 181517.2089
|
|
MOROCCO 1998 41797.823199999984
|
|
MOROCCO 1997 23685.801799999994
|
|
MOROCCO 1996 62115.19579999998
|
|
MOROCCO 1995 42442.64300000001
|
|
MOROCCO 1994 48655.878000000004
|
|
MOROCCO 1993 22926.744400000003
|
|
MOROCCO 1992 32239.8088
|
|
PERU 1998 86999.36459999997
|
|
PERU 1997 121110.41070000001
|
|
PERU 1996 177040.40759999995
|
|
PERU 1995 122247.94520000002
|
|
PERU 1994 88046.25329999998
|
|
PERU 1993 49379.813799999996
|
|
PERU 1992 80646.86050000001
|
|
UNITED KINGDOM 1998 50577.25560000001
|
|
UNITED KINGDOM 1997 114288.8605
|
|
UNITED KINGDOM 1996 147684.46480000002
|
|
UNITED KINGDOM 1995 225267.65759999998
|
|
UNITED KINGDOM 1994 140595.5864
|
|
UNITED KINGDOM 1993 322548.49210000003
|
|
UNITED KINGDOM 1992 67747.88279999999
|
|
UNITED STATES 1998 3957.0431999999996
|
|
UNITED STATES 1997 94729.5704
|
|
UNITED STATES 1996 79297.85670000002
|
|
UNITED STATES 1995 62201.23360000001
|
|
UNITED STATES 1994 43075.629899999985
|
|
UNITED STATES 1993 27168.486199999996
|
|
UNITED STATES 1992 34092.366
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='extended_keys=on';
|
|
EXPLAIN select o_orderkey, p_partkey
|
|
from part, lineitem, orders
|
|
where p_retailprice > 1100 and o_orderdate='1997-01-01'
|
|
and o_orderkey=l_orderkey and p_partkey=l_partkey;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE part range PRIMARY,i_p_retailprice i_p_retailprice 9 NULL 1 Using where; Using index
|
|
1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 Using index
|
|
1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity i_l_partkey 9 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 1 Using index
|
|
select o_orderkey, p_partkey
|
|
from part, lineitem, orders
|
|
where p_retailprice > 1100 and o_orderdate='1997-01-01'
|
|
and o_orderkey=l_orderkey and p_partkey=l_partkey;
|
|
o_orderkey p_partkey
|
|
5895 200
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
DROP DATABASE dbt3_s001;
|
|
use test;
|
|
#
|
|
# Bug mdev-473: ANALYZE table locked for write
|
|
#
|
|
set use_stat_tables='complementary';
|
|
create table t1 (i int);
|
|
lock table t1 write;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
alter table t1 add column a varchar(8);
|
|
drop table t1;
|
|
#
|
|
# Bug mdev-487: memory leak in ANALYZE with stat tables
|
|
#
|
|
SET use_stat_tables = 'preferably';
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
DELETE FROM t1 WHERE a=1;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug mdev-518: corrupted/missing statistical tables
|
|
#
|
|
CREATE TABLE t1 (i int) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
FLUSH TABLE t1;
|
|
SET use_stat_tables='never';
|
|
EXPLAIN SELECT * FROM t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
|
|
FLUSH TABLES;
|
|
SET use_stat_tables='preferably';
|
|
EXPLAIN SELECT * FROM t1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2
|
|
DROP TABLE t1;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# Bug mdev-5204: invalid impossible where after reading const tables
|
|
# when use_stat_tables = 'preferably'
|
|
#
|
|
set use_stat_tables = 'preferably';
|
|
CREATE TABLE t1 (id int PRIMARY KEY) ENGINE=MyISAM;
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
CREATE TABLE t2 (name char(3)) ENGINE=MyISAM;
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status Table is already up to date
|
|
INSERT INTO t2 VALUES ('USA'),('AUS');
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
|
id name
|
|
1 AUS
|
|
EXPLAIN
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 0 Using where
|
|
ANALYZE TABLE t2;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 analyze status Engine-independent statistics collected
|
|
test.t2 analyze status OK
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
|
id name
|
|
1 AUS
|
|
EXPLAIN
|
|
SELECT * FROM t1 STRAIGHT_JOIN t2 WHERE name IN ( 'AUS','YEM' ) AND id = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
|
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
|
|
DROP TABLE t1,t2;
|
|
#
|
|
# MDEV-7370: Server deadlocks on renaming a table for which persistent statistics exists
|
|
#
|
|
drop database if exists db1;
|
|
drop database if exists db1;
|
|
create database db1;
|
|
create database db2;
|
|
use db1;
|
|
#
|
|
# First, run the original testcase:
|
|
#
|
|
create table t1 (i int);
|
|
insert into t1 values (10),(20);
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t1 analyze status Engine-independent statistics collected
|
|
db1.t1 analyze status OK
|
|
rename table t1 to db2.t1;
|
|
# Verify that stats in the old database are gone:
|
|
select * from mysql.column_stats where db_name='db1' and table_name='t1';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
select * from mysql.table_stats where db_name='db1' and table_name='t1';
|
|
db_name table_name cardinality
|
|
# Verify that stats are present in the new database:
|
|
select * from mysql.column_stats where db_name='db2' and table_name='t1';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
db2 t1 i 10 20 0.0000 4.0000 1.0000 0 NULL NULL
|
|
select * from mysql.table_stats where db_name='db2' and table_name='t1';
|
|
db_name table_name cardinality
|
|
db2 t1 2
|
|
#
|
|
# Now, try with more than one column and with indexes:
|
|
#
|
|
use test;
|
|
create table t1(a int primary key);
|
|
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
use db1;
|
|
create table t2 (a int, b int, c int, key IDX1(a), key IDX2(a,b));
|
|
insert into t2 select a/10, a/2, a from test.t1;
|
|
analyze table t2 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
db1.t2 analyze status Engine-independent statistics collected
|
|
db1.t2 analyze status OK
|
|
alter table t2 rename db2.t2;
|
|
# Verify that stats in the old database are gone:
|
|
select * from mysql.table_stats where db_name='db1' and table_name='t2';
|
|
db_name table_name cardinality
|
|
select * from mysql.column_stats where db_name='db1' and table_name='t2';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
select * from mysql.index_stats where db_name='db1' and table_name='t2';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
# Verify that stats are present in the new database:
|
|
select * from mysql.table_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name cardinality
|
|
db2 t2 10
|
|
select * from mysql.column_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
db2 t2 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
|
|
db2 t2 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
|
|
db2 t2 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
|
|
select * from mysql.index_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
db2 t2 IDX1 1 5.0000
|
|
db2 t2 IDX2 1 5.0000
|
|
db2 t2 IDX2 2 1.6667
|
|
use db2;
|
|
#
|
|
# Now, rename within the same database and verify:
|
|
#
|
|
rename table t2 to t3;
|
|
# No stats under old name:
|
|
select * from mysql.table_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name cardinality
|
|
select * from mysql.column_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
select * from mysql.index_stats where db_name='db2' and table_name='t2';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
# Stats under the new name:
|
|
select * from mysql.table_stats where db_name='db2' and table_name='t3';
|
|
db_name table_name cardinality
|
|
db2 t3 10
|
|
select * from mysql.column_stats where db_name='db2' and table_name='t3';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
db2 t3 a 0 1 0.0000 4.0000 5.0000 0 NULL NULL
|
|
db2 t3 b 0 5 0.0000 4.0000 1.6667 0 NULL NULL
|
|
db2 t3 c 0 9 0.0000 4.0000 1.0000 0 NULL NULL
|
|
select * from mysql.index_stats where db_name='db2' and table_name='t3';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
db2 t3 IDX1 1 5.0000
|
|
db2 t3 IDX2 1 5.0000
|
|
db2 t3 IDX2 2 1.6667
|
|
use test;
|
|
drop database db1;
|
|
drop database db2;
|
|
drop table t1;
|
|
#
|
|
# MDEV-16552: [10.0] ASAN global-buffer-overflow in is_stat_table / statistics_for_tables_is_needed
|
|
#
|
|
SET use_stat_tables = PREFERABLY;
|
|
SELECT CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' );
|
|
CONVERT_TZ( '1991-09-20 10:11:02', '+00:00', 'GMT' )
|
|
NULL
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-16757: manual addition of min/max statistics for BLOB
|
|
#
|
|
SET use_stat_tables= PREFERABLY;
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
|
|
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze Warning Engine-independent statistics are not collected for column 't'
|
|
test.t1 analyze status OK
|
|
SELECT * FROM mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 pk 1 2 0.0000 4.0000 1.0000 NULL NULL NULL
|
|
DELETE FROM mysql.column_stats
|
|
WHERE db_name='test' AND table_name='t1' AND column_name='t';
|
|
INSERT INTO mysql.column_stats VALUES
|
|
('test','t1','t','bar','foo', 0.0, 3.0, 1.0, 0, NULL, NULL);
|
|
SELECT * FROM mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 pk 1 2 0.0000 4.0000 1.0000 NULL NULL NULL
|
|
test t1 t bar foo 0.0000 3.0000 1.0000 0 NULL NULL
|
|
SELECT pk FROM t1;
|
|
pk
|
|
1
|
|
2
|
|
DROP TABLE t1;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-16760: CREATE OR REPLACE TABLE after ANALYZE TABLE
|
|
#
|
|
SET use_stat_tables= PREFERABLY;
|
|
CREATE TABLE t1 (pk int PRIMARY KEY, c varchar(32));
|
|
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT * FROM t1;
|
|
pk c
|
|
1 foo
|
|
2 bar
|
|
SELECT * FROM mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 pk 1 2 0.0000 4.0000 1.0000 NULL NULL NULL
|
|
test t1 c bar foo 0.0000 3.0000 1.0000 0 NULL NULL
|
|
CREATE OR REPLACE TABLE t1 (pk int PRIMARY KEY, a char(7));
|
|
SELECT * FROM t1;
|
|
pk a
|
|
SELECT * FROM mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
DROP TABLE t1;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-17023: Crash during read_histogram_for_table with optimizer_use_condition_selectivity set to 4
|
|
#
|
|
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
|
|
set @@optimizer_use_condition_selectivity=4;
|
|
set @@use_stat_tables= PREFERABLY;
|
|
explain
|
|
SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE PROFILING ALL NULL NULL NULL NULL NULL
|
|
1 SIMPLE global_priv ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join)
|
|
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-17734: AddressSanitizer: use-after-poison in create_key_parts_for_pseudo_indexes
|
|
#
|
|
set @@use_stat_tables= PREFERABLY;
|
|
set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
|
|
set @@optimizer_use_condition_selectivity=4;
|
|
set @save_use_stat_tables= @@use_stat_tables;
|
|
create table t1 (a int, b int);
|
|
insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10);
|
|
analyze table t1 persistent for columns (a) indexes ();
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select * from t1 where a=1 and b=3;
|
|
a b
|
|
1 3
|
|
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
drop table t1;
|
|
#
|
|
# MDEV-16711:CREATE OR REPLACE TABLE introducing BLOB column
|
|
#
|
|
SET use_stat_tables= PREFERABLY;
|
|
CREATE TABLE t1 (pk INT PRIMARY KEY, t CHAR(60));
|
|
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT);
|
|
SELECT MAX(pk) FROM t1;
|
|
MAX(pk)
|
|
NULL
|
|
DROP TABLE t1;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-17605: SHOW INDEXES with use_stat_tables='preferably'
|
|
#
|
|
set use_stat_tables='preferably';
|
|
CREATE DATABASE dbt3_s001;
|
|
use dbt3_s001;
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='extended_keys=off';
|
|
select * from mysql.table_stats;
|
|
db_name table_name cardinality
|
|
dbt3_s001 lineitem 6005
|
|
select * from mysql.index_stats;
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
dbt3_s001 lineitem PRIMARY 1 4.0033
|
|
dbt3_s001 lineitem PRIMARY 2 1.0000
|
|
dbt3_s001 lineitem i_l_shipdate 1 2.6500
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_suppkey_partkey 2 8.5786
|
|
dbt3_s001 lineitem i_l_partkey 1 30.0250
|
|
dbt3_s001 lineitem i_l_suppkey 1 600.5000
|
|
dbt3_s001 lineitem i_l_receiptdate 1 2.6477
|
|
dbt3_s001 lineitem i_l_orderkey 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 1 4.0033
|
|
dbt3_s001 lineitem i_l_orderkey_quantity 2 1.0404
|
|
dbt3_s001 lineitem i_l_commitdate 1 2.7160
|
|
SHOW INDEXES FROM lineitem;
|
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
|
|
lineitem 0 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
lineitem 0 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO
|
|
lineitem 1 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
lineitem 1 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
lineitem 1 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO
|
|
lineitem 1 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO
|
|
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_name='lineitem';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
|
|
def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
def dbt3_s001 lineitem 0 dbt3_s001 PRIMARY 2 l_linenumber A 6005 NULL NULL BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_shipdate 1 l_shipDATE A 2266 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey_partkey 2 l_suppkey A 699 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_partkey 1 l_partkey A 200 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_suppkey 1 l_suppkey A 10 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_receiptdate 1 l_receiptDATE A 2268 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 1 l_orderkey A 1500 NULL NULL BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_orderkey_quantity 2 l_quantity A 5771 NULL NULL YES BTREE NO
|
|
def dbt3_s001 lineitem 1 dbt3_s001 i_l_commitdate 1 l_commitDATE A 2210 NULL NULL YES BTREE NO
|
|
SELECT
|
|
COUNT(DISTINCT l_orderkey), COUNT(DISTINCT l_orderkey,l_linenumber),
|
|
COUNT(DISTINCT l_shipDATE),
|
|
COUNT(DISTINCT l_partkey), COUNT(DISTINCT l_partkey,l_suppkey),
|
|
COUNT(DISTINCT l_suppkey), COUNT(DISTINCT l_receiptDATE),
|
|
COUNT(DISTINCT l_orderkey, l_quantity), COUNT(DISTINCT l_commitDATE)
|
|
FROM lineitem;
|
|
COUNT(DISTINCT l_orderkey) COUNT(DISTINCT l_orderkey,l_linenumber) COUNT(DISTINCT l_shipDATE) COUNT(DISTINCT l_partkey) COUNT(DISTINCT l_partkey,l_suppkey) COUNT(DISTINCT l_suppkey) COUNT(DISTINCT l_receiptDATE) COUNT(DISTINCT l_orderkey, l_quantity) COUNT(DISTINCT l_commitDATE)
|
|
1500 6005 2266 200 700 10 2268 5772 2211
|
|
set optimizer_switch=@save_optimizer_switch;
|
|
DROP DATABASE dbt3_s001;
|
|
USE test;
|
|
delete from mysql.table_stats;
|
|
delete from mysql.column_stats;
|
|
delete from mysql.index_stats;
|
|
#
|
|
# MDEV-19352: Server crash in alloc_histograms_for_table_share upon query from information schema
|
|
#
|
|
use test;
|
|
set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
|
|
set @@optimizer_use_condition_selectivity= 4;
|
|
set use_stat_tables='preferably';
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (b INT);
|
|
CREATE VIEW v AS SELECT * FROM t1 JOIN t2;
|
|
INSERT INTO t2 SELECT * FROM x;
|
|
ERROR 42S02: Table 'test.x' doesn't exist
|
|
select * from information_schema.tables where table_name='v';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
|
|
def test v VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL VIEW NULL NULL
|
|
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
|
|
drop table t1,t2;
|
|
drop view v;
|
|
#
|
|
# MDEV-19407: Assertion `field->table->stats_is_read' failed in is_eits_usable
|
|
#
|
|
set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity;
|
|
set @@optimizer_use_condition_selectivity= 1;
|
|
set @@use_stat_tables='never';
|
|
create table t1(pk int);
|
|
insert into t1 values (4),(3);
|
|
set @@optimizer_use_condition_selectivity= 4;
|
|
set use_stat_tables='preferably';
|
|
INSERT INTO t1 SELECT * FROM x;
|
|
ERROR 42S02: Table 'test.x' doesn't exist
|
|
CREATE TABLE t2 SELECT pk FROM t1 WHERE pk>2;
|
|
select * from t2;
|
|
pk
|
|
4
|
|
3
|
|
drop table t1,t2;
|
|
create table t1(a int,b int, key k1(a) );
|
|
insert into t1 values(1,1),(2,2),(3,3);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select * from mysql.index_stats, t1 where index_name='k1' and t1.a > 1 and t1.b > 1;
|
|
db_name table_name index_name prefix_arity avg_frequency a b
|
|
test t1 k1 1 1.0000 2 2
|
|
test t1 k1 1 1.0000 3 3
|
|
drop table t1;
|
|
set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
|
|
set @save_optimizer_switch=@@optimizer_switch;
|
|
set use_stat_tables=@save_use_stat_tables;
|
|
#
|
|
# MDEV-18899: Server crashes in Field::set_warning_truncated_wrong_value
|
|
#
|
|
set names utf8;
|
|
set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity;
|
|
set optimizer_use_condition_selectivity=4;
|
|
set use_stat_tables=preferably;
|
|
set histogram_size=255;
|
|
create table t1 ( a varchar(255) character set utf8);
|
|
insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select HEX(RIGHT(min_value, 1)), length(min_value) from mysql.column_stats where db_name='test' and table_name='t1';
|
|
HEX(RIGHT(min_value, 1)) length(min_value)
|
|
A7 254
|
|
select HEX(RIGHT(max_value, 1)), length(max_value) from mysql.column_stats where db_name='test' and table_name='t1';
|
|
HEX(RIGHT(max_value, 1)) length(max_value)
|
|
A5 254
|
|
analyze select * from t1 where a >= 'ӥ';
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 50.00 50.00 Using where
|
|
set @save_sql_mode= @@sql_mode;
|
|
set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
update mysql.column_stats set min_value= REPEAT('ӥ',255) where db_name='test' and table_name='t1';
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'min_value' at row 1
|
|
select HEX(RIGHT(min_value, 1)), length(min_value) from mysql.column_stats where db_name='test' and table_name='t1';
|
|
HEX(RIGHT(min_value, 1)) length(min_value)
|
|
D3 255
|
|
analyze select * from t1 where a >= 'ӥ';
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 2.00 50.00 50.00 Using where
|
|
set names latin1;
|
|
drop table t1;
|
|
CREATE TABLE t1 (col1 date);
|
|
INSERT INTO t1 VALUES('2004-01-01'),('2004-02-29');
|
|
INSERT INTO t1 VALUES('0000-10-31');
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
update mysql.column_stats set min_value='2004-0-31123' where db_name='test' and table_name='t1';
|
|
select min_value from mysql.column_stats where db_name='test' and table_name='t1';
|
|
min_value
|
|
2004-0-31123
|
|
select * from t1;
|
|
col1
|
|
2004-01-01
|
|
2004-02-29
|
|
0000-10-31
|
|
set @@sql_mode= @save_sql_mode;
|
|
set @@use_stat_tables=@save_use_stat_tables;
|
|
set @@histogram_size= @save_histogram_size;
|
|
set @@histogram_type=@save_histogram_type;
|
|
set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity;
|
|
drop table t1;
|
|
#
|
|
# MDEV-20589: Server still crashes in Field::set_warning_truncated_wrong_value
|
|
#
|
|
set names utf8;
|
|
create table t1 ( a varchar(255) character set utf8);
|
|
insert into t1 values (REPEAT('ӥ',255)), (REPEAT('ç',255));
|
|
set use_stat_tables='preferably';
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
set @save_sql_mode= @@sql_mode;
|
|
set sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
|
|
update mysql.column_stats set min_value= REPEAT('ӥ',256) where db_name='test' and table_name='t1';
|
|
Warnings:
|
|
Warning 1265 Data truncated for column 'min_value' at row 1
|
|
set @@sql_mode= @save_sql_mode;
|
|
select length(a) from t1 where a=REPEAT('ӥ',255);
|
|
length(a)
|
|
510
|
|
set names latin1;
|
|
set @@use_stat_tables=@save_use_stat_tables;
|
|
drop table t1;
|
|
#
|
|
# MDEV-23753: SIGSEGV in Column_stat::store_stat_fields
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT) PARTITION BY HASH (b) PARTITIONS 2;
|
|
LOCK TABLES t1 WRITE;
|
|
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES ();
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (nonexisting) INDEXES (nonexisting);
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze error Invalid argument
|
|
DROP TABLE t1;
|
|
# please keep this at the last
|
|
set @@global.histogram_size=@save_histogram_size;
|
|
# Start of 10.4 tests
|
|
set histogram_size=0;
|
|
#
|
|
# MDEV-17255: New optimizer defaults and ANALYZE TABLE
|
|
#
|
|
create table t1 (a int, b int);
|
|
insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10);
|
|
set use_stat_tables= preferably_for_queries;
|
|
#
|
|
# with use_stat_tables= PREFERABLY_FOR_QUERIES
|
|
# analyze table t1 will not collect statistics
|
|
#
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
select * from mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
analyze
|
|
select * from t1 where a = 1 and b=3;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where
|
|
#
|
|
# with use_stat_tables= PREFERABLY_FOR_QUERIES
|
|
# analyze table t1 will collect statistics if we use PERSISTENT
|
|
# for columns, indexes or everything
|
|
#
|
|
analyze table t1 persistent for columns (a) indexes ();
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select * from mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL
|
|
# filtered shows that we used the data from stat tables
|
|
analyze
|
|
select * from t1 where a = 1 and b=3;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 25.00 10.00 Using where
|
|
#
|
|
# with use_stat_tables= PREFERABLY
|
|
# analyze table t1 will collect statistics
|
|
#
|
|
set use_stat_tables=PREFERABLY;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select * from mysql.column_stats;
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL
|
|
test t1 b 2 10 0.0000 4.0000 1.1111 0 NULL NULL
|
|
# filtered shows that we used the data from stat tables
|
|
analyze
|
|
select * from t1 where a=1 and b=3;
|
|
id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 2.78 10.00 Using where
|
|
drop table t1;
|
|
set @@global.histogram_size=@save_histogram_size;
|
|
#
|
|
# End of 10.4 tests
|
|
#
|
|
#
|
|
# MDEV-29693 ANALYZE TABLE still flushes table definition cache
|
|
# when engine-independent statistics is used
|
|
#
|
|
create table t1 (a int);
|
|
insert into t1 select seq from seq_0_to_99;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 50.51 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connect con1, localhost, root,,;
|
|
connection con1;
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 50.51 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connection default;
|
|
update t1 set a= a +100;
|
|
# Explain shows outdated statistics:
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 50.51 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connection con1;
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 50.51 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connection default;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
# Now explain shows updated statistics:
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 1.00 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connection con1;
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 1.00 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
connection con1;
|
|
# Run update and analyze in con1:
|
|
update t1 set a= a - 150;
|
|
analyze table t1 persistent for all;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
connection default;
|
|
# Explain shows updated statistics:
|
|
explain extended select count(*) from t1 where a < 50;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 100 100.00 Using where
|
|
Warnings:
|
|
Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`a` < 50
|
|
disconnect con1;
|
|
drop table t1;
|
|
#
|
|
# End of 10.6 tests
|
|
#
|
|
set global innodb_stats_persistent= @innodb_stats_persistent_save;
|
|
set global innodb_stats_persistent_sample_pages=
|
|
@innodb_stats_persistent_sample_pages_save;
|
|
set optimizer_switch=@save_optimizer_switch_for_stat_tables_test;
|
|
#
|
|
# MDEV-22851: Engine independent index statistics are incorrect for large tables on Windows.
|
|
#
|
|
CREATE TABLE t1(a INT) ENGINE=INNODB;
|
|
INSERT INTO t1 SELECT 1 FROM seq_1_to_60000;
|
|
SET @save_use_stat_tables= @@use_stat_tables;
|
|
SET use_stat_tables= preferably;
|
|
SELECT count(*) FROM t1;
|
|
count(*)
|
|
60000
|
|
CREATE INDEX idx ON t1(a);
|
|
ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a) INDEXES (idx);
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
SELECT * FROM mysql.index_stats where table_name='t1';
|
|
db_name table_name index_name prefix_arity avg_frequency
|
|
test t1 idx 1 60000.0000
|
|
SELECT * FROM mysql.column_stats where table_name='t1';
|
|
db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram
|
|
test t1 a 1 1 0.0000 4.0000 60000.0000 0 NULL NULL
|
|
SET use_stat_tables= @save_use_stat_tables;
|
|
DROP TABLE t1;
|
|
# end of 10.1 tests
|
|
SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT;
|