diff options
Diffstat (limited to 'mysql-test/r/selectivity_innodb.result')
| -rw-r--r-- | mysql-test/r/selectivity_innodb.result | 119 |
1 files changed, 119 insertions, 0 deletions
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result new file mode 100644 index 00000000000..0a6abfd66d3 --- /dev/null +++ b/mysql-test/r/selectivity_innodb.result @@ -0,0 +1,119 @@ +SET SESSION STORAGE_ENGINE='InnoDB'; +set @save_optimizer_switch_for_selectivity_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_use_condition_selectivity=@@optimizer_use_condition_selectivity; +EXPLAIN EXTENDED select sql_calc_found_rows +s_name, s_address +from supplier, nation +where s_suppkey in (select ps_suppkey from partsupp +where ps_partkey in (select p_partkey from part +where p_name like 'g%') +and ps_availqty > +(select 0.5 * sum(l_quantity) +from lineitem +where l_partkey = ps_partkey +and l_suppkey = ps_suppkey +and l_shipdate >= date('1993-01-01') +and l_shipdate < date('1993-01-01') + +interval '1' year )) +and s_nationkey = n_nationkey +and n_name = 'UNITED STATES' +order by s_name +limit 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY supplier ALL PRIMARY,i_s_nationkey NULL NULL NULL 10 100.00 Using where; Using filesort +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +1 PRIMARY nation eq_ref PRIMARY PRIMARY 4 dbt3_s001.supplier.s_nationkey 1 100.00 Using where +2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 100.00 Using where +2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where +Warnings: +Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 +Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 +Note 1003 select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`nation`.`n_name` = 'UNITED STATES') and (`dbt3_s001`.`nation`.`n_nationkey` = `dbt3_s001`.`supplier`.`s_nationkey`) and (`dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((select (0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`)) from `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`) and (`dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date))) and (`dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>((cast('1993-01-01' as date) + interval '1' year))))))) and (`dbt3_s001`.`part`.`p_name` like 'g%')) order by `dbt3_s001`.`supplier`.`s_name` limit 10 +select sql_calc_found_rows +s_name, s_address +from supplier, nation +where s_suppkey in (select ps_suppkey from partsupp +where ps_partkey in (select p_partkey from part +where p_name like 'g%') +and ps_availqty > +(select 0.5 * sum(l_quantity) +from lineitem +where l_partkey = ps_partkey +and l_suppkey = ps_suppkey +and l_shipdate >= date('1993-01-01') +and l_shipdate < date('1993-01-01') + +interval '1' year )) +and s_nationkey = n_nationkey +and n_name = 'UNITED STATES' +order by s_name +limit 10; +s_name s_address +Supplier#000000010 Saygah3gYWMp72i PY +set optimizer_use_condition_selectivity=3; +EXPLAIN EXTENDED select sql_calc_found_rows +s_name, s_address +from supplier, nation +where s_suppkey in (select ps_suppkey from partsupp +where ps_partkey in (select p_partkey from part +where p_name like 'g%') +and ps_availqty > +(select 0.5 * sum(l_quantity) +from lineitem +where l_partkey = ps_partkey +and l_suppkey = ps_suppkey +and l_shipdate >= date('1993-01-01') +and l_shipdate < date('1993-01-01') + +interval '1' year )) +and s_nationkey = n_nationkey +and n_name = 'UNITED STATES' +order by s_name +limit 10; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY nation ALL PRIMARY NULL NULL NULL 25 4.00 Using where; Using temporary; Using filesort +1 PRIMARY supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED part ALL PRIMARY NULL NULL NULL 200 4.17 Using where +2 MATERIALIZED partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey PRIMARY 4 dbt3_s001.part.p_partkey 3 100.00 Using where +4 DEPENDENT SUBQUERY lineitem ref i_l_shipdate,i_l_suppkey_partkey,i_l_partkey,i_l_suppkey i_l_suppkey_partkey 10 dbt3_s001.partsupp.ps_partkey,dbt3_s001.partsupp.ps_suppkey 8 100.00 Using where +Warnings: +Note 1276 Field or reference 'dbt3_s001.partsupp.ps_partkey' of SELECT #4 was resolved in SELECT #2 +Note 1276 Field or reference 'dbt3_s001.partsupp.ps_suppkey' of SELECT #4 was resolved in SELECT #2 +Note 1003 select sql_calc_found_rows `dbt3_s001`.`supplier`.`s_name` AS `s_name`,`dbt3_s001`.`supplier`.`s_address` AS `s_address` from `dbt3_s001`.`supplier` semi join (`dbt3_s001`.`part` join `dbt3_s001`.`partsupp`) join `dbt3_s001`.`nation` where ((`dbt3_s001`.`partsupp`.`ps_partkey` = `dbt3_s001`.`part`.`p_partkey`) and (`dbt3_s001`.`nation`.`n_name` = 'UNITED STATES') and (`dbt3_s001`.`supplier`.`s_nationkey` = `dbt3_s001`.`nation`.`n_nationkey`) and (`dbt3_s001`.`partsupp`.`ps_availqty` > <expr_cache><`dbt3_s001`.`partsupp`.`ps_partkey`,`dbt3_s001`.`partsupp`.`ps_suppkey`>((select (0.5 * sum(`dbt3_s001`.`lineitem`.`l_quantity`)) from `dbt3_s001`.`lineitem` where ((`dbt3_s001`.`lineitem`.`l_partkey` = `dbt3_s001`.`partsupp`.`ps_partkey`) and (`dbt3_s001`.`lineitem`.`l_suppkey` = `dbt3_s001`.`partsupp`.`ps_suppkey`) and (`dbt3_s001`.`lineitem`.`l_shipDATE` >= <cache>(cast('1993-01-01' as date))) and (`dbt3_s001`.`lineitem`.`l_shipDATE` < <cache>((cast('1993-01-01' as date) + interval '1' year))))))) and (`dbt3_s001`.`part`.`p_name` like 'g%')) order by `dbt3_s001`.`supplier`.`s_name` limit 10 +select sql_calc_found_rows +s_name, s_address +from supplier, nation +where s_suppkey in (select ps_suppkey from partsupp +where ps_partkey in (select p_partkey from part +where p_name like 'g%') +and ps_availqty > +(select 0.5 * sum(l_quantity) +from lineitem +where l_partkey = ps_partkey +and l_suppkey = ps_suppkey +and l_shipdate >= date('1993-01-01') +and l_shipdate < date('1993-01-01') + +interval '1' year )) +and s_nationkey = n_nationkey +and n_name = 'UNITED STATES' +order by s_name +limit 10; +s_name s_address +Supplier#000000010 Saygah3gYWMp72i PY +DROP DATABASE dbt3_s001; +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +set use_stat_tables=@save_use_stat_tables; +set optimizer_switch=@save_optimizer_switch_for_selectivity_test; +SET SESSION STORAGE_ENGINE=DEFAULT; |