diff options
Diffstat (limited to 'mysql-test/main/stat_tables_innodb.result')
| -rw-r--r-- | mysql-test/main/stat_tables_innodb.result | 548 |
1 files changed, 548 insertions, 0 deletions
diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result new file mode 100644 index 00000000000..42443bfcb72 --- /dev/null +++ b/mysql-test/main/stat_tables_innodb.result @@ -0,0 +1,548 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +set @save_optimizer_switch_for_stat_tables_test=@@optimizer_switch; +set optimizer_switch='extended_keys=on'; +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 use_stat_tables='preferably'; +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 orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 213 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 +1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.customer.c_nationkey 1 Using index +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +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.8715 +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 +orders 0 PRIMARY 1 o_orderkey A 1500 NULL NULL BTREE +orders 1 i_o_orderdate 1 o_orderDATE A 1126 NULL NULL YES BTREE +orders 1 i_o_custkey 1 o_custkey A 100 NULL NULL YES BTREE +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 orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 213 Using where; Using temporary; Using filesort +1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3_s001.orders.o_custkey 1 Using where +1 SIMPLE nation eq_ref PRIMARY,i_n_regionkey PRIMARY 4 dbt3_s001.customer.c_nationkey 1 +1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.customer.c_nationkey 1 Using index +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +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.8715 +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 orders ALL PRIMARY,i_o_orderdate,i_o_custkey NULL NULL NULL 1500 Using where; Using temporary; Using filesort +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 +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 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 PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where +1 SIMPLE part eq_ref PRIMARY PRIMARY 4 dbt3_s001.lineitem.l_partkey 1 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 +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; +set use_stat_tables=@save_use_stat_tables; +set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; +SET SESSION STORAGE_ENGINE=DEFAULT; |