diff options
23 files changed, 1324 insertions, 335 deletions
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index afa3a2ac3af..1804bd1c6fb 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -202,6 +202,8 @@ The following options may be given as the first argument: The maximum length of the result of function GROUP_CONCAT() -?, --help Display this help and exit. + --histogram-size=# Number of bytes used for a histogram. If set to 0, no + histograms are created by ANALYZE. --ignore-builtin-innodb Disable initialization of builtin InnoDB plugin --ignore-db-dirs=name @@ -505,13 +507,16 @@ The following options may be given as the first argument: takes into account to calculate cardinality of a partial join when it searches for the best execution plan Meaning: 1 - use selectivity of index backed range - conditions to calculate cardinality of the partial join + conditions to calculate the cardinality of a partial join if the last joined table is accessed by full table scan - or an index scan 2 - use selectivity of index backed - range conditions to calculate cardinality of the partial - join in any case 3 - additionally always use selectivity - of range conditions that are not backed by any index to - calculate cardinality of the partial join + or an index scan, 2 - use selectivity of index backed + range conditions to calculate the cardinality of a + partial join in any case, 3 - additionally always use + selectivity of range conditions that are not backed by + any index to calculate the cardinality of a partial join, + 4 - use histograms to calculate selectivity of range + conditions that are not backed by any index to calculate + the cardinality of a partial join. --performance-schema Enable the performance schema. --performance-schema-events-waits-history-long-size=# @@ -926,6 +931,7 @@ gdb FALSE general-log FALSE group-concat-max-len 1024 help TRUE +histogram-size 0 ignore-builtin-innodb FALSE ignore-db-dirs init-connect diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result index 3db8da46ea2..af3f6fdda2d 100644 --- a/mysql-test/r/selectivity.result +++ b/mysql-test/r/selectivity.result @@ -10,6 +10,7 @@ DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; EXPLAIN EXTENDED select sql_calc_found_rows s_name, s_address from supplier, nation @@ -84,7 +85,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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 +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 15.14 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 @@ -109,6 +110,114 @@ order by s_name limit 10; s_name s_address Supplier#000000010 Saygah3gYWMp72i PY +set histogram_size=15; +flush table part; +ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES(); +Table Op Msg_type Msg_text +dbt3_s001.part analyze status Table is already up to date +set optimizer_use_condition_selectivity=4; +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 part ALL PRIMARY NULL NULL NULL 200 6.25 Using where +1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; FirstMatch(supplier) +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 15.14 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_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`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 +set histogram_size=24; +flush table nation; +ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES(); +Table Op Msg_type Msg_text +dbt3_s001.nation analyze status Table is already up to date +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 part ALL PRIMARY NULL NULL NULL 200 6.25 Using where +1 PRIMARY partsupp ref PRIMARY,i_ps_partkey,i_ps_suppkey i_ps_partkey 4 dbt3_s001.part.p_partkey 3 100.00 Using where; FirstMatch(supplier) +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 15.14 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_suppkey` = `dbt3_s001`.`supplier`.`s_suppkey`) and (`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 histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result index 0a6abfd66d3..9ececad3492 100644 --- a/mysql-test/r/selectivity_innodb.result +++ b/mysql-test/r/selectivity_innodb.result @@ -13,6 +13,7 @@ DROP DATABASE IF EXISTS dbt3_s001; CREATE DATABASE dbt3_s001; use dbt3_s001; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; EXPLAIN EXTENDED select sql_calc_found_rows s_name, s_address from supplier, nation @@ -87,7 +88,116 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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 +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 14.37 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 +set histogram_size=15; +flush table part; +ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES(); +Table Op Msg_type Msg_text +dbt3_s001.part analyze status OK +set optimizer_use_condition_selectivity=4; +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 6.25 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 14.37 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 +set histogram_size=24; +flush table nation; +ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES(); +Table Op Msg_type Msg_text +dbt3_s001.nation analyze status OK +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 6.25 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 14.37 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 @@ -113,6 +223,7 @@ limit 10; s_name s_address Supplier#000000010 Saygah3gYWMp72i PY DROP DATABASE dbt3_s001; +set histogram_size=@save_histogram_size; 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; diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index ba0390f98db..2abff4416c9 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -64,13 +64,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -87,8 +87,8 @@ COUNT(*) 40 SELECT * FROM mysql.column_stats WHERE db_name='test' AND table_name='t1' AND column_name='a'; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL SELECT MIN(t1.a), MAX(t1.a), (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.a)", @@ -99,8 +99,8 @@ MIN(t1.a) MAX(t1.a) NULLS_RATIO(t1.a) AVG_FREQUENCY(t1.a) 0 49 0.2000 1.0000 SELECT * FROM mysql.column_stats WHERE db_name='test' AND table_name='t1' AND column_name='b'; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL SELECT MIN(t1.b), MAX(t1.b), (SELECT COUNT(*) FROM t1 WHERE t1.b IS NULL) / (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.b)", @@ -111,8 +111,8 @@ MIN(t1.b) MAX(t1.b) NULLS_RATIO(t1.b) AVG_FREQUENCY(t1.b) vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 SELECT * FROM mysql.column_stats WHERE db_name='test' AND table_name='t1' AND column_name='c'; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL SELECT MIN(t1.c), MAX(t1.c), (SELECT COUNT(*) FROM t1 WHERE t1.c IS NULL) / (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.c)", @@ -123,8 +123,8 @@ MIN(t1.c) MAX(t1.c) NULLS_RATIO(t1.c) AVG_FREQUENCY(t1.c) aaaa dddddddd 0.1250 7.0000 SELECT * FROM mysql.column_stats WHERE db_name='test' AND table_name='t1' AND column_name='d'; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL SELECT MIN(t1.d), MAX(t1.d), (SELECT COUNT(*) FROM t1 WHERE t1.d IS NULL) / (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.d)", @@ -135,8 +135,8 @@ MIN(t1.d) MAX(t1.d) NULLS_RATIO(t1.d) AVG_FREQUENCY(t1.d) 1989-03-12 1999-07-23 0.1500 8.5000 SELECT * FROM mysql.column_stats WHERE db_name='test' AND table_name='t1' AND column_name='e'; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL SELECT MIN(t1.e), MAX(t1.e), (SELECT COUNT(*) FROM t1 WHERE t1.e IS NULL) / (SELECT COUNT(*) FROM t1) AS "NULLS_RATIO(t1.e)", @@ -206,6 +206,28 @@ WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) AS 'ARITY 3'; ARITY 1 ARITY 2 ARITY 3 6.2000 1.6875 1.1304 +DELETE FROM mysql.column_stats; +set histogram_size=4; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date +SELECT db_name, table_name, column_name, +min_value, max_value, +nulls_ratio, avg_frequency, +hist_size, HEX(histogram) +FROM mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_frequency hist_size HEX(histogram) +test t1 a 0 49 0.0000 1.0000 4 2E62A1D0 +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 4 003FBFFF +test t1 c aaaa dddddddd 0.1250 7.0000 4 0055AAFF +test t1 d 1989-03-12 1999-07-23 0.1500 8.5000 4 009393FF +test t1 e 0.01 0.112 0.2250 6.2000 4 000564E1 +test t1 f 1 5 0.2000 6.4000 4 3F7FBFBF +DELETE FROM mysql.column_stats; +set histogram_size= 0; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Table is already up to date CREATE TABLE t3 ( a int NOT NULL PRIMARY KEY, b varchar(32), @@ -238,16 +260,16 @@ db_name table_name cardinality test t1 40 test t3 17 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -267,16 +289,16 @@ db_name table_name cardinality test s1 40 test t3 17 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test s1 a 0 49 0.0000 4.0000 1.0000 -test s1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test s1 e 0.01 0.112 0.2250 8.0000 6.2000 -test s1 f 1 5 0.2000 1.0000 6.4000 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test s1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test s1 PRIMARY 1 1.0000 @@ -296,16 +318,16 @@ db_name table_name cardinality test t1 40 test t3 17 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t3 a 0 38 0.0000 4.0000 1.0000 -test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 -test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t3 a 0 38 0.0000 4.0000 1.0000 0 NULL +test t3 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.1765 18.0714 2.8000 0 NULL +test t3 c aaaa dddddddd 0.1176 6.4000 3.7500 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -324,13 +346,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -365,13 +387,13 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`y`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 y 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 y 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL ALTER TABLE t1 CHANGE COLUMN x b varchar(32), CHANGE COLUMN y e double; SHOW CREATE TABLE t1; @@ -390,13 +412,13 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL ALTER TABLE t1 RENAME TO s1, CHANGE COLUMN b x varchar(32); SHOW CREATE TABLE s1; Table Create Table @@ -417,13 +439,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test s1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test s1 a 0 49 0.0000 4.0000 1.0000 -test s1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test s1 e 0.01 0.112 0.2250 8.0000 6.2000 -test s1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test s1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test s1 x vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test s1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test s1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test s1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test s1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test s1 PRIMARY 1 1.0000 @@ -455,13 +477,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -490,12 +512,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -519,12 +541,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -535,13 +557,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx4); 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 -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -576,12 +598,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`x`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -605,12 +627,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -624,13 +646,13 @@ LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/save_index_stats' INTO TABLE mysql.index_stats FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -658,12 +680,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -721,12 +743,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -735,13 +757,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 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 -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b NULL NULL 1.0000 NULL NULL -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b NULL NULL 1.0000 NULL NULL 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -758,13 +780,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 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 -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -790,12 +812,12 @@ t1 CREATE TABLE `t1` ( KEY `idx3` (`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -820,12 +842,12 @@ t1 CREATE TABLE `t1` ( KEY `idx4` (`e`,`b`,`d`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -834,13 +856,13 @@ ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(b) INDEXES(idx1, idx2, idx4); 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 -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -862,7 +884,7 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency ANALYZE TABLE t1 PERSISTENT FOR COLUMNS(c,e,b) INDEXES(idx2,idx4); @@ -872,10 +894,10 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 idx2 1 7.0000 @@ -912,13 +934,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -942,19 +964,19 @@ db_name table_name cardinality test t1 40 test t2 40 SELECT * FROM mysql.column_stats ORDER BY column_name; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t2 a 0 49 0.0000 4.0000 1.0000 -test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t2 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 @@ -986,13 +1008,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t2 40 SELECT * FROM mysql.column_stats ORDER BY column_name; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t2 b vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 17.1250 6.4000 0 NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency test t2 PRIMARY 1 1.0000 @@ -1122,12 +1144,12 @@ MODIFY COLUMN b text, ADD INDEX idx1 (b(4), e), ADD INDEX idx4 (e, b(4), d); SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t2 idx3 1 8.5000 @@ -1143,18 +1165,18 @@ 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 -test t2 a 0 49 0.0000 4.0000 1.0000 -test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t2 e 0.01 0.112 0.2250 8.0000 6.2000 -test t2 f 1 5 0.2000 1.0000 6.4000 -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t1 b NULL NULL 0.2000 17.1250 NULL +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t2 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t2 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t2 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t2 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t1 b NULL NULL 0.2000 17.1250 NULL NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t2 idx3 1 8.5000 @@ -1187,7 +1209,7 @@ mysql.column_stats analyze status OK SELECT * FROM mysql.table_stats; db_name table_name cardinality SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency set use_stat_tables='never'; @@ -1198,13 +1220,13 @@ SELECT * FROM mysql.table_stats; db_name table_name cardinality test t1 40 SELECT * FROM mysql.column_stats; -db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency -test t1 a 0 49 0.0000 4.0000 1.0000 -test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 -test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 -test t1 e 0.01 0.112 0.2250 8.0000 6.2000 -test t1 f 1 5 0.2000 1.0000 6.4000 -test t1 b NULL NULL 0.2000 17.1250 NULL +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size histogram +test t1 a 0 49 0.0000 4.0000 1.0000 0 NULL +test t1 c aaaa dddddddd 0.1250 6.6571 7.0000 0 NULL +test t1 d 1989-03-12 1999-07-23 0.1500 3.0000 8.5000 0 NULL +test t1 e 0.01 0.112 0.2250 8.0000 6.2000 0 NULL +test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL +test t1 b NULL NULL 0.2000 17.1250 NULL NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t1 PRIMARY 1 1.0000 diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 4bd5c45d944..4ceedffa5cb 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -286,6 +286,8 @@ column_stats CREATE TABLE `column_stats` ( `nulls_ratio` decimal(12,4) DEFAULT NULL, `avg_length` decimal(12,4) DEFAULT NULL, `avg_frequency` decimal(12,4) DEFAULT NULL, + `hist_size` tinyint(3) unsigned DEFAULT NULL, + `histogram` varbinary(255) DEFAULT NULL, PRIMARY KEY (`db_name`,`table_name`,`column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Columns' show create table index_stats; diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql.result b/mysql-test/suite/funcs_1/r/is_columns_mysql.result index df3ca6b366d..bcd9c14f307 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -13,6 +13,8 @@ def mysql column_stats avg_frequency 8 NULL YES decimal NULL NULL 12 4 NULL NULL def mysql column_stats avg_length 7 NULL YES decimal NULL NULL 12 4 NULL NULL NULL decimal(12,4) select,insert,update,references def mysql column_stats column_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql column_stats db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql column_stats histogram 10 NULL YES varbinary 255 255 NULL NULL NULL NULL NULL varbinary(255) select,insert,update,references +def mysql column_stats hist_size 9 NULL YES tinyint NULL NULL 3 0 NULL NULL NULL tinyint(3) unsigned select,insert,update,references def mysql column_stats max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references def mysql column_stats min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references def mysql column_stats nulls_ratio 6 NULL YES decimal NULL NULL 12 4 NULL NULL NULL decimal(12,4) select,insert,update,references @@ -256,6 +258,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 1.0000 blob NULL NULL 1.0000 longblob NULL NULL +1.0000 varbinary NULL NULL 1.0000 char latin1 latin1_bin 1.0000 char latin1 latin1_swedish_ci 1.0000 varchar latin1 latin1_swedish_ci @@ -328,6 +331,8 @@ NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp NULL mysql column_stats nulls_ratio decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_length decimal NULL NULL NULL NULL decimal(12,4) NULL mysql column_stats avg_frequency decimal NULL NULL NULL NULL decimal(12,4) +NULL mysql column_stats hist_size tinyint NULL NULL NULL NULL tinyint(3) unsigned +1.0000 mysql column_stats histogram varbinary 255 255 NULL NULL varbinary(255) 3.0000 mysql db Host char 60 180 utf8 utf8_bin char(60) 3.0000 mysql db Db char 64 192 utf8 utf8_bin char(64) 3.0000 mysql db User char 16 48 utf8 utf8_bin char(16) diff --git a/mysql-test/suite/sys_vars/r/histogram_size_basic.result b/mysql-test/suite/sys_vars/r/histogram_size_basic.result new file mode 100644 index 00000000000..1f310600d00 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/histogram_size_basic.result @@ -0,0 +1,136 @@ +SET @start_global_value = @@global.histogram_size; +SELECT @start_global_value; +@start_global_value +0 +SET @start_session_value = @@session.histogram_size; +SELECT @start_session_value; +@start_session_value +0 +'#--------------------FN_DYNVARS_053_01-------------------------#' +SET @@global.histogram_size = DEFAULT; +SELECT @@global.histogram_size; +@@global.histogram_size +0 +SET @@session.histogram_size = DEFAULT; +SELECT @@session.histogram_size; +@@session.histogram_size +0 +'#--------------------FN_DYNVARS_053_03-------------------------#' +SET @@global.histogram_size = 1; +SELECT @@global.histogram_size; +@@global.histogram_size +1 +SET @@global.histogram_size = 31; +SELECT @@global.histogram_size; +@@global.histogram_size +31 +SET @@global.histogram_size = 255; +SELECT @@global.histogram_size; +@@global.histogram_size +255 +'#--------------------FN_DYNVARS_053_04-------------------------#' +SET @@session.histogram_size = 1; +SELECT @@session.histogram_size; +@@session.histogram_size +1 +SET @@session.histogram_size = 31; +SELECT @@session.histogram_size; +@@session.histogram_size +31 +SET @@session.histogram_size = 255; +SELECT @@session.histogram_size; +@@session.histogram_size +255 +'#------------------FN_DYNVARS_053_05-----------------------#' +SET @@global.histogram_size = -1; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '-1' +SELECT @@global.histogram_size; +@@global.histogram_size +0 +SET @@global.histogram_size = 256; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '256' +SELECT @@global.histogram_size; +@@global.histogram_size +255 +SET @@global.histogram_size = 1024; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '1024' +SELECT @@global.histogram_size; +@@global.histogram_size +255 +SET @@global.histogram_size = 4.5; +ERROR 42000: Incorrect argument type to variable 'histogram_size' +SELECT @@global.histogram_size; +@@global.histogram_size +255 +SET @@global.histogram_size = test; +ERROR 42000: Incorrect argument type to variable 'histogram_size' +SELECT @@global.histogram_size; +@@global.histogram_size +255 +SET @@session.histogram_size = -1; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '-1' +SELECT @@session.histogram_size; +@@session.histogram_size +0 +SET @@session.histogram_size = 256; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '256' +SELECT @@session.histogram_size; +@@session.histogram_size +255 +SET @@session.histogram_size = 1024; +Warnings: +Warning 1292 Truncated incorrect histogram_size value: '1024' +SELECT @@session.histogram_size; +@@session.histogram_size +255 +SET @@session.histogram_size = 4.5; +ERROR 42000: Incorrect argument type to variable 'histogram_size' +SELECT @@session.histogram_size; +@@session.histogram_size +255 +SET @@session.histogram_size = test; +ERROR 42000: Incorrect argument type to variable 'histogram_size' +SELECT @@session.histogram_size; +@@session.histogram_size +255 +'#------------------FN_DYNVARS_053_06-----------------------#' +SELECT @@global.histogram_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='histogram_size'; +@@global.histogram_size = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_053_07-----------------------#' +SELECT @@session.histogram_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='histogram_size'; +@@session.histogram_size = VARIABLE_VALUE +1 +'#------------------FN_DYNVARS_053_08-----------------------#' +SET @@global.histogram_size = TRUE; +SET @@global.histogram_size = FALSE; +'#---------------------FN_DYNVARS_001_09----------------------#' +SET @@global.histogram_size = 10; +SELECT @@histogram_size = @@global.histogram_size; +@@histogram_size = @@global.histogram_size +0 +'#---------------------FN_DYNVARS_001_10----------------------#' +SET @@histogram_size = 100; +SELECT @@histogram_size = @@local.histogram_size; +@@histogram_size = @@local.histogram_size +1 +SELECT @@local.histogram_size = @@session.histogram_size; +@@local.histogram_size = @@session.histogram_size +1 +SET @@global.histogram_size = @start_global_value; +SELECT @@global.histogram_size; +@@global.histogram_size +0 +SET @@session.histogram_size = @start_session_value; +SELECT @@session.histogram_size; +@@session.histogram_size +0 diff --git a/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result b/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result index 332194e369e..418c221b5aa 100644 --- a/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result +++ b/mysql-test/suite/sys_vars/r/optimizer_use_condition_selectivity_basic.result @@ -40,6 +40,10 @@ SET @@global.optimizer_use_condition_selectivity = 3; SELECT @@global.optimizer_use_condition_selectivity; @@global.optimizer_use_condition_selectivity 3 +SET @@global.optimizer_use_condition_selectivity = 4; +SELECT @@global.optimizer_use_condition_selectivity; +@@global.optimizer_use_condition_selectivity +4 '#--------------------FN_DYNVARS_115_04-------------------------#' SELECT @@session.optimizer_use_condition_selectivity; @@session.optimizer_use_condition_selectivity @@ -56,6 +60,10 @@ SET @@session.optimizer_use_condition_selectivity = 3; SELECT @@session.optimizer_use_condition_selectivity; @@session.optimizer_use_condition_selectivity 3 +SET @@session.optimizer_use_condition_selectivity = 4; +SELECT @@session.optimizer_use_condition_selectivity; +@@session.optimizer_use_condition_selectivity +4 '#------------------FN_DYNVARS_115_05-----------------------#' SET @@global.optimizer_use_condition_selectivity = ON; ERROR 42000: Incorrect argument type to variable 'optimizer_use_condition_selectivity' diff --git a/mysql-test/suite/sys_vars/t/histogram_size_basic.test b/mysql-test/suite/sys_vars/t/histogram_size_basic.test new file mode 100644 index 00000000000..d65936e3616 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/histogram_size_basic.test @@ -0,0 +1,142 @@ +--source include/load_sysvars.inc + +############################################################## +# START OF histogram_size TESTS # +############################################################## + + +############################################################# +# Save initial value # +############################################################# + +SET @start_global_value = @@global.histogram_size; +SELECT @start_global_value; +SET @start_session_value = @@session.histogram_size; +SELECT @start_session_value; + +--echo '#--------------------FN_DYNVARS_053_01-------------------------#' +################################################################ +# Display the DEFAULT value of histogram_size # +################################################################ + +SET @@global.histogram_size = DEFAULT; +SELECT @@global.histogram_size; + +SET @@session.histogram_size = DEFAULT; +SELECT @@session.histogram_size; + +--echo '#--------------------FN_DYNVARS_053_03-------------------------#' +######################################################################## +# Change the value of histogram_size to a valid value for GLOBAL Scope # +######################################################################## + +SET @@global.histogram_size = 1; +SELECT @@global.histogram_size; +SET @@global.histogram_size = 31; +SELECT @@global.histogram_size; +SET @@global.histogram_size = 255; +SELECT @@global.histogram_size; + +--echo '#--------------------FN_DYNVARS_053_04-------------------------#' +######################################################################### +# Change the value of histogram_size to a valid value for SESSION Scope # +######################################################################### + +SET @@session.histogram_size = 1; +SELECT @@session.histogram_size; +SET @@session.histogram_size = 31; +SELECT @@session.histogram_size; +SET @@session.histogram_size = 255; +SELECT @@session.histogram_size; + +--echo '#------------------FN_DYNVARS_053_05-----------------------#' +########################################################## +# Change the value of histogram_size to an invalid value # +########################################################### + +SET @@global.histogram_size = -1; +SELECT @@global.histogram_size; +SET @@global.histogram_size = 256; +SELECT @@global.histogram_size; +SET @@global.histogram_size = 1024; +SELECT @@global.histogram_size; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.histogram_size = 4.5; +SELECT @@global.histogram_size; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.histogram_size = test; +SELECT @@global.histogram_size; + +SET @@session.histogram_size = -1; +SELECT @@session.histogram_size; +SET @@session.histogram_size = 256; +SELECT @@session.histogram_size; +SET @@session.histogram_size = 1024; +SELECT @@session.histogram_size; + +--Error ER_WRONG_TYPE_FOR_VAR +SET @@session.histogram_size = 4.5; +SELECT @@session.histogram_size; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@session.histogram_size = test; +SELECT @@session.histogram_size; + +--echo '#------------------FN_DYNVARS_053_06-----------------------#' +#################################################################### +# Check if the value in GLOBAL Table matches value in variable # +#################################################################### + +SELECT @@global.histogram_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='histogram_size'; + +--echo '#------------------FN_DYNVARS_053_07-----------------------#' +#################################################################### +# Check if the value in SESSION Table matches value in variable # +#################################################################### + +SELECT @@session.histogram_size = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='histogram_size'; + + +--echo '#------------------FN_DYNVARS_053_08-----------------------#' +#################################################################### +# Check if TRUE and FALSE values can be used on variable # +#################################################################### + +SET @@global.histogram_size = TRUE; +SET @@global.histogram_size = FALSE; + +--echo '#---------------------FN_DYNVARS_001_09----------------------#' +################################################################################# +# Check if accessing variable with and without GLOBAL point to same variable # +################################################################################# + +SET @@global.histogram_size = 10; +SELECT @@histogram_size = @@global.histogram_size; + +--echo '#---------------------FN_DYNVARS_001_10----------------------#' +######################################################################################################## +# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable # +######################################################################################################## + +SET @@histogram_size = 100; +SELECT @@histogram_size = @@local.histogram_size; +SELECT @@local.histogram_size = @@session.histogram_size; + +#################################### +# Restore initial value # +#################################### + +SET @@global.histogram_size = @start_global_value; +SELECT @@global.histogram_size; +SET @@session.histogram_size = @start_session_value; +SELECT @@session.histogram_size; + + +################################################### +# END OF histogram_size TESTS # +################################################### + diff --git a/mysql-test/suite/sys_vars/t/optimizer_use_condition_selectivity_basic.test b/mysql-test/suite/sys_vars/t/optimizer_use_condition_selectivity_basic.test index d898cf448dc..58a1af4b975 100644 --- a/mysql-test/suite/sys_vars/t/optimizer_use_condition_selectivity_basic.test +++ b/mysql-test/suite/sys_vars/t/optimizer_use_condition_selectivity_basic.test @@ -52,6 +52,8 @@ SET @@global.optimizer_use_condition_selectivity = 2; SELECT @@global.optimizer_use_condition_selectivity; SET @@global.optimizer_use_condition_selectivity = 3; SELECT @@global.optimizer_use_condition_selectivity; +SET @@global.optimizer_use_condition_selectivity = 4; +SELECT @@global.optimizer_use_condition_selectivity; --echo '#--------------------FN_DYNVARS_115_04-------------------------#' @@ -66,6 +68,8 @@ SET @@session.optimizer_use_condition_selectivity = 2; SELECT @@session.optimizer_use_condition_selectivity; SET @@session.optimizer_use_condition_selectivity = 3; SELECT @@session.optimizer_use_condition_selectivity; +SET @@session.optimizer_use_condition_selectivity = 4; +SELECT @@session.optimizer_use_condition_selectivity; --echo '#------------------FN_DYNVARS_115_05-----------------------#' diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 4756c6d4816..77089271cdd 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -16,6 +16,7 @@ CREATE DATABASE dbt3_s001; use dbt3_s001; set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +set @save_histogram_size=@@histogram_size; --disable_query_log --disable_result_log @@ -58,8 +59,29 @@ set optimizer_use_condition_selectivity=3; eval EXPLAIN EXTENDED $Q20; eval $Q20; +set histogram_size=15; + +flush table part; + +ANALYZE TABLE part PERSISTENT FOR COLUMNS(p_name) INDEXES(); + +set optimizer_use_condition_selectivity=4; + +eval EXPLAIN EXTENDED $Q20; +eval $Q20; + +set histogram_size=24; + +flush table nation; + +ANALYZE TABLE nation PERSISTENT FOR COLUMNS(n_name) INDEXES(); + +eval EXPLAIN EXTENDED $Q20; +eval $Q20; + DROP DATABASE dbt3_s001; +set histogram_size=@save_histogram_size; set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test index 4f2d0510c11..65412d849ed 100644 --- a/mysql-test/t/statistics.test +++ b/mysql-test/t/statistics.test @@ -165,6 +165,23 @@ SELECT WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) AS 'ARITY 3'; + +DELETE FROM mysql.column_stats; + +set histogram_size=4; +ANALYZE TABLE t1; + +SELECT db_name, table_name, column_name, + min_value, max_value, + nulls_ratio, avg_frequency, + hist_size, HEX(histogram) + FROM mysql.column_stats; + +DELETE FROM mysql.column_stats; +set histogram_size= 0; +ANALYZE TABLE t1; + + CREATE TABLE t3 ( a int NOT NULL PRIMARY KEY, b varchar(32), diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index a55c5f60351..6a54f339cd0 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -108,6 +108,6 @@ set @had_proxies_priv_table= @@warning_count != 0; CREATE TABLE IF NOT EXISTS table_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, cardinality bigint(21) unsigned DEFAULT NULL, PRIMARY KEY (db_name,table_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Tables'; -CREATE TABLE IF NOT EXISTS column_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio decimal(12,4) DEFAULT NULL, avg_length decimal(12,4) DEFAULT NULL, avg_frequency decimal(12,4) DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; +CREATE TABLE IF NOT EXISTS column_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, column_name varchar(64) NOT NULL, min_value varchar(255) DEFAULT NULL, max_value varchar(255) DEFAULT NULL, nulls_ratio decimal(12,4) DEFAULT NULL, avg_length decimal(12,4) DEFAULT NULL, avg_frequency decimal(12,4) DEFAULT NULL, hist_size tinyint unsigned, histogram varbinary(255), PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; CREATE TABLE IF NOT EXISTS index_stats (db_name varchar(64) NOT NULL, table_name varchar(64) NOT NULL, index_name varchar(64) NOT NULL, prefix_arity int(11) unsigned NOT NULL, avg_frequency decimal(12,4) DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; diff --git a/sql/field.cc b/sql/field.cc index e54a3e59795..afa12f84178 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -8420,6 +8420,19 @@ my_decimal *Field_bit::val_decimal(my_decimal *deciaml_value) } +double Field_bit::middle_point_pos(Field *min, Field *max) +{ + double n, d; + n= val_real() - min->val_real(); + if (n < 0) + return 0.0; + d= max->val_real() - min->val_real(); + if (d <= 0) + return 1.0; + return min(n/d, 1.0); +} + + /* Compare two bit fields using pointers within the record. SYNOPSIS diff --git a/sql/field.h b/sql/field.h index 40cbcca09ad..b98a1fdba14 100644 --- a/sql/field.h +++ b/sql/field.h @@ -472,6 +472,10 @@ public: } return update_fl; } + virtual void store_field_value(uchar *val, uint len) + { + memcpy(ptr, val, len); + } virtual uint decimals() const { return 0; } /* Caller beware: sql_type can change str.Ptr, so check @@ -2299,6 +2303,11 @@ public: } return update_fl; } + void store_field_value(uchar *val, uint len) + { + store(*((longlong *)val), TRUE); + } + double middle_point_pos(Field *min, Field *max); void get_image(uchar *buff, uint length, CHARSET_INFO *cs) { get_key_image(buff, length, itRAW); } void set_image(const uchar *buff,uint length, CHARSET_INFO *cs) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 364b01aebb2..904356f6eb7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -3324,60 +3324,64 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) table->cond_selectivity= 1.0; - if (bitmap_is_clear_all(used_fields)) - DBUG_RETURN(FALSE); - - PARAM param; - MEM_ROOT alloc; - init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0, - MYF(MY_THREAD_SPECIFIC)); - param.thd= thd; - param.mem_root= &alloc; - param.old_root= thd->mem_root; - param.table= table; - param.is_ror_scan= FALSE; - - if (create_key_parts_for_pseudo_indexes(¶m, used_fields)) + if (!bitmap_is_clear_all(used_fields)) { - free_root(&alloc, MYF(0)); - DBUG_RETURN(FALSE); - } + PARAM param; + MEM_ROOT alloc; + SEL_TREE *tree; + SEL_ARG **key, **end; + uint idx= 0; + + init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0, + MYF(MY_THREAD_SPECIFIC)); + param.thd= thd; + param.mem_root= &alloc; + param.old_root= thd->mem_root; + param.table= table; + param.is_ror_scan= FALSE; - param.prev_tables= param.read_tables= 0; - param.current_table= table->map; - param.using_real_indexes= FALSE; - param.real_keynr[0]= 0; - param.alloced_sel_args= 0; + if (create_key_parts_for_pseudo_indexes(¶m, used_fields)) + { + free_root(&alloc, MYF(0)); + goto free_alloc; + } - thd->no_errors=1; // Don't warn about NULL + param.prev_tables= param.read_tables= 0; + param.current_table= table->map; + param.using_real_indexes= FALSE; + param.real_keynr[0]= 0; + param.alloced_sel_args= 0; - SEL_TREE *tree; - SEL_ARG **key, **end; - uint idx= 0; - - tree= get_mm_tree(¶m, cond); + thd->no_errors=1; - if (!tree) - goto end; + tree= get_mm_tree(¶m, cond); + + if (!tree) + goto free_alloc; + for (key= tree->keys, end= key + param.keys; key != end; key++, idx++) + { + double rows; + if (*key) + { + rows= records_in_column_ranges(¶m, idx, *key); + if (rows != HA_POS_ERROR) + (*key)->field->cond_selectivity= rows/table_records; + } + } - for (key= tree->keys, end= key + param.keys; key != end; key++, idx++) - { - double rows; - if (*key) + for (Field **field_ptr= table->field; *field_ptr; field_ptr++) { - rows= records_in_column_ranges(¶m, idx, *key); - if (rows != HA_POS_ERROR) - (*key)->field->cond_selectivity= rows/table_records; + Field *table_field= *field_ptr; + if (bitmap_is_set(table->read_set, table_field->field_index) && + table_field->cond_selectivity < 1.0) + table->cond_selectivity*= table_field->cond_selectivity; } - } - for (Field **field_ptr= table->field; *field_ptr; field_ptr++) - { - Field *table_field= *field_ptr; - if (bitmap_is_set(table->read_set, table_field->field_index) && - table_field->cond_selectivity < 1.0) - table->cond_selectivity*= table_field->cond_selectivity; + free_alloc: + thd->mem_root= param.old_root; + free_root(&alloc, MYF(0)); + } /* Calculate the selectivity of the range conditions supported by indexes */ @@ -3412,17 +3416,18 @@ bool calculate_cond_selectivity_for_table(THD *thd, TABLE *table, Item *cond) } if (i) { - double f1= key_info->actual_rec_per_key(i-1); - double f2= key_info->actual_rec_per_key(i); - table->cond_selectivity*= quick_cond_selectivity * f1 / f2; + table->cond_selectivity*= quick_cond_selectivity; + if (i != used_key_parts) + { + double f1= key_info->actual_rec_per_key(i-1); + double f2= key_info->actual_rec_per_key(i); + table->cond_selectivity*= f1 / f2; + } } } } } -end: - thd->mem_root= param.old_root; - free_root(&alloc, MYF(0)); DBUG_RETURN(FALSE); } diff --git a/sql/sql_class.h b/sql/sql_class.h index e2fe3a53dcc..2844cab9b4e 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -501,6 +501,7 @@ typedef struct system_variables ulong optimizer_search_depth; ulong optimizer_use_condition_selectivity; ulong use_stat_tables; + ulong histogram_size; ulong preload_buff_size; ulong profiling_history_size; ulong read_buff_size; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bc9e05fe2d9..a6d995434af 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -6988,8 +6988,10 @@ double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, /* Discount the selectivity of the access method used to join table s */ if (s->quick && s->quick->index != MAX_KEY) { - /* A range scan by index s->quick->index is used to access table s */ - sel*= table_records/table->quick_rows[s->quick->index]; + if (join->positions[idx].key == 0) + { + sel*= table->quick_rows[s->quick->index]/table_records; + } } else if (ref) { diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc index 5338632067a..8c0b2730b02 100644 --- a/sql/sql_statistics.cc +++ b/sql/sql_statistics.cc @@ -889,7 +889,7 @@ public: char buff[MAX_FIELD_WIDTH]; String val(buff, sizeof(buff), &my_charset_utf8_bin); - for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_AVG_FREQUENCY; i++) + for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_HISTOGRAM; i++) { Field *stat_field= stat_table->field[i]; if (table_field->collected_stats->is_null(i)) @@ -924,7 +924,17 @@ public: break; case COLUMN_STAT_AVG_FREQUENCY: stat_field->store(table_field->collected_stats->get_avg_frequency()); - break; + break; + case COLUMN_STAT_HIST_SIZE: + stat_field->store(table_field->collected_stats->histogram.get_size()); + break; + case COLUMN_STAT_HISTOGRAM: + const char * col_histogram= + (const char *) (table_field->collected_stats->histogram.get_values()); + stat_field->store(col_histogram, + table_field->collected_stats->histogram.get_size(), + &my_charset_bin); + break; } } } @@ -961,7 +971,7 @@ public: char buff[MAX_FIELD_WIDTH]; String val(buff, sizeof(buff), &my_charset_utf8_bin); - for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_AVG_FREQUENCY; i++) + for (uint i= COLUMN_STAT_MIN_VALUE; i <= COLUMN_STAT_HIST_SIZE; i++) { Field *stat_field= stat_table->field[i]; @@ -993,6 +1003,9 @@ public: break; case COLUMN_STAT_AVG_FREQUENCY: table_field->read_stats->set_avg_frequency(stat_field->val_real()); + break; + case COLUMN_STAT_HIST_SIZE: + table_field->read_stats->histogram.set_size(stat_field->val_int()); break; } } @@ -1000,6 +1013,21 @@ public: } } + void get_histogram_value() + { + if (find_stat()) + { + char buff[MAX_FIELD_WIDTH]; + String val(buff, sizeof(buff), &my_charset_utf8_bin); + uint fldno= COLUMN_STAT_HISTOGRAM; + Field *stat_field= stat_table->field[fldno]; + table_field->read_stats->set_not_null(fldno); + stat_field->val_str(&val); + memcpy(table_field->read_stats->histogram.get_values(), + val.ptr(), table_field->read_stats->histogram.get_size()); + } + } + }; @@ -1202,6 +1230,72 @@ public: }; +class Histogram_builder +{ + Field *column; + uint col_length; + ha_rows records; + Field *min_value; + Field *max_value; + Histogram *histogram; + uint hist_size; + double bucket_capacity; + uint curr_bucket; + ulonglong count; + ulonglong count_distinct; + +public: + Histogram_builder(Field *col, uint col_len, ha_rows rows) + : column(col), col_length(col_len), records(rows) + { + Column_statistics *col_stats= col->collected_stats; + min_value= col_stats->min_value; + max_value= col_stats->max_value; + histogram= &col_stats->histogram; + hist_size= histogram->get_size(); + bucket_capacity= (double) records / (hist_size + 1); + curr_bucket= 0; + count= 0; + count_distinct= 0; + } + + ulonglong get_count_distinct() { return count_distinct; } + + int next(void *elem, element_count elem_cnt) + { + count_distinct++; + count+= elem_cnt; + if (curr_bucket == hist_size) + return 0; + if (count > bucket_capacity * (curr_bucket + 1)) + { + column->store_field_value((uchar *) elem, col_length); + histogram->set_value(curr_bucket, + column->middle_point_pos(min_value, max_value)); + curr_bucket++; + while (curr_bucket != hist_size && + count > bucket_capacity * (curr_bucket + 1)) + { + histogram->set_prev_value(curr_bucket); + curr_bucket++; + } + } + return 0; + } +}; + + +C_MODE_START + +int histogram_build_walk(void *elem, element_count elem_cnt, void *arg) +{ + Histogram_builder *hist_builder= (Histogram_builder *) arg; + return hist_builder->next(elem, elem_cnt); +} + +C_MODE_END + + /* The class Count_distinct_field is a helper class used to calculate the number of distinct values for a column. The class employs the @@ -1221,6 +1315,8 @@ protected: uint tree_key_length; /* The length of the keys for the elements of 'tree */ public: + + Count_distinct_field() {} /** @param @@ -1239,27 +1335,10 @@ public: Count_distinct_field(Field *field, uint max_heap_table_size) { - qsort_cmp2 compare_key; - void* cmp_arg; - enum enum_field_types f_type= field->type(); - table_field= field; tree_key_length= field->pack_length(); - if ((f_type == MYSQL_TYPE_VARCHAR) || - (!field->binary() && (f_type == MYSQL_TYPE_STRING || - f_type == MYSQL_TYPE_VAR_STRING))) - { - compare_key= (qsort_cmp2) simple_str_key_cmp; - cmp_arg= (void*) field; - } - else - { - cmp_arg= (void*) &tree_key_length; - compare_key= (qsort_cmp2) simple_raw_key_cmp; - } - - tree= new Unique(compare_key, cmp_arg, + tree= new Unique((qsort_cmp2) simple_str_key_cmp, (void*) field, tree_key_length, max_heap_table_size); } @@ -1300,9 +1379,36 @@ public: tree->walk(table_field->table, count_distinct_walk, (void*) &count); return count; } + + ulonglong get_value_with_histogram(ha_rows rows) + { + Histogram_builder hist_builder(table_field, tree_key_length, rows); + tree->walk(table_field->table, histogram_build_walk, (void *) &hist_builder); + return hist_builder.get_count_distinct(); + } + + uint get_hist_size() + { + return table_field->collected_stats->histogram.get_size(); + } + + uchar *get_histogram() + { + return table_field->collected_stats->histogram.get_values(); + } + }; +static +int simple_ulonglong_key_cmp(void* arg, uchar* key1, uchar* key2) +{ + ulonglong *val1= (ulonglong *) key1; + ulonglong *val2= (ulonglong *) key2; + return *val1 > *val2 ? 1 : *val1 == *val2 ? 0 : -1; +} + + /* The class Count_distinct_field_bit is derived from the class Count_distinct_field to be used only for fields of the MYSQL_TYPE_BIT type. @@ -1312,8 +1418,17 @@ public: class Count_distinct_field_bit: public Count_distinct_field { public: + Count_distinct_field_bit(Field *field, uint max_heap_table_size) - :Count_distinct_field(field, max_heap_table_size) {} + { + table_field= field; + tree_key_length= sizeof(ulonglong); + + tree= new Unique((qsort_cmp2) simple_ulonglong_key_cmp, + (void*) &tree_key_length, + tree_key_length, max_heap_table_size); + } + bool add() { longlong val= table_field->val_int(); @@ -1672,13 +1787,26 @@ int alloc_statistics_for_table(THD* thd, TABLE *table) ulong *idx_avg_frequency= (ulong*) alloc_root(&table->mem_root, sizeof(ulong) * key_parts); - if (!table_stats || !column_stats || !index_stats || !idx_avg_frequency) + uint columns= 0; + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + if (bitmap_is_set(table->read_set, (*field_ptr)->field_index)) + columns++; + } + uint hist_size= thd->variables.histogram_size; + uchar *histogram= NULL; + if (hist_size > 0) + histogram= (uchar *) alloc_root(&table->mem_root, hist_size * columns); + + if (!table_stats || !column_stats || !index_stats || !idx_avg_frequency || + (hist_size && !histogram)) DBUG_RETURN(1); table->collected_stats= table_stats; table_stats->column_stats= column_stats; table_stats->index_stats= index_stats; table_stats->idx_avg_frequency= idx_avg_frequency; + table_stats->histograms= histogram; memset(column_stats, 0, sizeof(Column_statistics) * (fields+1)); @@ -1687,6 +1815,12 @@ int alloc_statistics_for_table(THD* thd, TABLE *table) (*field_ptr)->collected_stats= column_stats; (*field_ptr)->collected_stats->max_value= NULL; (*field_ptr)->collected_stats->min_value= NULL; + if (bitmap_is_set(table->read_set, (*field_ptr)->field_index)) + { + column_stats->histogram.set_size(hist_size); + column_stats->histogram.set_values(histogram); + histogram+= hist_size; + } } memset(idx_avg_frequency, 0, sizeof(ulong) * key_parts); @@ -1903,10 +2037,51 @@ int alloc_statistics_for_table_share(THD* thd, TABLE_SHARE *table_share, if (!is_safe) mysql_mutex_unlock(&table_share->LOCK_ha_data); - DBUG_RETURN(0); } +static +int alloc_histograms_for_table_share(THD* thd, TABLE_SHARE *table_share, + bool is_safe) +{ + TABLE_STATISTICS_CB *stats_cb= &table_share->stats_cb; + + DBUG_ENTER("alloc_histograms_for_table_share"); + + if (!is_safe) + mysql_mutex_lock(&table_share->LOCK_ha_data); + + if (stats_cb->histograms_can_be_read) + { + if (!is_safe) + mysql_mutex_unlock(&table_share->LOCK_ha_data); + DBUG_RETURN(0); + } + + Table_statistics *table_stats= stats_cb->table_stats; + ulong total_hist_size= table_stats->total_hist_size; + + if (total_hist_size && !table_stats->histograms) + { + uchar *histograms= (uchar *) alloc_root(&stats_cb->mem_root, + total_hist_size); + if (!histograms) + { + if (!is_safe) + mysql_mutex_unlock(&table_share->LOCK_ha_data); + DBUG_RETURN(1); + } + memset(histograms, 0, total_hist_size); + table_stats->histograms= histograms; + stats_cb->histograms_can_be_read= TRUE; + } + + if (!is_safe) + mysql_mutex_unlock(&table_share->LOCK_ha_data); + + DBUG_RETURN(0); + +} /** @brief @@ -2006,14 +2181,28 @@ void Column_statistics_collected::finish(ha_rows rows) set_not_null(COLUMN_STAT_AVG_LENGTH); } if (count_distinct) - { - ulonglong distincts= count_distinct->get_value(); + { + ulonglong distincts; + uint hist_size= count_distinct->get_hist_size(); + if (hist_size == 0) + distincts= count_distinct->get_value(); + else + distincts= count_distinct->get_value_with_histogram(rows - nulls); if (distincts) { val= (double) (rows - nulls) / distincts; set_avg_frequency(val); set_not_null(COLUMN_STAT_AVG_FREQUENCY); } + else + hist_size= 0; + histogram.set_size(hist_size); + set_not_null(COLUMN_STAT_HIST_SIZE); + if (hist_size && distincts) + { + histogram.set_values(count_distinct->get_histogram()); + set_not_null(COLUMN_STAT_HISTOGRAM); + } delete count_distinct; count_distinct= NULL; } @@ -2234,16 +2423,19 @@ int collect_statistics_for_table(THD *thd, TABLE *table) table->collected_stats->cardinality= rows; } + bitmap_clear_all(table->write_set); for (field_ptr= table->field; *field_ptr; field_ptr++) { table_field= *field_ptr; if (!bitmap_is_set(table->read_set, table_field->field_index)) continue; + bitmap_set_bit(table->write_set, table_field->field_index); if (!rc) table_field->collected_stats->finish(rows); else table_field->collected_stats->cleanup(); } +bitmap_clear_all(table->write_set); if (!rc) { @@ -2421,6 +2613,7 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) Field **field_ptr; KEY *key_info, *key_info_end; TABLE_SHARE *table_share= table->s; + Table_statistics *read_stats= table_share->stats_cb.table_stats; DBUG_ENTER("read_statistics_for_table"); @@ -2432,16 +2625,18 @@ int read_statistics_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) /* Read statistics from the statistical table column_stats */ stat_table= stat_tables[COLUMN_STAT].table; + ulong total_hist_size= 0; Column_stat column_stat(stat_table, table); for (field_ptr= table_share->field; *field_ptr; field_ptr++) { table_field= *field_ptr; column_stat.set_key_fields(table_field); column_stat.get_stat_values(); + total_hist_size+= table_field->read_stats->histogram.get_size(); } + read_stats->total_hist_size= total_hist_size; /* Read statistics from the statistical table index_stats */ - Table_statistics *read_stats= table_share->stats_cb.table_stats; stat_table= stat_tables[INDEX_STAT].table; Index_stat index_stat(stat_table, table); for (key_info= table_share->key_info, @@ -2559,10 +2754,14 @@ bool statistics_for_tables_is_needed(THD *thd, TABLE_LIST *tables) TABLE_SHARE *table_share= tl->table->s; if (table_share && table_share->stats_cb.stats_can_be_read && - !table_share->stats_cb.stats_is_read) + (!table_share->stats_cb.stats_is_read || + (!table_share->stats_cb.histograms_are_read && + thd->variables.optimizer_use_condition_selectivity > 3))) return TRUE; if (table_share->stats_cb.stats_is_read) tl->table->stats_is_read= TRUE; + if (table_share->stats_cb.histograms_are_read) + tl->table->histograms_are_read= TRUE; } } @@ -2570,6 +2769,41 @@ bool statistics_for_tables_is_needed(THD *thd, TABLE_LIST *tables) } +static +int read_histograms_for_table(THD *thd, TABLE *table, TABLE_LIST *stat_tables) +{ + TABLE_SHARE *table_share= table->s; + + DBUG_ENTER("read_histograms_for_table"); + + if (!table_share->stats_cb.histograms_can_be_read) + { + (void) alloc_histograms_for_table_share(thd, table_share, FALSE); + } + if (table_share->stats_cb.histograms_can_be_read && + !table_share->stats_cb.histograms_are_read) + { + Field **field_ptr; + uchar *histogram= table_share->stats_cb.table_stats->histograms; + TABLE *stat_table= stat_tables[COLUMN_STAT].table; + Column_stat column_stat(stat_table, table); + for (field_ptr= table_share->field; *field_ptr; field_ptr++) + { + Field *table_field= *field_ptr; + uint hist_size= table_field->read_stats->histogram.get_size(); + if (hist_size) + { + column_stat.set_key_fields(table_field); + table_field->read_stats->histogram.set_values(histogram); + column_stat.get_histogram_value(); + histogram+= hist_size; + } + } + } + + DBUG_RETURN(0); +} + /** @brief Read statistics for tables from a table list if it is needed @@ -2597,7 +2831,7 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables) TABLE_LIST stat_tables[STATISTICS_TABLES]; Open_tables_backup open_tables_backup; - DBUG_ENTER("read_statistics_for_table_if_needed"); + DBUG_ENTER("read_statistics_for_tables_if_needed"); DEBUG_SYNC(thd, "statistics_read_start"); @@ -2624,6 +2858,14 @@ int read_statistics_for_tables_if_needed(THD *thd, TABLE_LIST *tables) } if (table_share->stats_cb.stats_is_read) tl->table->stats_is_read= TRUE; + if (thd->variables.optimizer_use_condition_selectivity > 3 && + table_share && !table_share->stats_cb.histograms_are_read) + { + (void) read_histograms_for_table(thd, tl->table, stat_tables); + table_share->stats_cb.histograms_are_read= TRUE; + } + if (table_share->stats_cb.stats_is_read) + tl->table->histograms_are_read= TRUE; } } @@ -3083,20 +3325,40 @@ double get_column_range_cardinality(Field *field, res= table->stat_records(); else if (min_endp->length == max_endp->length && !memcmp(min_endp->key, max_endp->key, min_endp->length)) - { - res= col_stats->get_avg_frequency(); + { + double avg_frequency= col_stats->get_avg_frequency(); + res= avg_frequency; + if (avg_frequency > 1.0 + 0.000001 && + col_stats->min_value && col_stats->max_value) + { + Histogram *hist= &col_stats->histogram; + if (hist->get_size() > 0) + { + double pos= field->middle_point_pos(col_stats->min_value, + col_stats->max_value); + res= table->stat_records() * + hist->point_selectivity(pos, + avg_frequency / table->stat_records()); + } + } } else { if (col_stats->min_value && col_stats->max_value) { + double sel; store_key_image_to_rec(field, (uchar *) min_endp->key, min_endp->length); double min_mp_pos= field->middle_point_pos(col_stats->min_value, col_stats->max_value); store_key_image_to_rec(field, (uchar *) max_endp->key, max_endp->length); double max_mp_pos= field->middle_point_pos(col_stats->min_value, col_stats->max_value); - res= table->stat_records() * (max_mp_pos - min_mp_pos); + Histogram *hist= &col_stats->histogram; + if (hist->get_size() == 0) + sel= (max_mp_pos - min_mp_pos); + else + sel= hist->range_selectivity(min_mp_pos, max_mp_pos); + res= table->stat_records() * sel; } else res= table->stat_records(); diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h index 6bf552b92a0..9a2b5c2433b 100644 --- a/sql/sql_statistics.h +++ b/sql/sql_statistics.h @@ -16,15 +16,6 @@ #ifndef SQL_STATISTICS_H #define SQL_STATISTICS_H -/* - These enumeration types comprise the dictionary of three - statistical tables table_stat, column_stat and index_stat - as they defined in ../scripts/mysql_system_tables.sql. - - It would be nice if the declarations of these types were - generated automatically by the table definitions. -*/ - typedef enum enum_use_stat_tables_mode { @@ -40,6 +31,16 @@ enum enum_stat_tables INDEX_STAT, }; + +/* + These enumeration types comprise the dictionary of three + statistical tables table_stat, column_stat and index_stat + as they defined in ../scripts/mysql_system_tables.sql. + + It would be nice if the declarations of these types were + generated automatically by the table definitions. +*/ + enum enum_table_stat_col { TABLE_STAT_DB_NAME, @@ -56,7 +57,9 @@ enum enum_column_stat_col COLUMN_STAT_MAX_VALUE, COLUMN_STAT_NULLS_RATIO, COLUMN_STAT_AVG_LENGTH, - COLUMN_STAT_AVG_FREQUENCY + COLUMN_STAT_AVG_FREQUENCY, + COLUMN_STAT_HIST_SIZE, + COLUMN_STAT_HISTOGRAM }; enum enum_index_stat_col @@ -96,6 +99,98 @@ double get_column_range_cardinality(Field *field, key_range *min_endp, key_range *max_endp); +#define HIST_FACTOR 255 +#define INV_HIST_FACTOR ((double) 1.0 / HIST_FACTOR) + +class Histogram +{ +private: +public: + +private: + uint8 size; + uint8 *values; + + uint find_bucket(double pos, bool first) + { + uint8 val= (uint8) (pos * HIST_FACTOR); + int lp= 0; + int rp= size - 1; + int i= 0; + for (int d= size / 2 ; d; d= (rp - lp) / 2) + { + i= lp + d; + if (val == values[i]) + break; + if (val < values[i]) + rp= i; + else if (val > values[i + 1]) + lp= i + 1; + else + break; + } + if (val == values[i]) + { + if (first) + { + while(i && val == values[i - 1]) + i--; + } + else + { + while(i + 1 < size && val == values[i + 1]) + i++; + } + } + return i; + } + +public: + + uint get_size() { return (uint) size; } + + uchar *get_values() { return (uchar *) values; } + + void set_size (ulonglong sz) { size= (uint8) sz; } + + void set_values (uchar *vals) { values= (uint8 *) vals; } + + void set_value(uint i, double val) + { + values[i]= (uint8) (val * HIST_FACTOR); + } + + void set_prev_value(uint i) { values[i]= values[i-1]; } + + + double range_selectivity(double min_pos, double max_pos) + { + double sel; + double bucket_sel= 1.0/(size + 1); + uint min= find_bucket(min_pos, TRUE); + uint max= find_bucket(max_pos, FALSE); + sel= bucket_sel * (max - min + 1); + return sel; + } + + double point_selectivity(double pos, double avg_sel) + { + double sel; + double bucket_sel= 1.0/(size + 1); + uint min= find_bucket(pos, TRUE); + uint max= min; + while (max + 1 < size && values[max + 1] == values[max]) + max++; + double width= ((max + 1 == size ? 1.0 : values[max]) - + (min == 0 ? 0.0 : values[min-1])) * + INV_HIST_FACTOR; + sel= avg_sel * (bucket_sel * (max + 1 - min)) / width; + return sel; + } + +}; + + class Columns_statistics; class Index_statistics; @@ -111,8 +206,9 @@ public: uchar *min_max_record_buffers; /* Record buffers for min/max values */ Column_statistics *column_stats; /* Array of statistical data for columns */ Index_statistics *index_stats; /* Array of statistical data for indexes */ - ulong *idx_avg_frequency; /* Array of records per key for index prefixes */ - + ulong *idx_avg_frequency; /* Array of records per key for index prefixes */ + ulong total_hist_size; + uchar *histograms; /* Sequence of histograms */ }; @@ -167,10 +263,12 @@ private: public: + Histogram histogram; + void set_all_nulls() { column_stat_nulls= - ((1 << (COLUMN_STAT_AVG_FREQUENCY-COLUMN_STAT_COLUMN_NAME))-1) << + ((1 << (COLUMN_STAT_HISTOGRAM-COLUMN_STAT_COLUMN_NAME))-1) << (COLUMN_STAT_COLUMN_NAME+1); } diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 7f6a00f3c62..2bf9a55f8b1 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -1431,14 +1431,17 @@ static Sys_var_ulong Sys_optimizer_use_condition_selectivity( "for the best execution plan " "Meaning: " "1 - use selectivity of index backed range conditions to calculate " - "cardinality of the partial join if the last joined table is " - "accessed by full table scan or an index scan " + "the cardinality of a partial join if the last joined table is " + "accessed by full table scan or an index scan, " "2 - use selectivity of index backed range conditions to calculate " - "cardinality of the partial join in any case " + "the cardinality of a partial join in any case, " "3 - additionally always use selectivity of range conditions that are " - "not backed by any index to calculate cardinality of the partial join", + "not backed by any index to calculate the cardinality of a partial join, " + "4 - use histograms to calculate selectivity of range conditions that " + "are not backed by any index to calculate the cardinality of " + "a partial join.", SESSION_VAR(optimizer_use_condition_selectivity), CMD_LINE(REQUIRED_ARG), - VALID_RANGE(1, 3), DEFAULT(1), BLOCK_SIZE(1)); + VALID_RANGE(1, 4), DEFAULT(1), BLOCK_SIZE(1)); /** Warns about deprecated value 63 */ static bool fix_optimizer_search_depth(sys_var *self, THD *thd, @@ -3767,6 +3770,13 @@ static Sys_var_enum Sys_optimizer_use_stat_tables( SESSION_VAR(use_stat_tables), CMD_LINE(REQUIRED_ARG), use_stat_tables_modes, DEFAULT(0)); +static Sys_var_ulong Sys_histogram_size( + "histogram_size", + "Number of bytes used for a histogram. " + "If set to 0, no histograms are created by ANALYZE.", + SESSION_VAR(histogram_size), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, 255), DEFAULT(0), BLOCK_SIZE(1)); + static Sys_var_mybool Sys_no_thread_alarm( "debug_no_thread_alarm", "Disable system thread alarm calls. Disabling it may be useful " diff --git a/sql/table.cc b/sql/table.cc index e4dc2adef64..eb693170cb2 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -432,6 +432,8 @@ void TABLE_SHARE::destroy() free_root(&stats_cb.mem_root, MYF(0)); stats_cb.stats_can_be_read= FALSE; stats_cb.stats_is_read= FALSE; + stats_cb.histograms_can_be_read= FALSE; + stats_cb.histograms_are_read= FALSE; if (tmp_table == NO_TMP_TABLE) mysql_mutex_unlock(&LOCK_ha_data); diff --git a/sql/table.h b/sql/table.h index 85b31af8a77..62daa46c9d3 100644 --- a/sql/table.h +++ b/sql/table.h @@ -573,7 +573,9 @@ struct TABLE_STATISTICS_CB Table_statistics *table_stats; /* Structure to access the statistical data */ bool stats_can_be_read; /* Memory for statistical data is allocated */ bool stats_is_read; /* Statistical data for table has been read - from statistical tables */ + from statistical tables */ + bool histograms_can_be_read; + bool histograms_are_read; }; @@ -1200,6 +1202,7 @@ public: #endif uint max_keys; /* Size of allocated key_info array. */ bool stats_is_read; /* Persistent statistics is read for the table */ + bool histograms_are_read; MDL_ticket *mdl_ticket; void init(THD *thd, TABLE_LIST *tl); |