diff options
Diffstat (limited to 'mysql-test/r/stat_tables_innodb.result')
| -rw-r--r-- | mysql-test/r/stat_tables_innodb.result | 80 |
1 files changed, 40 insertions, 40 deletions
diff --git a/mysql-test/r/stat_tables_innodb.result b/mysql-test/r/stat_tables_innodb.result index 42443bfcb72..369faf6d01f 100644 --- a/mysql-test/r/stat_tables_innodb.result +++ b/mysql-test/r/stat_tables_innodb.result @@ -68,11 +68,11 @@ 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 +1 SIMPLE customer hash_index PRIMARY,i_c_nationkey #hash#PRIMARY:i_c_nationkey 4:5 dbt3_s001.orders.o_custkey 150 Using where; Using index; Using join buffer (flat, BNLH join) +1 SIMPLE nation hash_ALL PRIMARY,i_n_regionkey #hash#PRIMARY 4 dbt3_s001.customer.c_nationkey 25 Using join buffer (incremental, BNLH join) +1 SIMPLE supplier hash_index PRIMARY,i_s_nationkey #hash#i_s_nationkey:i_s_nationkey 5:5 dbt3_s001.customer.c_nationkey 10 Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE lineitem hash_ALL PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity #hash#PRIMARY 4 dbt3_s001.orders.o_orderkey 6005 Using where; Using join buffer (incremental, BNLH join) 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 @@ -83,8 +83,8 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01' group by n_name order by revenue desc; n_name revenue -PERU 321915.8715 -ARGENTINA 69817.1451 +PERU 321915.87149999995 +ARGENTINA 69817.14510000001 set optimizer_switch=@save_optimizer_switch; delete from mysql.index_stats; select * from mysql.table_stats; @@ -199,11 +199,11 @@ 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 +1 SIMPLE customer hash_index PRIMARY,i_c_nationkey #hash#PRIMARY:i_c_nationkey 4:5 dbt3_s001.orders.o_custkey 150 Using where; Using index; Using join buffer (flat, BNLH join) +1 SIMPLE nation hash_ALL PRIMARY,i_n_regionkey #hash#PRIMARY 4 dbt3_s001.customer.c_nationkey 25 Using join buffer (incremental, BNLH join) +1 SIMPLE supplier hash_index PRIMARY,i_s_nationkey #hash#i_s_nationkey:i_s_nationkey 5:5 dbt3_s001.customer.c_nationkey 10 Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE lineitem hash_ALL PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity #hash#PRIMARY 4 dbt3_s001.orders.o_orderkey 6005 Using where; Using join buffer (incremental, BNLH join) 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 @@ -214,8 +214,8 @@ and r_name = 'AMERICA' and o_orderdate >= date '1995-01-01' group by n_name order by revenue desc; n_name revenue -PERU 321915.8715 -ARGENTINA 69817.1451 +PERU 321915.87149999995 +ARGENTINA 69817.14510000001 set optimizer_switch=@save_optimizer_switch; EXPLAIN select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / @@ -235,13 +235,13 @@ 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 +1 SIMPLE customer hash_index PRIMARY,i_c_nationkey #hash#PRIMARY:i_c_nationkey 4:5 dbt3_s001.orders.o_custkey 150 Using where; Using index; Using join buffer (flat, BNLH join) +1 SIMPLE n1 hash_index PRIMARY,i_n_regionkey #hash#PRIMARY:i_n_regionkey 4:5 dbt3_s001.customer.c_nationkey 25 Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE region ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE lineitem hash_ALL PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity #hash#PRIMARY 4 dbt3_s001.orders.o_orderkey 6005 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE part hash_ALL PRIMARY #hash#PRIMARY 4 dbt3_s001.lineitem.l_partkey 200 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE supplier hash_index PRIMARY,i_s_nationkey #hash#PRIMARY:i_s_nationkey 4:5 dbt3_s001.lineitem.l_suppkey 10 Using where; Using index; Using join buffer (incremental, BNLH join) +1 SIMPLE n2 hash_ALL PRIMARY #hash#PRIMARY 4 dbt3_s001.supplier.s_nationkey 25 Using join buffer (incremental, BNLH join) select o_year, sum(case when nation = 'UNITED STATES' then volume else 0 end) / sum(volume) as mkt_share @@ -260,7 +260,7 @@ group by o_year order by o_year; o_year mkt_share 1995 0.4495521838895718 -1996 0.024585468215352495 +1996 0.0245854682153525 EXPLAIN select nation, o_year, sum(amount) as sum_profit from (select n_name as nation, extract(year from o_orderdate) as o_year, @@ -274,11 +274,11 @@ 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 +1 SIMPLE nation hash_ALL PRIMARY #hash#PRIMARY 4 dbt3_s001.supplier.s_nationkey 25 Using join buffer (flat, BNLH join) +1 SIMPLE partsupp hash_ALL PRIMARY,i_ps_partkey,i_ps_suppkey #hash#i_ps_suppkey 4 dbt3_s001.supplier.s_suppkey 700 Using join buffer (incremental, BNLH join) +1 SIMPLE part hash_ALL PRIMARY #hash#PRIMARY 4 dbt3_s001.partsupp.ps_partkey 200 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE lineitem hash_ALL PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity #hash#i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.supplier.s_suppkey 6005 Using join buffer (incremental, BNLH join) +1 SIMPLE orders hash_index PRIMARY #hash#PRIMARY:i_o_orderdate 4:4 dbt3_s001.lineitem.l_orderkey 1500 Using index; Using join buffer (incremental, BNLH join) select nation, o_year, sum(amount) as sum_profit from (select n_name as nation, extract(year from o_orderdate) as o_year, @@ -316,38 +316,38 @@ 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 1997 57578.362599999986 +KENYA 1996 59195.9021 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 1997 23685.801799999997 +MOROCCO 1996 62115.195799999994 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 1998 86999.36459999999 +PERU 1997 121110.41070000002 PERU 1996 177040.40759999995 -PERU 1995 122247.94520000002 +PERU 1995 122247.94519999999 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 1995 225267.6576 +UNITED KINGDOM 1994 140595.58639999997 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 1996 79297.8567 UNITED STATES 1995 62201.23360000001 -UNITED STATES 1994 43075.629899999985 +UNITED STATES 1994 43075.6299 UNITED STATES 1993 27168.486199999996 UNITED STATES 1992 34092.366 set @save_optimizer_switch=@@optimizer_switch; @@ -358,8 +358,8 @@ 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 +1 SIMPLE orders hash_range PRIMARY,i_o_orderdate #hash#i_o_orderdate:i_o_orderdate 4:4 const 1 Using where; Using index; Using join buffer (flat, BNLH join) +1 SIMPLE lineitem hash_index PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity #hash#i_l_partkey:i_l_partkey 9:5 dbt3_s001.part.p_partkey,dbt3_s001.orders.o_orderkey 6005 Using index; Using join buffer (incremental, BNLH join) select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' |