diff options
53 files changed, 3608 insertions, 21 deletions
diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt index 6603983e69b..9be06ec8397 100644 --- a/libmysqld/CMakeLists.txt +++ b/libmysqld/CMakeLists.txt @@ -73,7 +73,8 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc ../sql/debug_sync.cc ../sql/opt_table_elimination.cc ../sql/sql_prepare.cc ../sql/sql_rename.cc ../sql/sql_repl.cc ../sql/sql_select.cc ../sql/sql_servers.cc - ../sql/sql_show.cc ../sql/sql_state.c ../sql/sql_string.cc + ../sql/sql_show.cc ../sql/sql_state.c + ../sql/sql_statistics.cc ../sql/sql_string.cc ../sql/sql_tablespace.cc ../sql/sql_table.cc ../sql/sql_test.cc ../sql/sql_trigger.cc ../sql/sql_udf.cc ../sql/sql_union.cc ../sql/sql_update.cc ../sql/sql_view.cc ../sql/sql_profile.cc diff --git a/mysql-test/include/system_db_struct.inc b/mysql-test/include/system_db_struct.inc index 123c82484b9..3d97ff67043 100644 --- a/mysql-test/include/system_db_struct.inc +++ b/mysql-test/include/system_db_struct.inc @@ -16,3 +16,6 @@ show create table proc; show create table event; show create table general_log; show create table slow_log; +show create table table_stat; +show create table column_stat; +show create table index_stat; diff --git a/mysql-test/include/world_schema_utf8.inc b/mysql-test/include/world_schema_utf8.inc new file mode 100644 index 00000000000..2a09c5d32d5 --- /dev/null +++ b/mysql-test/include/world_schema_utf8.inc @@ -0,0 +1,25 @@ +CREATE TABLE Country ( + Code char(3) NOT NULL default '', + Name char(52) NOT NULL default '', + SurfaceArea float(10,2) NOT NULL default '0.00', + Population int(11) NOT NULL default '0', + Capital int(11) default NULL, + PRIMARY KEY (Code), + UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( + ID int(11) NOT NULL auto_increment, + Name char(35) NOT NULL default '', + Country char(3) NOT NULL default '', + Population int(11) NOT NULL default '0', + PRIMARY KEY (ID), + INDEX (Population), + INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( + Country char(3) NOT NULL default '', + Language char(30) NOT NULL default '', + Percentage float(3,1) NOT NULL default '0.0', + PRIMARY KEY (Country, Language), + INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; diff --git a/mysql-test/r/1st.result b/mysql-test/r/1st.result index 792d9eaf2f1..9c97cd3d2c3 100644 --- a/mysql-test/r/1st.result +++ b/mysql-test/r/1st.result @@ -7,6 +7,7 @@ performance_schema test show tables in mysql; Tables_in_mysql +column_stat columns_priv db event @@ -17,6 +18,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -24,6 +26,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index a43a4d383ac..dd31120f5a6 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -1,6 +1,7 @@ drop table if exists t1,t2; show tables; Tables_in_mysql +column_stat columns_priv db event @@ -11,6 +12,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -18,6 +20,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -35,6 +38,7 @@ grant ALL on *.* to test@localhost identified by "gambling"; grant ALL on *.* to test@127.0.0.1 identified by "gambling"; show tables; Tables_in_mysql +column_stat columns_priv db event @@ -45,6 +49,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -52,6 +57,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -77,6 +83,7 @@ ERROR HY000: Password hash should be a 41-digit hexadecimal number set password=old_password('gambling3'); show tables; Tables_in_mysql +column_stat columns_priv db event @@ -87,6 +94,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -94,6 +102,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index fcb40dae4ff..85729a3ea87 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -81,6 +81,7 @@ TRIGGERS USER_PRIVILEGES USER_STATISTICS VIEWS +column_stat columns_priv db event @@ -91,6 +92,7 @@ help_keyword help_relation help_topic host +index_stat plugin proc procs_priv @@ -102,6 +104,7 @@ t2 t3 t4 t5 +table_stat tables_priv time_zone time_zone_leap_second @@ -120,6 +123,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -141,6 +145,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second @@ -162,6 +167,7 @@ TABLE_CONSTRAINTS TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLE_PRIVILEGES TABLE_STATISTICS TABLE_STATISTICS TRIGGERS TRIGGERS +table_stat table_stat tables_priv tables_priv time_zone time_zone time_zone_leap_second time_zone_leap_second diff --git a/mysql-test/r/information_schema_all_engines.result b/mysql-test/r/information_schema_all_engines.result index 9304febcc5a..d9f437a29f3 100644 --- a/mysql-test/r/information_schema_all_engines.result +++ b/mysql-test/r/information_schema_all_engines.result @@ -310,4 +310,4 @@ Wildcard: inf_rmation_schema SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; table_schema count(*) information_schema 40 -mysql 23 +mysql 26 diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index 5ed59eecc31..09dd996aa54 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -19,6 +19,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -28,6 +29,7 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK @@ -35,6 +37,7 @@ mysql.procs_priv OK mysql.proxies_priv OK mysql.renamed_general_log OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 247c2b80d62..e1d1153a47a 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -7,6 +7,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -16,12 +17,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -44,6 +47,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -53,12 +57,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -81,6 +87,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -90,12 +97,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -121,6 +130,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -130,12 +140,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -164,6 +176,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -173,12 +186,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -210,6 +225,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -219,12 +235,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -259,6 +277,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -268,12 +287,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index a08e7c115cc..40a20e38a1d 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -9,6 +9,7 @@ mtr mtr.global_suppressions OK mtr.test_suppressions OK mysql +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -18,12 +19,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index ab707ceef80..288dc5bd213 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -3,6 +3,7 @@ drop view if exists v1; drop database if exists client_test_db; mtr.global_suppressions OK mtr.test_suppressions OK +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -12,12 +13,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -27,6 +30,7 @@ mysql.time_zone_transition_type OK mysql.user OK mtr.global_suppressions Table is already up to date mtr.test_suppressions Table is already up to date +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -36,12 +40,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -49,6 +55,7 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK +mysql.column_stat OK mysql.columns_priv OK mysql.db OK mysql.event OK @@ -58,12 +65,14 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.index_stat OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.table_stat OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -71,6 +80,7 @@ mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK +mysql.column_stat Table is already up to date mysql.columns_priv Table is already up to date mysql.db Table is already up to date mysql.event Table is already up to date @@ -80,12 +90,14 @@ mysql.help_keyword Table is already up to date mysql.help_relation Table is already up to date mysql.help_topic Table is already up to date mysql.host Table is already up to date +mysql.index_stat Table is already up to date mysql.ndb_binlog_index Table is already up to date mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.servers Table is already up to date +mysql.table_stat Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date mysql.time_zone_leap_second Table is already up to date diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index f9b64e88f68..b1c15bd00b2 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -493,6 +493,9 @@ The following options may be given as the first argument: partial_match_table_scan, semijoin, semijoin_with_cache, subquery_cache, table_elimination, extended_keys } and val is one of {on, off, default} + --optimizer-use-stat-tables=name + Specifies how to use system statistics tables. Possible + values are NEVER, COMPLEMENTARY, PREVERABLY, EXCLUSIVELY --performance-schema Enable the performance schema. --performance-schema-events-waits-history-long-size=# @@ -717,6 +720,9 @@ The following options may be given as the first argument: --skip-show-database Don't allow 'SHOW DATABASE' commands --skip-slave-start If set, slave is not autostarted. + --skip-stat-tables Start without statistical tables. Statistical data on + table cardinalities, columns and indexes from these + tables become unavailable --skip-thread-priority Don't give threads different priorities. This option is deprecated because it has no effect; the implied behavior @@ -990,6 +996,7 @@ old-style-user-limits FALSE optimizer-prune-level 1 optimizer-search-depth 62 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on +optimizer-use-stat-tables NEVER performance-schema FALSE performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 @@ -1050,6 +1057,7 @@ skip-name-resolve FALSE skip-networking FALSE skip-show-database FALSE skip-slave-start FALSE +skip-stat-tables TRUE slave-compressed-protocol FALSE slave-exec-mode STRICT slave-net-timeout 3600 diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result new file mode 100644 index 00000000000..3a83f9ba66c --- /dev/null +++ b/mysql-test/r/statistics.result @@ -0,0 +1,467 @@ +drop table if exists t1,t2; +set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables; +CREATE VIEW table_stat AS +SELECT * FROM mysql.table_stat; +CREATE VIEW column_stat AS +SELECT db_name, table_name, column_name, +min_value, max_value, +CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio', +CAST(avg_length AS decimal(12,4)) AS 'avg_length', +CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.column_stat; +CREATE VIEW index_stat AS +SELECT db_name, table_name, index_name, prefix_arity, +CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.index_stat; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +set optimizer_use_stat_tables='preferably'; +CREATE TABLE t1 ( +a int NOT NULL PRIMARY KEY, +b varchar(32), +c char(16), +d date, +e double, +f bit(3), +INDEX idx1 (b, e), +INDEX idx2(c, d), +INDEX idx3 (d), +INDEX idx4 (e, b, d) +); +INSERT INTO t1 VALUES +(0, NULL, NULL, NULL, NULL, NULL), +(7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'), +(17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'), +(1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'), +(12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'), +(23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'), +(8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'), +(22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'), +(31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'), +(10, NULL, 'aaaa', NULL, 0.01, b'010'), +(5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'), +(15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'), +(30, NULL, 'bbbbbb', NULL, NULL, b'100'), +(38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL), +(18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'), +(9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL), +(29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'), +(3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'), +(39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'), +(14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'), +(40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL), +(44, NULL, 'aaaa', '1989-03-12', NULL, b'010'), +(19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'), +(21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'), +(45, NULL, NULL, '1989-03-12', NULL, b'011'), +(2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'), +(35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'), +(4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'), +(47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'), +(42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'), +(32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'), +(49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL), +(43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'), +(37, 'yyy', NULL, '1989-03-12', 0.05, b'011'), +(41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL), +(34, 'yyy', NULL, NULL, NULL, NULL), +(33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'), +(24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'), +(11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL), +(25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT * FROM table_stat; +db_name table_name cardinality +test t1 40 +SELECT * FROM column_stat; +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 +SELECT * FROM index_stat; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t1 idx1 1 6.4000 +test t1 idx1 2 1.6875 +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t1 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t1 idx4 3 1.1304 +SELECT COUNT(*) FROM t1; +COUNT(*) +40 +SELECT * FROM column_stat +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 +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)", +(SELECT COUNT(t1.a) FROM t1) / +(SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)" +FROM t1; +MIN(t1.a) MAX(t1.a) NULLS_RATIO(t1.a) AVG_FREQUENCY(t1.a) +0 49 0.2000 1.0000 +SELECT * FROM column_stat +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 +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)", +(SELECT COUNT(t1.b) FROM t1) / +(SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)" +FROM t1; +MIN(t1.b) MAX(t1.b) NULLS_RATIO(t1.b) AVG_FREQUENCY(t1.b) +vvvvvvvvvvvvv zzzzzzzzzzzzzzzzzz 0.2000 6.4000 +SELECT * FROM column_stat +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 +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)", +(SELECT COUNT(t1.c) FROM t1) / +(SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)" +FROM t1; +MIN(t1.c) MAX(t1.c) NULLS_RATIO(t1.c) AVG_FREQUENCY(t1.c) +aaaa dddddddd 0.1250 7.0000 +SELECT * FROM column_stat +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 +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)", +(SELECT COUNT(t1.d) FROM t1) / +(SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)" +FROM t1; +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 column_stat +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 +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)", +(SELECT COUNT(t1.e) FROM t1) / +(SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)" +FROM t1; +MIN(t1.e) MAX(t1.e) NULLS_RATIO(t1.e) AVG_FREQUENCY(t1.e) +0.01 0.112 0.2250 6.2000 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx1'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx1 1 6.4000 +test t1 idx1 2 1.6875 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1 +WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) +AS 'ARITY 2'; +ARITY 1 ARITY 2 +6.4000 1.6875 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx2'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1 +WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) +AS 'ARITY 2'; +ARITY 1 ARITY 2 +7.0000 2.3846 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx3'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx3 1 8.5000 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL) +AS 'ARITY 1'; +ARITY 1 +8.5000 +SELECT * FROM index_stat +WHERE db_name='test' AND table_name='t1' AND index_name='idx4'; +db_name table_name index_name prefix_arity avg_frequency +test t1 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t1 idx4 3 1.1304 +SELECT +(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL) +AS 'ARITY 1', +(SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1 +WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) +AS 'ARITY 2', +(SELECT COUNT(*) FROM t1 +WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) / +(SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1 +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 +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; +ANALYZE TABLE t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +SELECT * FROM table_stat; +db_name table_name cardinality +test t1 40 +test t2 40 +SELECT * FROM column_stat 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 +SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t2 PRIMARY 1 1.0000 +test t1 idx1 1 6.4000 +test t2 idx1 1 6.4000 +test t1 idx1 2 1.6875 +test t2 idx1 2 1.6875 +test t1 idx2 1 7.0000 +test t2 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t2 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t2 idx3 1 8.5000 +test t1 idx4 1 6.2000 +test t2 idx4 1 6.2000 +test t1 idx4 2 1.6875 +test t2 idx4 2 1.6875 +test t1 idx4 3 1.1304 +test t2 idx4 3 1.1304 +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +ALTER TABLE t1 +DROP INDEX idx1, +DROP INDEX idx4; +ALTER TABLE t1 +MODIFY COLUMN b text, +ADD INDEX idx1 (b(4), e), +ADD INDEX idx4 (e, b(4), d); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +SELECT * FROM column_stat; +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 0.2000 17.1250 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 +SELECT * FROM index_stat; +db_name table_name index_name prefix_arity avg_frequency +test t1 PRIMARY 1 1.0000 +test t1 idx2 1 7.0000 +test t1 idx2 2 2.3846 +test t1 idx3 1 8.5000 +test t1 idx1 1 NULL +test t1 idx1 2 NULL +test t1 idx4 1 6.2000 +test t1 idx4 2 NULL +test t1 idx4 3 NULL +DROP TABLE t1,t2; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +set optimizer_use_stat_tables='never'; +set names utf8; +CREATE DATABASE world; +use world; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; +set optimizer_use_stat_tables='preferably'; +ANALYZE TABLE Country, City, CountryLanguage; +SELECT UPPER(db_name), UPPER(table_name), cardinality +FROM test.table_stat; +UPPER(db_name) UPPER(table_name) cardinality +WORLD COUNTRY 239 +WORLD CITY 4079 +WORLD COUNTRYLANGUAGE 984 +SELECT UPPER(db_name), UPPER(table_name), +column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency +FROM test.column_stat; +UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency +WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +SELECT UPPER(db_name), UPPER(table_name), +index_name, prefix_arity, avg_frequency +FROM test.index_stat; +UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency +WORLD COUNTRY PRIMARY 1 1.0000 +WORLD COUNTRY Name 1 1.0000 +WORLD CITY PRIMARY 1 1.0000 +WORLD CITY Population 1 1.0467 +WORLD CITY Country 1 17.5819 +WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD COUNTRYLANGUAGE Percentage 1 2.7640 +use test; +set optimizer_use_stat_tables='never'; +CREATE DATABASE world_innodb; +use world_innodb; +CREATE TABLE Country ( +Code char(3) NOT NULL default '', +Name char(52) NOT NULL default '', +SurfaceArea float(10,2) NOT NULL default '0.00', +Population int(11) NOT NULL default '0', +Capital int(11) default NULL, +PRIMARY KEY (Code), +UNIQUE INDEX (Name) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE City ( +ID int(11) NOT NULL auto_increment, +Name char(35) NOT NULL default '', +Country char(3) NOT NULL default '', +Population int(11) NOT NULL default '0', +PRIMARY KEY (ID), +INDEX (Population), +INDEX (Country) +) CHARACTER SET utf8 COLLATE utf8_bin; +CREATE TABLE CountryLanguage ( +Country char(3) NOT NULL default '', +Language char(30) NOT NULL default '', +Percentage float(3,1) NOT NULL default '0.0', +PRIMARY KEY (Country, Language), +INDEX (Percentage) +) CHARACTER SET utf8 COLLATE utf8_bin; +ALTER TABLE Country ENGINE=InnoDB; +ALTER TABLE City ENGINE=InnoDB; +ALTER TABLE CountryLanguage ENGINE=InnoDB; +set optimizer_use_stat_tables='preferably'; +ANALYZE TABLE Country, City, CountryLanguage; +SELECT UPPER(db_name), UPPER(table_name), cardinality +FROM test.table_stat; +UPPER(db_name) UPPER(table_name) cardinality +WORLD COUNTRY 239 +WORLD CITY 4079 +WORLD COUNTRYLANGUAGE 984 +WORLD_INNODB COUNTRY 239 +WORLD_INNODB CITY 4079 +WORLD_INNODB COUNTRYLANGUAGE 984 +SELECT UPPER(db_name), UPPER(table_name), +column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency +FROM test.column_stat; +UPPER(db_name) UPPER(table_name) column_name min_value max_value nulls_ratio avg_length avg_frequency +WORLD COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +WORLD_INNODB COUNTRY Code ABW ZWE 0.0000 3.0000 1.0000 +WORLD_INNODB COUNTRY Name Afghanistan Zimbabwe 0.0000 10.1088 1.0000 +WORLD_INNODB COUNTRY SurfaceArea 0.40 17075400.00 0.0000 4.0000 1.0042 +WORLD_INNODB COUNTRY Population 0 1277558000 0.0000 4.0000 1.0575 +WORLD_INNODB COUNTRY Capital 1 4074 0.0293 4.0000 1.0000 +WORLD_INNODB CITY ID 1 4079 0.0000 4.0000 1.0000 +WORLD_INNODB CITY Name A Coruña (La Coruña) Ürgenc 0.0000 8.6416 1.0195 +WORLD_INNODB CITY Country ABW ZWE 0.0000 3.0000 17.5819 +WORLD_INNODB CITY Population 42 10500000 0.0000 4.0000 1.0467 +WORLD_INNODB COUNTRYLANGUAGE Country ABW ZWE 0.0000 3.0000 4.2232 +WORLD_INNODB COUNTRYLANGUAGE Language Abhyasi [South]Mande 0.0000 7.1778 2.1532 +WORLD_INNODB COUNTRYLANGUAGE Percentage 0.0 99.9 0.0000 4.0000 2.7640 +SELECT UPPER(db_name), UPPER(table_name), +index_name, prefix_arity, avg_frequency +FROM test.index_stat; +UPPER(db_name) UPPER(table_name) index_name prefix_arity avg_frequency +WORLD COUNTRY PRIMARY 1 1.0000 +WORLD COUNTRY Name 1 1.0000 +WORLD CITY PRIMARY 1 1.0000 +WORLD CITY Population 1 1.0467 +WORLD CITY Country 1 17.5819 +WORLD COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD COUNTRYLANGUAGE Percentage 1 2.7640 +WORLD_INNODB COUNTRY PRIMARY 1 1.0000 +WORLD_INNODB COUNTRY Name 1 1.0000 +WORLD_INNODB CITY PRIMARY 1 1.0000 +WORLD_INNODB CITY Population 1 1.0467 +WORLD_INNODB CITY Country 1 17.5819 +WORLD_INNODB COUNTRYLANGUAGE PRIMARY 1 4.2232 +WORLD_INNODB COUNTRYLANGUAGE PRIMARY 2 1.0000 +WORLD_INNODB COUNTRYLANGUAGE Percentage 1 2.7640 +use test; +DROP DATABASE world; +DROP DATABASE world_innodb; +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; +DROP VIEW test.table_stat; +DROP VIEW test.column_stat; +DROP VIEW test.index_stat; +set optimizer_use_stat_tables=@save_optimizer_use_stat_tables; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double 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_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double 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_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double 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_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index d5409136ed4..985fbd0539d 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -1,5 +1,6 @@ show tables; Tables_in_db +column_stat columns_priv db event @@ -10,6 +11,7 @@ help_keyword help_relation help_topic host +index_stat ndb_binlog_index plugin proc @@ -17,6 +19,7 @@ procs_priv proxies_priv servers slow_log +table_stat tables_priv time_zone time_zone_leap_second @@ -264,5 +267,36 @@ slow_log CREATE TABLE `slow_log` ( `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' +show create table table_stat; +Table Create Table +table_stat CREATE TABLE `table_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `cardinality` bigint(21) unsigned DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Tables' +show create table column_stat; +Table Create Table +column_stat CREATE TABLE `column_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `column_name` varchar(64) COLLATE utf8_bin NOT NULL, + `min_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `max_value` varchar(255) COLLATE utf8_bin DEFAULT NULL, + `nulls_ratio` double DEFAULT NULL, + `avg_length` double DEFAULT NULL, + `avg_frequency` double 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_stat; +Table Create Table +index_stat CREATE TABLE `index_stat` ( + `db_name` varchar(64) COLLATE utf8_bin NOT NULL, + `table_name` varchar(64) COLLATE utf8_bin NOT NULL, + `index_name` varchar(64) COLLATE utf8_bin NOT NULL, + `prefix_arity` int(11) unsigned NOT NULL, + `avg_frequency` double DEFAULT NULL, + PRIMARY KEY (`db_name`,`table_name`,`index_name`,`prefix_arity`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Statistics on Indexes' show tables; Tables_in_test 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 4eff12dab7b..c83f80cd28f 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql.result @@ -9,6 +9,14 @@ def mysql columns_priv Host 1 NO char 60 180 NULL NULL NULL utf8 utf8_bin char( def mysql columns_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI select,insert,update,references def mysql columns_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references def mysql columns_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI select,insert,update,references +def mysql column_stat avg_frequency 8 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat avg_length 7 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat column_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql column_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql column_stat max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stat min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) select,insert,update,references +def mysql column_stat nulls_ratio 6 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql column_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql db Alter_routine_priv 19 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql db Create_priv 8 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references @@ -97,6 +105,11 @@ def mysql host Select_priv 3 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci e def mysql host Show_view_priv 16 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql host Trigger_priv 20 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references def mysql host Update_priv 5 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') select,insert,update,references +def mysql index_stat avg_frequency 5 NULL YES double NULL NULL 22 NULL NULL NULL NULL double select,insert,update,references +def mysql index_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql index_stat index_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql index_stat prefix_arity 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned PRI select,insert,update,references +def mysql index_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql ndb_binlog_index deletes 6 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned select,insert,update,references def mysql ndb_binlog_index epoch 3 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned PRI select,insert,update,references def mysql ndb_binlog_index File 2 NULL NO varchar 255 255 NULL NULL NULL latin1 latin1_swedish_ci varchar(255) select,insert,update,references @@ -169,6 +182,9 @@ def mysql tables_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin def mysql tables_priv Table_priv 7 NO set 98 294 NULL NULL NULL utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') select,insert,update,references def mysql tables_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP select,insert,update,references def mysql tables_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI select,insert,update,references +def mysql table_stat cardinality 3 NULL YES bigint NULL NULL 20 0 NULL NULL NULL bigint(21) unsigned select,insert,update,references +def mysql table_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references +def mysql table_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI select,insert,update,references def mysql time_zone Time_zone_id 1 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned PRI auto_increment select,insert,update,references def mysql time_zone Use_leap_seconds 2 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('Y','N') select,insert,update,references def mysql time_zone_leap_second Correction 2 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) select,insert,update,references @@ -258,6 +274,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 3.0000 char utf8 utf8_bin 3.0000 enum utf8 utf8_bin +3.0000 varchar utf8 utf8_bin 3.0000 char utf8 utf8_general_ci 3.0000 enum utf8 utf8_general_ci 3.0000 set utf8 utf8_general_ci @@ -274,6 +291,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL bigint NULL NULL NULL datetime NULL NULL +NULL double NULL NULL NULL int NULL NULL NULL smallint NULL NULL NULL time NULL NULL @@ -302,6 +320,14 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 mysql columns_priv Column_name char 64 192 utf8 utf8_bin char(64) NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql columns_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql column_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat column_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat min_value varchar 255 765 utf8 utf8_bin varchar(255) +3.0000 mysql column_stat max_value varchar 255 765 utf8 utf8_bin varchar(255) +NULL mysql column_stat nulls_ratio double NULL NULL NULL NULL double +NULL mysql column_stat avg_length double NULL NULL NULL NULL double +NULL mysql column_stat avg_frequency double NULL NULL NULL NULL double 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) @@ -390,6 +416,11 @@ NULL mysql help_topic help_category_id smallint NULL NULL NULL NULL smallint(5) 3.0000 mysql host Alter_routine_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Execute_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Trigger_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') +3.0000 mysql index_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat index_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql index_stat prefix_arity int NULL NULL NULL NULL int(11) unsigned +NULL mysql index_stat avg_frequency double NULL NULL NULL NULL double NULL mysql ndb_binlog_index Position bigint NULL NULL NULL NULL bigint(20) unsigned 1.0000 mysql ndb_binlog_index File varchar 255 255 latin1 latin1_swedish_ci varchar(255) NULL mysql ndb_binlog_index epoch bigint NULL NULL NULL NULL bigint(20) unsigned @@ -462,6 +493,9 @@ NULL mysql slow_log server_id int NULL NULL NULL NULL int(10) unsigned NULL mysql tables_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql tables_priv Table_priv set 98 294 utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') 3.0000 mysql tables_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql table_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql table_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql table_stat cardinality bigint NULL NULL NULL NULL bigint(21) unsigned NULL mysql time_zone Time_zone_id int NULL NULL NULL NULL int(10) unsigned 3.0000 mysql time_zone Use_leap_seconds enum 1 3 utf8 utf8_general_ci enum('Y','N') NULL mysql time_zone_leap_second Transition_time bigint NULL NULL NULL NULL bigint(20) diff --git a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result index d99e7f06295..031d9660e5b 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_mysql_embedded.result @@ -9,6 +9,14 @@ def mysql columns_priv Host 1 NO char 60 180 NULL NULL NULL utf8 utf8_bin char( def mysql columns_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin char(64) PRI def mysql columns_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP def mysql columns_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI +def mysql column_stat avg_frequency 8 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat avg_length 7 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat column_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql column_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql column_stat max_value 5 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) +def mysql column_stat min_value 4 NULL YES varchar 255 765 NULL NULL NULL utf8 utf8_bin varchar(255) +def mysql column_stat nulls_ratio 6 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql column_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql db Alter_priv 13 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql db Alter_routine_priv 19 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql db Create_priv 8 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') @@ -97,6 +105,11 @@ def mysql host Select_priv 3 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci e def mysql host Show_view_priv 16 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql host Trigger_priv 20 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') def mysql host Update_priv 5 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('N','Y') +def mysql index_stat avg_frequency 5 NULL YES double NULL NULL 22 NULL NULL NULL NULL double +def mysql index_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql index_stat index_name 3 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql index_stat prefix_arity 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) unsigned PRI +def mysql index_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql ndb_binlog_index deletes 6 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned def mysql ndb_binlog_index epoch 3 NULL NO bigint NULL NULL 20 0 NULL NULL NULL bigint(20) unsigned PRI def mysql ndb_binlog_index File 2 NULL NO varchar 255 255 NULL NULL NULL latin1 latin1_swedish_ci varchar(255) @@ -169,6 +182,9 @@ def mysql tables_priv Table_name 4 NO char 64 192 NULL NULL NULL utf8 utf8_bin def mysql tables_priv Table_priv 7 NO set 98 294 NULL NULL NULL utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') def mysql tables_priv Timestamp 6 CURRENT_TIMESTAMP NO timestamp NULL NULL NULL NULL 0 NULL NULL timestamp on update CURRENT_TIMESTAMP def mysql tables_priv User 3 NO char 16 48 NULL NULL NULL utf8 utf8_bin char(16) PRI +def mysql table_stat cardinality 3 NULL YES bigint NULL NULL 20 0 NULL NULL NULL bigint(21) unsigned +def mysql table_stat db_name 1 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI +def mysql table_stat table_name 2 NULL NO varchar 64 192 NULL NULL NULL utf8 utf8_bin varchar(64) PRI def mysql time_zone Time_zone_id 1 NULL NO int NULL NULL 10 0 NULL NULL NULL int(10) unsigned PRI auto_increment def mysql time_zone Use_leap_seconds 2 N NO enum 1 3 NULL NULL NULL utf8 utf8_general_ci enum('Y','N') def mysql time_zone_leap_second Correction 2 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) @@ -258,6 +274,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME 3.0000 char utf8 utf8_bin 3.0000 enum utf8 utf8_bin +3.0000 varchar utf8 utf8_bin 3.0000 char utf8 utf8_general_ci 3.0000 enum utf8 utf8_general_ci 3.0000 set utf8 utf8_general_ci @@ -274,6 +291,7 @@ ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML; COL_CML DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME NULL bigint NULL NULL NULL datetime NULL NULL +NULL double NULL NULL NULL int NULL NULL NULL smallint NULL NULL NULL time NULL NULL @@ -302,6 +320,14 @@ COL_CML TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH C 3.0000 mysql columns_priv Column_name char 64 192 utf8 utf8_bin char(64) NULL mysql columns_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql columns_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql column_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat column_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql column_stat min_value varchar 255 765 utf8 utf8_bin varchar(255) +3.0000 mysql column_stat max_value varchar 255 765 utf8 utf8_bin varchar(255) +NULL mysql column_stat nulls_ratio double NULL NULL NULL NULL double +NULL mysql column_stat avg_length double NULL NULL NULL NULL double +NULL mysql column_stat avg_frequency double NULL NULL NULL NULL double 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) @@ -390,6 +416,11 @@ NULL mysql help_topic help_category_id smallint NULL NULL NULL NULL smallint(5) 3.0000 mysql host Alter_routine_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Execute_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') 3.0000 mysql host Trigger_priv enum 1 3 utf8 utf8_general_ci enum('N','Y') +3.0000 mysql index_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql index_stat index_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql index_stat prefix_arity int NULL NULL NULL NULL int(11) unsigned +NULL mysql index_stat avg_frequency double NULL NULL NULL NULL double NULL mysql ndb_binlog_index Position bigint NULL NULL NULL NULL bigint(20) unsigned 1.0000 mysql ndb_binlog_index File varchar 255 255 latin1 latin1_swedish_ci varchar(255) NULL mysql ndb_binlog_index epoch bigint NULL NULL NULL NULL bigint(20) unsigned @@ -462,6 +493,9 @@ NULL mysql slow_log server_id int NULL NULL NULL NULL int(10) unsigned NULL mysql tables_priv Timestamp timestamp NULL NULL NULL NULL timestamp 3.0000 mysql tables_priv Table_priv set 98 294 utf8 utf8_general_ci set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') 3.0000 mysql tables_priv Column_priv set 31 93 utf8 utf8_general_ci set('Select','Insert','Update','References') +3.0000 mysql table_stat db_name varchar 64 192 utf8 utf8_bin varchar(64) +3.0000 mysql table_stat table_name varchar 64 192 utf8 utf8_bin varchar(64) +NULL mysql table_stat cardinality bigint NULL NULL NULL NULL bigint(21) unsigned NULL mysql time_zone Time_zone_id int NULL NULL NULL NULL int(10) unsigned 3.0000 mysql time_zone Use_leap_seconds enum 1 3 utf8 utf8_general_ci enum('Y','N') NULL mysql time_zone_leap_second Transition_time bigint NULL NULL NULL NULL bigint(20) diff --git a/mysql-test/suite/funcs_1/r/is_key_column_usage.result b/mysql-test/suite/funcs_1/r/is_key_column_usage.result index afd1fe15fed..147e063fe6f 100644 --- a/mysql-test/suite/funcs_1/r/is_key_column_usage.result +++ b/mysql-test/suite/funcs_1/r/is_key_column_usage.result @@ -75,6 +75,9 @@ table_schema, table_name, column_name FROM information_schema.key_column_usage WHERE constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL; constraint_catalog constraint_schema constraint_name table_catalog table_schema table_name column_name +def mysql PRIMARY def mysql column_stat db_name +def mysql PRIMARY def mysql column_stat table_name +def mysql PRIMARY def mysql column_stat column_name def mysql PRIMARY def mysql columns_priv Host def mysql PRIMARY def mysql columns_priv Db def mysql PRIMARY def mysql columns_priv User @@ -96,6 +99,10 @@ def mysql PRIMARY def mysql help_topic help_topic_id def mysql name def mysql help_topic name def mysql PRIMARY def mysql host Host def mysql PRIMARY def mysql host Db +def mysql PRIMARY def mysql index_stat db_name +def mysql PRIMARY def mysql index_stat table_name +def mysql PRIMARY def mysql index_stat index_name +def mysql PRIMARY def mysql index_stat prefix_arity def mysql PRIMARY def mysql ndb_binlog_index epoch def mysql PRIMARY def mysql plugin name def mysql PRIMARY def mysql proc db @@ -111,6 +118,8 @@ def mysql PRIMARY def mysql proxies_priv User def mysql PRIMARY def mysql proxies_priv Proxied_host def mysql PRIMARY def mysql proxies_priv Proxied_user def mysql PRIMARY def mysql servers Server_name +def mysql PRIMARY def mysql table_stat db_name +def mysql PRIMARY def mysql table_stat table_name def mysql PRIMARY def mysql tables_priv Host def mysql PRIMARY def mysql tables_priv Db def mysql PRIMARY def mysql tables_priv User diff --git a/mysql-test/suite/funcs_1/r/is_statistics.result b/mysql-test/suite/funcs_1/r/is_statistics.result index 8543b207728..b0d52779ba5 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics.result +++ b/mysql-test/suite/funcs_1/r/is_statistics.result @@ -85,6 +85,9 @@ INDEX_COMMENT varchar(1024) NO SELECT table_catalog, table_schema, table_name, index_schema, index_name FROM information_schema.statistics WHERE table_catalog IS NOT NULL; table_catalog table_schema table_name index_schema index_name +def mysql column_stat mysql PRIMARY +def mysql column_stat mysql PRIMARY +def mysql column_stat mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY def mysql columns_priv mysql PRIMARY @@ -107,6 +110,10 @@ def mysql help_topic mysql PRIMARY def mysql help_topic mysql name def mysql host mysql PRIMARY def mysql host mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY +def mysql index_stat mysql PRIMARY def mysql ndb_binlog_index mysql PRIMARY def mysql plugin mysql PRIMARY def mysql proc mysql PRIMARY @@ -124,6 +131,8 @@ def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql PRIMARY def mysql proxies_priv mysql Grantor def mysql servers mysql PRIMARY +def mysql table_stat mysql PRIMARY +def mysql table_stat mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY def mysql tables_priv mysql PRIMARY diff --git a/mysql-test/suite/funcs_1/r/is_statistics_mysql.result b/mysql-test/suite/funcs_1/r/is_statistics_mysql.result index 4c7d58f96f1..3609fd97232 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_statistics_mysql.result @@ -12,6 +12,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -29,6 +32,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -51,6 +58,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE diff --git a/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result index 9137d70b88c..d6b5523a2b2 100644 --- a/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_statistics_mysql_embedded.result @@ -12,6 +12,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -29,6 +32,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -51,6 +58,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE @@ -70,6 +79,9 @@ def mysql columns_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE def mysql columns_priv 0 mysql PRIMARY 5 Column_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql column_stat 0 mysql PRIMARY 3 column_name A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql db 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE @@ -87,6 +99,10 @@ def mysql help_topic 0 mysql name 1 name A #CARD# NULL NULL BTREE def mysql help_topic 0 mysql PRIMARY 1 help_topic_id A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql host 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 3 index_name A #CARD# NULL NULL BTREE +def mysql index_stat 0 mysql PRIMARY 4 prefix_arity A #CARD# NULL NULL BTREE def mysql ndb_binlog_index 0 mysql PRIMARY 1 epoch A #CARD# NULL NULL BTREE def mysql plugin 0 mysql PRIMARY 1 name A #CARD# NULL NULL BTREE def mysql proc 0 mysql PRIMARY 1 db A #CARD# NULL NULL BTREE @@ -109,6 +125,8 @@ def mysql tables_priv 0 mysql PRIMARY 1 Host A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 2 Db A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 3 User A #CARD# NULL NULL BTREE def mysql tables_priv 0 mysql PRIMARY 4 Table_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 1 db_name A #CARD# NULL NULL BTREE +def mysql table_stat 0 mysql PRIMARY 2 table_name A #CARD# NULL NULL BTREE def mysql time_zone 0 mysql PRIMARY 1 Time_zone_id A #CARD# NULL NULL BTREE def mysql time_zone_leap_second 0 mysql PRIMARY 1 Transition_time A #CARD# NULL NULL BTREE def mysql time_zone_name 0 mysql PRIMARY 1 Name A #CARD# NULL NULL BTREE diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints.result b/mysql-test/suite/funcs_1/r/is_table_constraints.result index 559a1f1f9f5..67a12d1529b 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints.result @@ -57,6 +57,7 @@ table_schema, table_name FROM information_schema.table_constraints WHERE constraint_catalog IS NOT NULL; constraint_catalog constraint_schema constraint_name table_schema table_name +def mysql PRIMARY mysql column_stat def mysql PRIMARY mysql columns_priv def mysql PRIMARY mysql db def mysql PRIMARY mysql event @@ -69,12 +70,14 @@ def mysql PRIMARY mysql help_relation def mysql PRIMARY mysql help_topic def mysql name mysql help_topic def mysql PRIMARY mysql host +def mysql PRIMARY mysql index_stat def mysql PRIMARY mysql ndb_binlog_index def mysql PRIMARY mysql plugin def mysql PRIMARY mysql proc def mysql PRIMARY mysql procs_priv def mysql PRIMARY mysql proxies_priv def mysql PRIMARY mysql servers +def mysql PRIMARY mysql table_stat def mysql PRIMARY mysql tables_priv def mysql PRIMARY mysql time_zone def mysql PRIMARY mysql time_zone_leap_second diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result index bca333b6387..2629eca1325 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql.result @@ -8,6 +8,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -19,6 +20,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -26,6 +28,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY diff --git a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result index 307357cdd2b..726cebd590b 100644 --- a/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_table_constraints_mysql_embedded.result @@ -8,6 +8,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -19,6 +20,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -26,6 +28,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY @@ -38,6 +41,7 @@ WHERE table_schema = 'mysql' ORDER BY table_schema,table_name,constraint_name; CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE def mysql PRIMARY mysql columns_priv PRIMARY KEY +def mysql PRIMARY mysql column_stat PRIMARY KEY def mysql PRIMARY mysql db PRIMARY KEY def mysql PRIMARY mysql event PRIMARY KEY def mysql PRIMARY mysql func PRIMARY KEY @@ -49,6 +53,7 @@ def mysql PRIMARY mysql help_relation PRIMARY KEY def mysql name mysql help_topic UNIQUE def mysql PRIMARY mysql help_topic PRIMARY KEY def mysql PRIMARY mysql host PRIMARY KEY +def mysql PRIMARY mysql index_stat PRIMARY KEY def mysql PRIMARY mysql ndb_binlog_index PRIMARY KEY def mysql PRIMARY mysql plugin PRIMARY KEY def mysql PRIMARY mysql proc PRIMARY KEY @@ -56,6 +61,7 @@ def mysql PRIMARY mysql procs_priv PRIMARY KEY def mysql PRIMARY mysql proxies_priv PRIMARY KEY def mysql PRIMARY mysql servers PRIMARY KEY def mysql PRIMARY mysql tables_priv PRIMARY KEY +def mysql PRIMARY mysql table_stat PRIMARY KEY def mysql PRIMARY mysql time_zone PRIMARY KEY def mysql PRIMARY mysql time_zone_leap_second PRIMARY KEY def mysql PRIMARY mysql time_zone_name PRIMARY KEY diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql.result b/mysql-test/suite/funcs_1/r/is_tables_mysql.result index 1b317fa5bba..9b20b05a6d4 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql.result @@ -37,6 +37,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -244,6 +267,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -428,6 +474,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result index e5afebd0de9..80718c62d85 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_mysql_embedded.result @@ -37,6 +37,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -244,6 +267,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -428,6 +474,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -605,6 +674,29 @@ user_comment Column privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME column_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Columns +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME db TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -812,6 +904,29 @@ user_comment Host privileges; Merged with database privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME index_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Indexes +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME ndb_binlog_index TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA @@ -996,6 +1111,29 @@ user_comment Table privileges Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA mysql +TABLE_NAME table_stat +TABLE_TYPE BASE TABLE +ENGINE MYISAM_OR_MARIA +VERSION 10 +ROW_FORMAT DYNAMIC_OR_PAGE +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8_bin +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +user_comment Statistics on Tables +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA mysql TABLE_NAME time_zone TABLE_TYPE BASE TABLE ENGINE MYISAM_OR_MARIA diff --git a/mysql-test/suite/perfschema/r/pfs_upgrade.result b/mysql-test/suite/perfschema/r/pfs_upgrade.result index 4d7d9e28fe8..b15fb1ad7fa 100644 --- a/mysql-test/suite/perfschema/r/pfs_upgrade.result +++ b/mysql-test/suite/perfschema/r/pfs_upgrade.result @@ -25,7 +25,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_table"; Tables_in_performance_schema (user_table) @@ -55,7 +55,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed show tables like "user_view"; Tables_in_performance_schema (user_view) @@ -83,7 +83,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -111,7 +111,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.proc where db='performance_schema'; name @@ -139,7 +139,7 @@ ERROR 1050 (42S01) at line 428: Table 'setup_consumers' already exists ERROR 1050 (42S01) at line 445: Table 'setup_instruments' already exists ERROR 1050 (42S01) at line 461: Table 'setup_timers' already exists ERROR 1050 (42S01) at line 478: Table 'threads' already exists -ERROR 1644 (HY000) at line 1126: Unexpected content found in the performance_schema database. +ERROR 1644 (HY000) at line 1132: Unexpected content found in the performance_schema database. FATAL ERROR: Upgrade failed select name from mysql.event where db='performance_schema'; name diff --git a/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result b/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result new file mode 100644 index 00000000000..22c6859af62 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/optimizer_use_stat_tables_basic.result @@ -0,0 +1,116 @@ +SET @start_global_value = @@global.optimizer_use_stat_tables; +SELECT @start_global_value; +@start_global_value +NEVER +SET @start_session_value = @@session.optimizer_use_stat_tables; +SELECT @start_session_value; +@start_session_value +NEVER +SET @@global.optimizer_use_stat_tables = 2; +SET @@global.optimizer_use_stat_tables = DEFAULT; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = 3; +SET @@session.optimizer_use_stat_tables = DEFAULT; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = 0; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = 1; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = 2; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +PREFERABLY +SET @@global.optimizer_use_stat_tables = 3; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +EXCLUSIVELY +SET @@global.optimizer_use_stat_tables = NEVER; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@global.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = PREFERABLY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +PREFERABLY +SET @@global.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = 0; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = 1; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +COMPLEMENTARY +SET @@session.optimizer_use_stat_tables = 2; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +PREFERABLY +SET @@session.optimizer_use_stat_tables = 3; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = NEVER; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = PREFERABLY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +PREFERABLY +SET @@session.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +EXCLUSIVELY +SET @@session.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +COMPLEMENTARY +set sql_mode=TRADITIONAL; +SET @@global.optimizer_use_stat_tables = 10; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '10' +SET @@global.optimizer_use_stat_tables = -1024; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '-1024' +SET @@global.optimizer_use_stat_tables = 2.4; +ERROR 42000: Incorrect argument type to variable 'optimizer_use_stat_tables' +SET @@global.optimizer_use_stat_tables = OFF; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of 'OFF' +SET @@session.optimizer_use_stat_tables = 10; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '10' +SET @@session.optimizer_use_stat_tables = -2; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of '-2' +SET @@session.optimizer_use_stat_tables = 1.2; +ERROR 42000: Incorrect argument type to variable 'optimizer_use_stat_tables' +SET @@session.optimizer_use_stat_tables = ON; +ERROR 42000: Variable 'optimizer_use_stat_tables' can't be set to the value of 'ON' +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; +VARIABLE_NAME VARIABLE_VALUE +OPTIMIZER_USE_STAT_TABLES EXCLUSIVELY +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; +VARIABLE_NAME VARIABLE_VALUE +OPTIMIZER_USE_STAT_TABLES COMPLEMENTARY +SET @@global.optimizer_use_stat_tables = @start_global_value; +SELECT @@global.optimizer_use_stat_tables; +@@global.optimizer_use_stat_tables +NEVER +SET @@session.optimizer_use_stat_tables = @start_session_value; +SELECT @@session.optimizer_use_stat_tables; +@@session.optimizer_use_stat_tables +NEVER +set sql_mode=''; diff --git a/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test b/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test new file mode 100644 index 00000000000..6e0a96c403e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/optimizer_use_stat_tables_basic.test @@ -0,0 +1,112 @@ +--source include/load_sysvars.inc + +############################################################# +# Save initial value # +############################################################# + +SET @start_global_value = @@global.optimizer_use_stat_tables; +SELECT @start_global_value; +SET @start_session_value = @@session.optimizer_use_stat_tables; +SELECT @start_session_value; + +############################################################### +# Display the DEFAULT value of optimizer_use_stat_tables # +############################################################### + +SET @@global.optimizer_use_stat_tables = 2; +SET @@global.optimizer_use_stat_tables = DEFAULT; +SELECT @@global.optimizer_use_stat_tables; + +SET @@session.optimizer_use_stat_tables = 3; +SET @@session.optimizer_use_stat_tables = DEFAULT; +SELECT @@session.optimizer_use_stat_tables; + + +################################################################################## +# Change the value ofoptimizer_use_stat_tables to a valid value for GLOBAL Scope # +################################################################################## + +SET @@global.optimizer_use_stat_tables = 0; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 1; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 2; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = 3; +SELECT @@global.optimizer_use_stat_tables; + +SET @@global.optimizer_use_stat_tables = NEVER; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = PREFERABLY; +SELECT @@global.optimizer_use_stat_tables; +SET @@global.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@global.optimizer_use_stat_tables; + +#################################################################################### +# Change the value of optimizer_use_stat_tables to a valid value for SESSION Scope # +#################################################################################### + +SET @@session.optimizer_use_stat_tables = 0; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 1; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 2; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = 3; +SELECT @@session.optimizer_use_stat_tables; + +SET @@session.optimizer_use_stat_tables = NEVER; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = PREFERABLY; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = EXCLUSIVELY; +SELECT @@session.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = COMPLEMENTARY; +SELECT @@session.optimizer_use_stat_tables; + +##################################################################### +# Change the value of optimizer_use_stat_tables to an invalid value # +##################################################################### +set sql_mode=TRADITIONAL; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = 10; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = -1024; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@global.optimizer_use_stat_tables = 2.4; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@global.optimizer_use_stat_tables = OFF; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = 10; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = -2; +--Error ER_WRONG_TYPE_FOR_VAR +SET @@session.optimizer_use_stat_tables = 1.2; +--Error ER_WRONG_VALUE_FOR_VAR +SET @@session.optimizer_use_stat_tables = ON; + +############################################################################### +# Check if the value in GLOBAL & SESSION Tables matches value in variable # +############################################################################### + +SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; + +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='optimizer_use_stat_tables'; + +#################################### +# Restore initial value # +#################################### + +SET @@global.optimizer_use_stat_tables = @start_global_value; +SELECT @@global.optimizer_use_stat_tables; +SET @@session.optimizer_use_stat_tables = @start_session_value; +SELECT @@session.optimizer_use_stat_tables; +set sql_mode=''; + +###################################################### +# END OF optimizer_use_stat_tables TESTS # +######################################################
\ No newline at end of file diff --git a/mysql-test/t/statistics.test b/mysql-test/t/statistics.test new file mode 100644 index 00000000000..81108c9846d --- /dev/null +++ b/mysql-test/t/statistics.test @@ -0,0 +1,303 @@ +--source include/have_innodb.inc +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +set @save_optimizer_use_stat_tables=@@optimizer_use_stat_tables; + +CREATE VIEW table_stat AS +SELECT * FROM mysql.table_stat; + +CREATE VIEW column_stat AS + SELECT db_name, table_name, column_name, + min_value, max_value, + CAST(nulls_ratio AS decimal(12,4)) AS 'nulls_ratio', + CAST(avg_length AS decimal(12,4)) AS 'avg_length', + CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.column_stat; + +CREATE VIEW index_stat AS + SELECT db_name, table_name, index_name, prefix_arity, + CAST(avg_frequency AS decimal(12,4)) AS 'avg_frequency' + FROM mysql.index_stat; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +set optimizer_use_stat_tables='preferably'; + +CREATE TABLE t1 ( + a int NOT NULL PRIMARY KEY, + b varchar(32), + c char(16), + d date, + e double, + f bit(3), + INDEX idx1 (b, e), + INDEX idx2(c, d), + INDEX idx3 (d), + INDEX idx4 (e, b, d) +); + +INSERT INTO t1 VALUES + (0, NULL, NULL, NULL, NULL, NULL), + (7, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'dddddddd', '1990-05-15', 0.1, b'100'), + (17, 'vvvvvvvvvvvvv', 'aaaa', '1989-03-12', 0.01, b'101'), + (1, 'vvvvvvvvvvvvv', NULL, '1989-03-12', 0.01, b'100'), + (12, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.112, b'001'), + (23, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.1, b'100'), + (8, 'vvvvvvvvvvvvv', 'aaaa', '1999-07-23', 0.1, b'100'), + (22, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'aaaa', '1989-03-12', 0.112, b'001'), + (31, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'aaaa', '1999-07-23', 0.01, b'001'), + (10, NULL, 'aaaa', NULL, 0.01, b'010'), + (5, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1999-07-23', 0.1, b'100'), + (15, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.1, b'010'), + (30, NULL, 'bbbbbb', NULL, NULL, b'100'), + (38, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, NULL, NULL), + (18, 'zzzzzzzzzzzzzzzzzz', 'ccccccccc', '1990-05-15', 0.01, b'010'), + (9, 'yyy', 'bbbbbb', '1998-08-28', 0.01, NULL), + (29, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.012, b'010'), + (3, 'yyy', 'dddddddd', '1990-05-15', 0.112, b'010'), + (39, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', NULL, 0.01, b'100'), + (14, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.1, b'100'), + (40, 'zzzzzzzzzzzzzzzzzz', 'bbbbbb', '1989-03-12', NULL, NULL), + (44, NULL, 'aaaa', '1989-03-12', NULL, b'010'), + (19, 'vvvvvvvvvvvvv', 'ccccccccc', '1990-05-15', 0.012, b'011'), + (21, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.112, b'100'), + (45, NULL, NULL, '1989-03-12', NULL, b'011'), + (2, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'ccccccccc', '1990-05-15', 0.1, b'001'), + (35, 'yyy', 'aaaa', '1990-05-15', 0.05, b'011'), + (4, 'vvvvvvvvvvvvv', 'dddddddd', '1999-07-23', 0.01, b'101'), + (47, NULL, 'aaaa', '1990-05-15', 0.05, b'010'), + (42, NULL, 'ccccccccc', '1989-03-12', 0.01, b'010'), + (32, NULL, 'bbbbbb', '1990-05-15', 0.01, b'011'), + (49, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'aaaa', '1990-05-15', NULL, NULL), + (43, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww' , 'bbbbbb', '1990-05-15', NULL, b'100'), + (37, 'yyy', NULL, '1989-03-12', 0.05, b'011'), + (41, 'xxxxxxxxxxxxxxxxxxxxxxxxxx', 'ccccccccc', '1990-05-15', 0.05, NULL), + (34, 'yyy', NULL, NULL, NULL, NULL), + (33, 'zzzzzzzzzzzzzzzzzz', 'dddddddd', '1989-03-12', 0.05, b'011'), + (24, 'wwwwwwwwwwwwwwwwwwwwwwwwwwww', 'dddddddd', '1990-05-15', 0.01, b'101'), + (11, 'yyy', 'ccccccccc', '1999-07-23', 0.1, NULL), + (25, 'zzzzzzzzzzzzzzzzzz', 'bbb', '1989-03-12', 0.01, b'101'); + +ANALYZE TABLE t1; + +SELECT * FROM table_stat; +SELECT * FROM column_stat; +SELECT * FROM index_stat; + +SELECT COUNT(*) FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='a'; +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)", + (SELECT COUNT(t1.a) FROM t1) / + (SELECT COUNT(DISTINCT t1.a) FROM t1) AS "AVG_FREQUENCY(t1.a)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='b'; +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)", + (SELECT COUNT(t1.b) FROM t1) / + (SELECT COUNT(DISTINCT t1.b) FROM t1) AS "AVG_FREQUENCY(t1.b)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='c'; +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)", + (SELECT COUNT(t1.c) FROM t1) / + (SELECT COUNT(DISTINCT t1.c) FROM t1) AS "AVG_FREQUENCY(t1.c)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='d'; +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)", + (SELECT COUNT(t1.d) FROM t1) / + (SELECT COUNT(DISTINCT t1.d) FROM t1) AS "AVG_FREQUENCY(t1.d)" +FROM t1; + +SELECT * FROM column_stat + WHERE db_name='test' AND table_name='t1' AND column_name='e'; +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)", + (SELECT COUNT(t1.e) FROM t1) / + (SELECT COUNT(DISTINCT t1.e) FROM t1) AS "AVG_FREQUENCY(t1.e)" +FROM t1; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx1'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.b) FROM t1 WHERE t1.b IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.b, t1.e) FROM t1 + WHERE t1.b IS NOT NULL AND t1.e IS NOT NULL) + AS 'ARITY 2'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx2'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.c) FROM t1 WHERE t1.c IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.c, t1.d) FROM t1 + WHERE t1.c IS NOT NULL AND t1.d IS NOT NULL) + AS 'ARITY 2'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx3'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.d) FROM t1 WHERE t1.d IS NOT NULL) + AS 'ARITY 1'; + +SELECT * FROM index_stat + WHERE db_name='test' AND table_name='t1' AND index_name='idx4'; +SELECT + (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e) FROM t1 WHERE t1.e IS NOT NULL) + AS 'ARITY 1', + (SELECT COUNT(*) FROM t1 WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e, t1.b) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL) + AS 'ARITY 2', + (SELECT COUNT(*) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) / + (SELECT COUNT(DISTINCT t1.e, t1.b, t1.d) FROM t1 + WHERE t1.e IS NOT NULL AND t1.b IS NOT NULL AND t1.d IS NOT NULL) + AS 'ARITY 3'; + + +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ENGINE=InnoDB; +INSERT INTO t2 SELECT * FROM t1; + +ANALYZE TABLE t2; + +SELECT * FROM table_stat; +SELECT * FROM column_stat ORDER BY column_name; +SELECT * FROM index_stat ORDER BY index_name, prefix_arity, table_name; + + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +ALTER TABLE t1 + DROP INDEX idx1, + DROP INDEX idx4; +ALTER TABLE t1 + MODIFY COLUMN b text, + ADD INDEX idx1 (b(4), e), + ADD INDEX idx4 (e, b(4), d); + +ANALYZE TABLE t1; + +SELECT * FROM column_stat; +SELECT * FROM index_stat; + +DROP TABLE t1,t2; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +set optimizer_use_stat_tables='never'; + +set names utf8; + +CREATE DATABASE world; + +use world; + +--source include/world_schema_utf8.inc + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +set optimizer_use_stat_tables='preferably'; + +--disable_result_log +ANALYZE TABLE Country, City, CountryLanguage; +--enable_result_log + +SELECT UPPER(db_name), UPPER(table_name), cardinality + FROM test.table_stat; +SELECT UPPER(db_name), UPPER(table_name), + column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency + FROM test.column_stat; +SELECT UPPER(db_name), UPPER(table_name), + index_name, prefix_arity, avg_frequency + FROM test.index_stat; + +use test; + +set optimizer_use_stat_tables='never'; + +CREATE DATABASE world_innodb; + +use world_innodb; + +--source include/world_schema_utf8.inc + +ALTER TABLE Country ENGINE=InnoDB; +ALTER TABLE City ENGINE=InnoDB; +ALTER TABLE CountryLanguage ENGINE=InnoDB; + +--disable_query_log +--disable_result_log +--disable_warnings +--source include/world.inc +--enable_warnings +--enable_result_log +--enable_query_log + +set optimizer_use_stat_tables='preferably'; + +--disable_result_log +ANALYZE TABLE Country, City, CountryLanguage; +--enable_result_log + +SELECT UPPER(db_name), UPPER(table_name), cardinality + FROM test.table_stat; +SELECT UPPER(db_name), UPPER(table_name), + column_name, min_value, max_value, nulls_ratio, avg_length, avg_frequency + FROM test.column_stat; +SELECT UPPER(db_name), UPPER(table_name), + index_name, prefix_arity, avg_frequency + FROM test.index_stat; + +use test; + +DROP DATABASE world; +DROP DATABASE world_innodb; + +DELETE FROM mysql.table_stat; +DELETE FROM mysql.column_stat; +DELETE FROM mysql.index_stat; + +DROP VIEW test.table_stat; +DROP VIEW test.column_stat; +DROP VIEW test.index_stat; + +set optimizer_use_stat_tables=@save_optimizer_use_stat_tables; + +
\ No newline at end of file diff --git a/mysql-test/t/system_mysql_db_fix40123.test b/mysql-test/t/system_mysql_db_fix40123.test index 5c82f0022b8..ae5b86262eb 100644 --- a/mysql-test/t/system_mysql_db_fix40123.test +++ b/mysql-test/t/system_mysql_db_fix40123.test @@ -59,6 +59,11 @@ CREATE TABLE time_zone_transition_type ( Time_zone_id int unsigned NOT NULL, CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, Correction int signed NOT NULL, PRIMARY KEY TranTime (Transition_time) ) engine=MyISAM CHARACTER SET utf8 comment='Leap seconds information for time zones'; +CREATE TABLE table_stat (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 column_stat (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 double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (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 double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -72,7 +77,7 @@ CREATE TABLE time_zone_leap_second ( Transition_time bigint signed NOT NULL, -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50030.test b/mysql-test/t/system_mysql_db_fix50030.test index e8cfe6a6cf3..35938b6f7fa 100644 --- a/mysql-test/t/system_mysql_db_fix50030.test +++ b/mysql-test/t/system_mysql_db_fix50030.test @@ -66,6 +66,12 @@ CREATE TABLE servers ( Server_name char(64) NOT NULL DEFAULT '', Host char(64) N INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','root'); +CREATE TABLE table_stat (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 column_stat (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 double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (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 double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; + # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -78,7 +84,7 @@ INSERT INTO servers VALUES ('test','localhost','test','root','', 0,'','mysql','r -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log diff --git a/mysql-test/t/system_mysql_db_fix50117.test b/mysql-test/t/system_mysql_db_fix50117.test index 69ad68faaa8..bf19b70e77d 100644 --- a/mysql-test/t/system_mysql_db_fix50117.test +++ b/mysql-test/t/system_mysql_db_fix50117.test @@ -85,6 +85,12 @@ CREATE TABLE IF NOT EXISTS event ( db char(64) CHARACTER SET utf8 COLLATE utf8_b CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM; +CREATE TABLE table_stat (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 column_stat (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 double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double DEFAULT NULL, PRIMARY KEY (db_name,table_name,column_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Columns'; + +CREATE TABLE index_stat (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 double DEFAULT NULL, PRIMARY KEY (db_name,table_name,index_name,prefix_arity) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Statistics on Indexes'; + # Run the mysql_fix_privilege_tables.sql using "mysql --force" --exec $MYSQL --force test < $MYSQL_FIX_PRIVILEGE_TABLES @@ -97,7 +103,7 @@ CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, -- disable_query_log # Drop all tables created by this test -DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv; +DROP TABLE db, host, user, func, plugin, tables_priv, columns_priv, procs_priv, servers, help_category, help_keyword, help_relation, help_topic, proc, time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, time_zone_transition_type, general_log, slow_log, event, ndb_binlog_index, proxies_priv, table_stat, column_stat, index_stat; -- enable_query_log diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 2e97d2b8272..eaf4fda4026 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -482,3 +482,9 @@ CREATE TABLE IF NOT EXISTS proxies_priv (Host char(60) binary DEFAULT '' NOT NUL -- Remember for later if proxies_priv table already existed set @had_proxies_priv_table= @@warning_count != 0; + +CREATE TABLE IF NOT EXISTS table_stat (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_stat (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 double DEFAULT NULL, avg_length double DEFAULT NULL, avg_frequency double 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 index_stat (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 double 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/CMakeLists.txt b/sql/CMakeLists.txt index 22c51f3ce23..8834e6a8ae9 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -62,7 +62,8 @@ SET (SQL_SOURCE sql_list.cc sql_load.cc sql_manager.cc sql_parse.cc sql_partition.cc sql_plugin.cc sql_prepare.cc sql_rename.cc debug_sync.cc debug_sync.h - sql_repl.cc sql_select.cc sql_show.cc sql_state.c sql_string.cc + sql_repl.cc sql_select.cc sql_show.cc sql_state.c + sql_statistics.cc sql_string.cc sql_table.cc sql_test.cc sql_trigger.cc sql_udf.cc sql_union.cc sql_update.cc sql_view.cc strfunc.cc table.cc thr_malloc.cc sql_time.cc tztime.cc uniques.cc unireg.cc item_xmlfunc.cc diff --git a/sql/field.cc b/sql/field.cc index f5b43c69f63..34cb81f9d66 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -76,7 +76,7 @@ const char field_separator=','; ((ulong) ((LL(1) << min(arg, 4) * 8) - LL(1))) #define ASSERT_COLUMN_MARKED_FOR_READ DBUG_ASSERT(!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))) -#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(!table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || bitmap_is_set(table->vcol_set, field_index))) +#define ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED DBUG_ASSERT(is_stat_field || !table || (!table->write_set || bitmap_is_set(table->write_set, field_index) || bitmap_is_set(table->vcol_set, field_index))) #define FLAGSTR(S,F) ((S) & (F) ? #F " " : "") @@ -1330,7 +1330,8 @@ Field::Field(uchar *ptr_arg,uint32 length_arg,uchar *null_ptr_arg, flags=null_ptr ? 0: NOT_NULL_FLAG; comment.str= (char*) ""; comment.length=0; - field_index= 0; + field_index= 0; + is_stat_field= FALSE; } @@ -1855,6 +1856,21 @@ Field *Field::clone(MEM_ROOT *root, TABLE *new_table) } + +Field *Field::clone(MEM_ROOT *root, TABLE *new_table, my_ptrdiff_t diff, + bool stat_flag) +{ + Field *tmp; + if ((tmp= (Field*) memdup_root(root,(char*) this,size_of()))) + { + tmp->init(new_table); + tmp->move_field_offset(diff); + } + tmp->is_stat_field= stat_flag; + return tmp; +} + + /**************************************************************************** Field_null, a field that always return NULL ****************************************************************************/ diff --git a/sql/field.h b/sql/field.h index 0b32aab0337..9f747426847 100644 --- a/sql/field.h +++ b/sql/field.h @@ -63,6 +63,7 @@ enum Derivation #define my_charset_numeric my_charset_latin1 #define MY_REPERTOIRE_NUMERIC MY_REPERTOIRE_ASCII +class Count_distinct_field; struct ha_field_option_struct; struct st_cache_field; @@ -213,6 +214,57 @@ public: */ bool is_created_from_null_item; + bool is_stat_field; /* TRUE in Field objects created for column min/max values */ + + /* Statistical data on a column */ + class Column_statistics + { + public: + /* + Bitmap indicating what statistical characteristics + are available for the column + */ + uint32 column_stat_nulls; + + /* Minimum value for the column */ + Field *min_value; + /* Maximum value for the column */ + Field *max_value; + /* + The ratio Z/N, where N is the total number of rows, + Z is the number of nulls in the column + */ + double nulls_ratio; + /* + Average number of bytes occupied by the representation of a + value of the column in memory buffers such as join buffer. + CHAR values are stripped of trailing spaces. + Flexible values are stripped of their length prefixes. + */ + double avg_length; + /* + The ratio N/D, where N is the number of rows with null value + in the column, D the number of distinct values among them + */ + double avg_frequency; + }; + + /* + This structure is used for statistical data on the column + that has been read from the statistical table column_stat + */ + Column_statistics read_stat; + /* + This structure is used for statistical data on the column that + is collected by the function collect_statistics_for_table + */ + Column_statistics write_stat; + + /* These members are used only when collecting statistics on the column */ + ha_rows nulls; + ulonglong column_total_length; + Count_distinct_field *count_distinct; + /* This is additional data provided for any computed(virtual) field. In particular it includes a pointer to the item by which this field @@ -305,6 +357,26 @@ public: virtual uint32 data_length() { return pack_length(); } virtual uint32 sort_length() const { return pack_length(); } + /* + Get the number bytes occupied by the value in the field. + CHAR values are stripped of trailing spaces. + Flexible values are stripped of their length. + */ + virtual uint32 value_length() + { + uint len; + if (!zero_pack() && + (type() == MYSQL_TYPE_STRING && + (len= pack_length()) >= 4 && len < 256)) + { + uchar *str, *end; + for (str= ptr, end= str+len; end > str && end[-1] == ' '; end--) {} + len=(uint) (end-str); + return len; + } + return data_length(); + } + /** Get the maximum size of the data in packed format. @@ -346,6 +418,36 @@ public: { return cmp(a, b); } virtual int key_cmp(const uchar *str, uint length) { return cmp(ptr,str); } + /* + Update the value m of the 'min_val' field with the current value v + of this field if force_update is set to TRUE or if v < m. + Return TRUE if the value has been updated. + */ + virtual bool update_min(Field *min_val, bool force_update) + { + bool update_fl= force_update || cmp(ptr, min_val->ptr) < 0; + if (update_fl) + { + min_val->set_notnull(); + memcpy(min_val->ptr, ptr, pack_length()); + } + return update_fl; + } + /* + Update the value m of the 'max_val' field with the current value v + of this field if force_update is set to TRUE or if v > m. + Return TRUE if the value has been updated. + */ + virtual bool update_max(Field *max_val, bool force_update) + { + bool update_fl= force_update || cmp(ptr, max_val->ptr) > 0; + if (update_fl) + { + max_val->set_notnull(); + memcpy(max_val->ptr, ptr, pack_length()); + } + return update_fl; + } virtual uint decimals() const { return 0; } /* Caller beware: sql_type can change str.Ptr, so check @@ -417,6 +519,8 @@ public: uchar *new_ptr, uchar *new_null_ptr, uint new_null_bit); Field *clone(MEM_ROOT *mem_root, TABLE *new_table); + Field *clone(MEM_ROOT *mem_root, TABLE *new_table, my_ptrdiff_t diff, + bool stat_flag= FALSE); inline void move_field(uchar *ptr_arg,uchar *null_ptr_arg,uchar null_bit_arg) { ptr=ptr_arg; null_ptr=null_ptr_arg; null_bit=null_bit_arg; @@ -1808,6 +1912,10 @@ public: int cmp_binary(const uchar *a,const uchar *b, uint32 max_length=~0L); int key_cmp(const uchar *,const uchar*); int key_cmp(const uchar *str, uint length); + /* Never update the value of min_val for a blob field */ + bool update_min(Field *min_val, bool force_update) { return FALSE; } + /* Never update the value of max_val for a blob field */ + bool update_max(Field *max_val, bool force_update) { return FALSE; } uint32 key_length() const { return 0; } void sort_string(uchar *buff,uint length); uint32 pack_length() const @@ -1825,6 +1933,7 @@ public: { return (uint32) (packlength); } uint row_pack_length() { return pack_length_no_ptr(); } uint32 sort_length() const; + uint32 value_length() { return get_length(); } virtual uint32 max_data_length() const { return (uint32) (((ulonglong) 1 << (packlength*8)) -1); @@ -2092,6 +2201,28 @@ public: { return cmp_binary((uchar *) a, (uchar *) b); } int key_cmp(const uchar *str, uint length); int cmp_offset(uint row_offset); + bool update_min(Field *min_val, bool force_update) + { + longlong val= val_int(); + bool update_fl= force_update || val < min_val->val_int(); + if (update_fl) + { + min_val->set_notnull(); + min_val->store(val, FALSE); + } + return update_fl; + } + bool update_max(Field *max_val, bool force_update) + { + longlong val= val_int(); + bool update_fl= force_update || val > max_val->val_int(); + if (update_fl) + { + max_val->set_notnull(); + max_val->store(val, FALSE); + } + return update_fl; + } 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/item_sum.cc b/sql/item_sum.cc index afe4f34bd05..014d7535c1c 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -636,13 +636,24 @@ void Item_sum::cleanup() @retval > 0 if key1 > key2 */ -static int simple_str_key_cmp(void* arg, uchar* key1, uchar* key2) +int simple_str_key_cmp(void* arg, uchar* key1, uchar* key2) { Field *f= (Field*) arg; return f->cmp(key1, key2); } +C_MODE_START + +int count_distinct_walk(void *elem, element_count count, void *arg) +{ + (*((ulonglong*)arg))++; + return 0; +} + +C_MODE_END + + /** Correctly compare composite keys. @@ -710,13 +721,13 @@ C_MODE_START /* Declarations for auxilary C-callbacks */ -static int simple_raw_key_cmp(void* arg, const void* key1, const void* key2) +int simple_raw_key_cmp(void* arg, const void* key1, const void* key2) { return memcmp(key1, key2, *(uint *) arg); } -static int item_sum_distinct_walk(void *element, element_count num_of_dups, +int item_sum_distinct_walk(void *element, element_count num_of_dups, void *item) { return ((Aggregator_distinct*) (item))->unique_walk_function(element); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 7394ce5c931..4b6df56d9a8 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -446,6 +446,7 @@ ulong opt_replicate_events_marked_for_skip; */ volatile bool mqh_used = 0; my_bool opt_noacl; +my_bool opt_no_stat_tables; my_bool sp_automatic_privileges= 1; ulong opt_binlog_rows_event_max_size; @@ -3685,6 +3686,8 @@ static int init_common_variables() global_system_variables.character_set_results= default_charset_info; global_system_variables.character_set_client= default_charset_info; + global_system_variables.optimizer_use_stat_tables= 0; + if (!(character_set_filesystem= get_charset_by_csname(character_set_filesystem_name, MY_CS_PRIMARY, MYF(MY_WME)))) @@ -4866,6 +4869,11 @@ int mysqld_main(int argc, char **argv) check_performance_schema(); #endif +#if 0 + if (! opt_bootstrap) + init_stat_tables_usage(); +#endif + initialize_information_schema_acl(); execute_ddl_log_recovery(); @@ -6010,6 +6018,7 @@ error: */ struct my_option my_long_options[]= + { {"help", '?', "Display this help and exit.", &opt_help, &opt_help, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, @@ -6315,6 +6324,11 @@ struct my_option my_long_options[]= &opt_noacl, &opt_noacl, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, #endif + {"skip-stat-tables", OPT_SKIP_STAT_TABLES, + "Start without statistical tables. Statistical data on table cardinalities, " + "columns and indexes from these tables become unavailable", + &opt_no_stat_tables, &opt_no_stat_tables, 0, GET_BOOL, NO_ARG, + 0, 0, 0, 0, 0, 0}, {"skip-host-cache", OPT_SKIP_HOST_CACHE, "Don't cache host names.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {"skip-slave-start", 0, @@ -7610,6 +7624,7 @@ mysqld_get_one_option(int optid, break; case OPT_BOOTSTRAP: opt_noacl=opt_bootstrap=1; + opt_no_stat_tables= 1; break; case OPT_SERVER_ID: server_id_supplied = 1; diff --git a/sql/mysqld.h b/sql/mysqld.h index 988584f779a..617ecbb8da2 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -105,6 +105,7 @@ extern char* opt_secure_backup_file_priv; extern size_t opt_secure_backup_file_priv_len; extern my_bool opt_log_slow_admin_statements, opt_log_slow_slave_statements; extern my_bool sp_automatic_privileges, opt_noacl; +extern my_bool opt_no_stat_tables; extern my_bool opt_old_style_user_limits, trust_function_creators; extern uint opt_crash_binlog_innodb; extern char *shared_memory_base_name, *mysqld_unix_port; @@ -395,6 +396,7 @@ enum options_mysqld OPT_SKIP_PRIOR, OPT_SKIP_RESOLVE, OPT_SKIP_STACK_TRACE, + OPT_SKIP_STAT_TABLES, OPT_SKIP_SYMLINKS, OPT_SLOW_QUERY_LOG, OPT_SSL_CA, diff --git a/sql/sql_admin.cc b/sql/sql_admin.cc index 5897d74582a..b33442d66b3 100644 --- a/sql/sql_admin.cc +++ b/sql/sql_admin.cc @@ -307,6 +307,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, Protocol *protocol= thd->protocol; LEX *lex= thd->lex; int result_code; + int compl_result_code; bool need_repair_or_alter= 0; DBUG_ENTER("mysql_admin_table"); DBUG_PRINT("enter", ("extra_open_options: %u", extra_open_options)); @@ -632,9 +633,22 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, } } - DBUG_PRINT("admin", ("calling operator_func '%s'", operator_name)); - result_code = (table->table->file->*operator_func)(thd, check_opt); - DBUG_PRINT("admin", ("operator_func returned: %d", result_code)); + result_code= compl_result_code= 0; + if (operator_func != &handler::ha_analyze || + thd->variables.optimizer_use_stat_tables < 3) + { + DBUG_PRINT("admin", ("calling operator_func '%s'", operator_name)); + result_code = (table->table->file->*operator_func)(thd, check_opt); + DBUG_PRINT("admin", ("operator_func returned: %d", result_code)); + } + + if (operator_func == &handler::ha_analyze && !opt_no_stat_tables && + thd->variables.optimizer_use_stat_tables > 0) + { + if (!(compl_result_code= + collect_statistics_for_table(thd, table->table))) + compl_result_code= update_statistics_for_table(thd, table->table); + } if (result_code == HA_ADMIN_NOT_IMPLEMENTED && need_repair_or_alter) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 9298d3ccb72..63cc075918b 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -4624,6 +4624,13 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, goto end; } + if (!opt_no_stat_tables && thd->variables.optimizer_use_stat_tables > 0) + { + if (tables->table && tables->table->s && + tables->table->s->table_category != TABLE_CATEGORY_SYSTEM) + (void) read_statistics_for_table(thd, tables->table); + } + process_view_routines: /* Again we may need cache all routines used by this view and add @@ -9476,6 +9483,73 @@ open_system_tables_for_read(THD *thd, TABLE_LIST *table_list, /* + Unlock opened tables and open and lock system tables for write. + + SYNOPSIS + unlock_tables_n_open_system_tables_for_write() + thd Thread context. + table_list List of tables to open. + backup Pointer to Open_tables_state instance where + information about currently open tables will be + saved, and from which will be restored when we will + end work with system tables. + + DESCRIPTION + The function first unlocks the opened tables, but do not close them. + Then it opens and locks for write the specified system tables. + + NOTE + The system tables cannot be locked for write without unlocking + the current opened tables. Yet in some cases we still need valid TABLE + structures for these tables to be able to extract data that is to be + written into the system tables. + This function is used when updating the statistical tables. + + RETURN + FALSE Success + TRUE Error +*/ + +bool +unlock_tables_n_open_system_tables_for_write(THD *thd, + TABLE_LIST *table_list, + Open_tables_backup *backup) +{ + Query_tables_list query_tables_list_backup; + LEX *lex= thd->lex; + + DBUG_ENTER("unlock_tables_n_open_system_tables_for_write"); + + mysql_unlock_tables(thd, thd->lock); + thd->lock= 0; + + lex->reset_n_backup_query_tables_list(&query_tables_list_backup); + thd->reset_n_backup_open_tables_state(backup); + + if (open_and_lock_tables(thd, table_list, FALSE, + MYSQL_OPEN_IGNORE_FLUSH | MYSQL_LOCK_IGNORE_TIMEOUT)) + { + lex->restore_backup_query_tables_list(&query_tables_list_backup); + goto error; + } + + for (TABLE_LIST *tables= table_list; tables; tables= tables->next_global) + { + DBUG_ASSERT(tables->table->s->table_category == TABLE_CATEGORY_SYSTEM); + tables->table->use_all_columns(); + } + lex->restore_backup_query_tables_list(&query_tables_list_backup); + + DBUG_RETURN(FALSE); + +error: + close_system_tables(thd, backup); + + DBUG_RETURN(TRUE); +} + + +/* Close system tables, opened with open_system_tables_for_read(). SYNOPSIS diff --git a/sql/sql_base.h b/sql/sql_base.h index e602aba98a9..5b88d53d231 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -275,6 +275,9 @@ bool is_equal(const LEX_STRING *a, const LEX_STRING *b); /* Functions to work with system tables. */ bool open_system_tables_for_read(THD *thd, TABLE_LIST *table_list, Open_tables_backup *backup); +bool unlock_tables_n_open_system_tables_for_write(THD *thd, + TABLE_LIST *table_list, + Open_tables_backup *backup); void close_system_tables(THD *thd, Open_tables_backup *backup); void close_mysql_tables(THD *thd); TABLE *open_system_table_for_update(THD *thd, TABLE_LIST *one_table); @@ -306,6 +309,18 @@ bool check_if_table_exists(THD *thd, TABLE_LIST *table, bool *exists); int update_virtual_fields(THD *thd, TABLE *table, bool ignore_stored= FALSE); int dynamic_column_error_message(enum_dyncol_func_result rc); +/* open_and_lock_tables with optional derived handling */ +int open_and_lock_tables_derived(THD *thd, TABLE_LIST *tables, bool derived); + +int read_statistics_for_table(THD *thd, TABLE *table); +int collect_statistics_for_table(THD *thd, TABLE *table); +int update_statistics_for_table(THD *thd, TABLE *table); + +extern "C" int simple_raw_key_cmp(void* arg, const void* key1, + const void* key2); +extern "C" int count_distinct_walk(void *elem, element_count count, void *arg); +int simple_str_key_cmp(void* arg, uchar* key1, uchar* key2); + extern TABLE *unused_tables; extern Item **not_found_item; extern Field *not_found_field; @@ -472,7 +487,6 @@ open_tables(THD *thd, TABLE_LIST **tables, uint *counter, uint flags) return open_tables(thd, tables, counter, flags, &prelocking_strategy); } - inline TABLE *open_n_lock_single_table(THD *thd, TABLE_LIST *table_l, thr_lock_type lock_type, uint flags) { diff --git a/sql/sql_class.h b/sql/sql_class.h index c6c46975076..24f5a909658 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -501,6 +501,7 @@ typedef struct system_variables ulong net_write_timeout; ulong optimizer_prune_level; ulong optimizer_search_depth; + ulong optimizer_use_stat_tables; ulong preload_buff_size; ulong profiling_history_size; ulong read_buff_size; diff --git a/sql/sql_priv.h b/sql/sql_priv.h index 749e65e0902..acfdf970099 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -327,6 +327,7 @@ enum enum_yes_no_unknown }; #ifdef MYSQL_SERVER + /* External variables */ diff --git a/sql/sql_statistics.cc b/sql/sql_statistics.cc new file mode 100644 index 00000000000..89f3c23d5af --- /dev/null +++ b/sql/sql_statistics.cc @@ -0,0 +1,1588 @@ +/* Copyright (C) 2009 MySQL AB + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ + +/** + @file + + @brief + functions to update persitent statistical tables and to read from them + + @defgroup Query_Optimizer Query Optimizer + @{ +*/ + +#include "sql_base.h" +#include "key.h" +#include "sql_statistics.h" + +/* + The system variable 'optimizer_use_stat_tables' can take one of the + following values: + "never", "complementary", "preferably", "exclusively". + If the values of the variable 'optimizer_use_stat_tables' is set to + "never then any statistical data from the persistent statistical tables + is ignored by the optimizer. + If the value of the variable 'optimizer_use_stat_tables' is set to + "complementary" then a particular statistical characteristic is used + by the optimizer only if the database engine does not provide similar + statistics. For example, 'nulls_ratio' for table columns currently + are not provided by any engine. So optimizer uses this statistical data + from the statistical tables. At the same time it does not use + 'avg_frequency' for any index prefix from the statistical tables since + the a similar statistical characteristic 'records_per_key' can be + requested from the database engine. + If the value the variable 'optimizer_use_stat_tables' is set to + "preferably" the optimizer uses a particular statistical data only if + it can't be found in the statistical data. + If the value of the variable 'optimizer_use_stat_tables' is set to + "exclusively" the optimizer never uses statistical data that can be + returned by the database engine Only statistical data from the + statistical tables is used. + If an ANALYZE command is executed then it results in collecting + statistical data for the tables specified by the command and storing + the collected statistics in the persistent statistical tables only + when the value of the variable 'optimizer_use_stat_tables' is not + equal to "never". +*/ + +/* Currently there are only 3 persistent statistical tables */ +static const uint STATISTICS_TABLES= 3; + +/* + The names of the statistical tables in this list must correspond the + definitions of the tables in the file ../scripts/mysql_system_tables.sql +*/ +static const char *STAT_TABLE_NAME[STATISTICS_TABLES]= +{ + "table_stat", + "column_stat", + "index_stat" +}; + +/** + @details + The function builds a list of TABLE_LIST elements for system statistical + tables using array of TABLE_LIST passed as a parameter. + The lock type of each element is set to TL_READ if for_write = FALSE, + otherwise it is set to TL_WRITE. +*/ +inline void init_table_list_for_stat_tables(TABLE_LIST *tables, bool for_write) +{ + uint i; + + bzero((char *) &tables[0], sizeof(TABLE_LIST) * STATISTICS_TABLES); + + for (i= 0; i < STATISTICS_TABLES; i++) + { + tables[i].db= (char*) "mysql"; + tables[i].table_name= (char *) STAT_TABLE_NAME[i]; + tables[i].alias= tables[i].table_name; + tables[i].lock_type= for_write ? TL_WRITE : TL_READ; + if (i < STATISTICS_TABLES - 1) + tables[i].next_global= tables[i].next_local= + tables[i].next_name_resolution_table= &tables[i+1]; + } +} + +/** + @details + The function sets null bits stored in the bitmap table_field->write_stat + for all statistical values collected for a column. +*/ +inline void set_nulls_for_write_column_stat_values(Field *table_field) +{ + table_field->write_stat.column_stat_nulls= + ((1 << (COLUMN_STAT_AVG_FREQUENCY-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1); +} + +/** + @details + The function sets null bits stored in the bitmap table_field->read_stat + for all statistical values collected for a column. +*/ +inline void set_nulls_for_read_column_stat_values(Field *table_field) +{ + table_field->read_stat.column_stat_nulls= + ((1 << (COLUMN_STAT_AVG_FREQUENCY-COLUMN_STAT_COLUMN_NAME))-1) << + (COLUMN_STAT_COLUMN_NAME+1); +} + +/** + @details + The function removes the null bit stored in the bitmap + table_field->write_stat for the statistical value collected + on the statistical column number stat_field_no. +*/ +inline void set_not_null_for_write_column_stat_value(Field *table_field, + uint stat_field_no) +{ + table_field->write_stat.column_stat_nulls&= ~(1 << stat_field_no); +} + +/** + @details + The function removes the null bit stored in the bitmap + table_field->read_stat for the statistical value collected + on the statistical column number stat_field_no. +*/ +inline void set_not_null_for_read_column_stat_value(Field *table_field, + uint stat_field_no) +{ + table_field->read_stat.column_stat_nulls&= ~(1 << stat_field_no); +} + +/** + @details + The function checks the null bit stored in the bitmap + table_field->read_stat for the statistical value collected + on the statistical column number stat_field_no. +*/ +inline bool check_null_for_write_column_stat_value(Field *table_field, + uint stat_field_no) +{ + return table_field->write_stat.column_stat_nulls & (1 << stat_field_no); +} + +/* + Stat_table is the base class for classes Table_stat, Column_stat and + Index_stat. The methods of these classes allow us table to read + statistical data from statistical tables and write collected statistical + data into statistical data. Objects of the classes Table_stat, Column_stat + and Index stat are used for reading/writing statistics from/into + persistent tables table_stat, column_stat and index_stat correspondingly. + These tables are stored in the system database 'mysql'. + + Statistics is read and written always for a given database table t. When + an object of any of these classes is created a pointer to the TABLE + structure for this database table is passed as a parameter to the constructor + of the object. The other parameter is a pointer to the TABLE structure for + the corresponding statistical table st. So construction of an object to + read/write statistical data on table t from/into statistical table st + requires both table t and st to be opened. + + Reading/writing statistical data from/into a statistical table is always + performed by key. At the moment there is only one key defined for each + statistical table and this key is primary. + The primary key for the table table_stat is built as (db_name, table_name). + The primary key for the table column_stat is built as (db_name, table_name, + column_name). + The primary key for the table index_stat is built as (db_name, table_name, + index_name, prefix_arity). + + Reading statistical data from a statistical table is performed by the + following pattern. First a table dependent method sets the values of the + the fields that comprise the lookup key. Then an implementation of the + method get_stat_values() declared in Stat_table as a pure virtual method + finds the row from the statistical table by the set key. If the row is + found the values of statistical fields are read from this row and are + distributed in the internal structures. + + Let's assume the statistical data is read for table t from database db. + + When statistical data is searched in the table table_stat first + Table_stat::set_key_fields() should set the fields of db_name and + table_name. Then get_stat_values looks for a row by the set key value, + and, if the row is found, reads the value from the column + table_stat.cardinality into the field read_stat.cardinality of the TABLE + structure for table t and sets the value of read_stat.cardinality_is_null + from this structure to FALSE. If the value of the 'cardinality' column + in the row is null or if no row is found read_stat.cardinality_is_null + is set to TRUE. + + When statistical data is searched in the table column_stat first + Column_stat::set_key_fields() should set the fields of db_name, table_name + and column_name with column_name taken out of the only parameter f of the + Field* type passed to this method. After this get_stat_values looks + for a row by the set key value. If the row is found the values of statistical + data columns min_value, max_value, nulls_ratio, avg_length, avg_frequency + are read into internal structures. Values of nulls_ratio, avg_length, + avg_frequency are read into the corresponding fields of the read_stat + structure from the Field object f, while values from min_value and max_value + are copied into the min_value and max_value record buffers attached to the + TABLE structure for table t. + If the value of a statistical column in the found row is null, then the + corresponding flag in the f->read_stat.column_stat_nulls bitmap is set off. + Otherwise the flag is set on. If no row is found for the column the all flags + in f->column_stat_nulls are set off. + + When statistical data is searched in the table index_stat first + Index_stat::set_key_fields() has to be called to set the fields of db_name, + table_name, index_name and prefix_arity. The value of index_name is extracted + from the first parameter key_info of the KEY* type passed to the method. + This parameter specifies the index of interest idx. The second parameter + passed to the method specifies the arity k of the index prefix for which + statistical data is to be read. E.g. if the index idx consists of 3 + components (p1,p2,p3) the table index_stat usually will contain 3 rows for + this index: the first - for the prefix (p1), the second - for the prefix + (p1,p2), and the third - for the the prefix (p1,p2,p3). After the key fields + has been set a call of get_stat_value looks for a row by the set key value. + If the row is found and the value of the avg_frequency column is not null + then this value is assigned to key_info->read_stat.avg_frequency[k]. + Otherwise 0 is assigned to this element. + + The method Stat_table::update_stat is used to write statistical data + collected in the internal structures into a statistical table st. + It is assumed that before any invocation of this method a call of the + function st.set_key_fields has set the values of the primary key fields + that serve to locate the row from the statistical table st where the + the colected statistical data from internal structures are to be written + to. The statistical data is written from the counterparts of the + statistical fields of internal structures into which it would be read + by the functions get_stat_values. The counterpart fields are used + only when statistics is collected + When updating/inserting a row from the statistical table st the method + Stat_table::update_stat calls the implementation of the pure virtual + method store_field_values to transfer statistical data from the fields + of internal structures to the fields of record buffer used for updates + of the statistical table st. +*/ + +class Stat_table +{ +private: + /* Handler used for the retrieval of the statistical table stat_table */ + handler *stat_file; + + KEY *stat_key_info; /* Structure for the index to access stat_table */ + uint stat_key_length; /* Length of the key to access stat_table */ + uchar *record[2]; /* Record buffers used to access/update stat_table */ + uint stat_key_idx; /* The number of the key to access stat_table */ + +protected: + /* Statistical table to read statistics from or to update */ + TABLE *stat_table; + + /* Table for which statistical data is read / updated */ + TABLE *table; + char *db_name; /* Name of the database containing 'table' */ + uint db_name_len; /* Length of db_name */ + char *table_name; /* Name of the table 'table' */ + uint table_name_len; /* Name of table_name */ + +public: + + /* + @details + This constructor has to be called by any constructor of the derived + classes. The constructor 'tunes' the private and protected members of + the constructed object to the statistical table 'stat_table' with the + statistical data of our interest and to the table 'tab' for which this + statistics has been collected. + */ + Stat_table(TABLE *stat, TABLE *tab) :stat_table(stat), table(tab) + { + stat_file= stat_table->file; + /* Currently any statistical table has only one key */ + stat_key_idx= 0; + stat_key_info= &stat_table->key_info[stat_key_idx]; + stat_key_length= stat_key_info->key_length; + record[0]= stat_table->record[0]; + record[1]= stat_table->record[1]; + db_name= table->s->db.str; + db_name_len= table->s->db.length; + table_name= table->s->table_name.str; + table_name_len= table->s->table_name.length; + } + + virtual ~Stat_table() {} + + /* + @brief + Store statistical data into fields of the statistical table + + @details + This is a purely virtual method. + The implementation for any derived class shall put the appropriate + statistical data into the corresponding fields of stat_table. + + @note + The method is called by the update_stat function. + */ + virtual void store_stat_fields()= 0; + + /* + @brief + Read statistical data from fields of the statistical table + + @details + This is a purely virtual method. + The implementation for any derived read shall read the appropriate + statistical data from the corresponding fields of stat_table. + */ + virtual void get_stat_values()= 0; + + /* + @breif + Find a record by key in the statistical table + + @details + The function looks for a record in stat_table by its primary key. + It assumes that the key fields have been already stored in the record + buffer of stat_table. + + @retval + FALSE the record is not found + @retval + TRUE the record is found + */ + bool find_stat() + { + uchar key[MAX_KEY_LENGTH]; + key_copy(key, record[0], stat_key_info, stat_key_length); + return !stat_file->ha_index_read_idx_map(record[0], stat_key_idx, key, + HA_WHOLE_KEY, HA_READ_KEY_EXACT); + } + + /* + @breif + Update/insert a record in the statistical table with new statistics + + @details + The function first looks for a record by its primary key in the statistical + table stat_table. If the record is found the function updates statistical + fields of the records. The data for these fields are taken from internal + structures containing info on the table 'table'. If the record is not + found the function inserts a new record with the primary key set to the + search key and the statistical data taken from the internal structures. + The function assumes that the key fields have been already stored in + the record buffer of stat_table. + + @retval + FALSE success with the update/insert of the record + @retval + TRUE failure with the update/insert of the record + + @note + The function calls the virtual method store_stat_fields to populate the + statistical fields of the updated/inserted row with new statistics. + */ + bool update_stat() + { + int err; + if (find_stat()) + { + store_record(stat_table, record[1]); + store_stat_fields(); + if ((err= stat_file->ha_update_row(record[1], record[0])) && + err != HA_ERR_RECORD_IS_THE_SAME) + return TRUE; + } + else + { + store_stat_fields(); + if ((err= stat_file->ha_write_row(record[0]))) + return TRUE; + } + return FALSE; + } +}; + + +/* + An object of the class Table_stat is created to read statistical + data on tables from the statistical table table_stat or to update + table_stat with such statistical data. + Rows from the statistical table are read and updated always by + primary key. +*/ + +class Table_stat: public Stat_table +{ +private: + Field *db_name_field; /* Field for the column table_stat.db_name */ + Field *table_name_field; /* Field for the column table_stat.table_name */ + +public: + + /* + @details + The constructor 'tunes' the private and protected members of the + constructed object for the statistical table table_stat to read/update + statistics on table 'tab'. The TABLE structure for the table table_stat + must be passed as a value for the parameter 'stat'. + */ + Table_stat(TABLE *stat, TABLE *tab) :Stat_table(stat, tab) + { + db_name_field= stat_table->field[TABLE_STAT_DB_NAME]; + table_name_field= stat_table->field[TABLE_STAT_TABLE_NAME]; + } + + /* + @brief + Set the key fields for the statistical table table_stat + + @details + The function sets the values of the fields db_name and table_name + in the record buffer for the statistical table table_stat. + These fields comprise the primary key for the table. + + @note + The function is supposed to be called before any use of the + method find_stat for an object of the Table_stat class. + */ + void set_key_fields() + { + db_name_field->store(db_name, db_name_len, &my_charset_bin); + table_name_field->store(table_name, table_name_len, &my_charset_bin); + } + + /* + @brief + Store statistical data into statistical fields of table_stat + + @details + This implementation of a purely virtual method sets the value of the + column 'cardinality' of the statistical table table_stat according to + the value of the flag write_stat.cardinality_is_null and the value of + the field write_stat.cardinality' from the TABLE structure for 'table'. + */ + void store_stat_fields() + { + Field *stat_field= stat_table->field[TABLE_STAT_CARDINALITY]; + if (table->write_stat.cardinality_is_null) + stat_field->set_null(); + else + { + stat_field->set_notnull(); + stat_field->store(table->write_stat.cardinality); + } + } + + /* + @brief + Read statistical data from statistical fields of table_stat + + @details + This implementation of a purely virtual method first looks for a record + the statistical table table_stat by its primary key set the record + buffer with the help of Table_stat::set_key_fields. Then, if the row is + found the function reads the value of the column 'cardinality' of the table + table_stat and sets the value of the flag read_stat.cardinality_is_null + and the value of the field read_stat.cardinality' from the TABLE structure + for 'table' accordingly. + */ + void get_stat_values() + { + table->read_stat.cardinality_is_null= TRUE; + table->read_stat.cardinality= 0; + if (find_stat()) + { + Field *stat_field= stat_table->field[TABLE_STAT_CARDINALITY]; + if (!stat_field->is_null()) + { + table->read_stat.cardinality_is_null= FALSE; + table->read_stat.cardinality= stat_field->val_int(); + } + } + } + +}; + + +/* + An object of the class Column_stat is created to read statistical data + on table columns from the statistical table column_stat or to update + column_stat with such statistical data. + Rows from the statistical table are read and updated always by + primary key. +*/ + +class Column_stat: public Stat_table +{ +private: + Field *db_name_field; /* Field for the column column_stat.db_name */ + Field *table_name_field; /* Field for the column column_stat.table_name */ + Field *column_name_field; /* Field for the column column_stat.column_name */ + + Field *table_field; /* Field from 'table' to read /update statistics on */ + +public: + + /* + @details + The constructor 'tunes' the private and protected members of the + constructed object for the statistical table column_stat to read/update + statistics on fields of the table 'tab'. The TABLE structure for the table + column_stat must be passed as a value for the parameter 'stat'. + */ + Column_stat(TABLE *stat, TABLE *tab) :Stat_table(stat, tab) + { + db_name_field= stat_table->field[COLUMN_STAT_DB_NAME]; + table_name_field= stat_table->field[COLUMN_STAT_TABLE_NAME]; + column_name_field= stat_table->field[COLUMN_STAT_COLUMN_NAME]; + } + + /* + @brief + Set the key fields for the statistical table column_stat + + @param + column Field for the 'table' column to read/update statistics on + + @details + The function sets the values of the fields db_name, table_name and + column_name in the record buffer for the statistical table column_stat. + These fields comprise the primary key for the table. + + @note + The function is supposed to be called before any use of the + method find_stat for an object of the Column_stat class. + */ + void set_key_fields(Field *column) + { + db_name_field->store(db_name, db_name_len, &my_charset_bin); + table_name_field->store(table_name, table_name_len, &my_charset_bin); + table_field= column; + const char *column_name= column->field_name; + column_name_field->store(column_name, strlen(column_name), &my_charset_bin); + } + + /* + @brief + Store statistical data into statistical fields of column_stat + + @details + This implementation of a purely virtual method sets the value of the + columns 'min_value', 'max_value', 'nulls_ratio', 'avg_length' and + 'avg_frequency' of the stistical table columns_stat according to the + contents of the bitmap write_stat.column_stat_nulls and the values + of the fields min_value, max_value, nulls_ratio, avg_length and + avg_frequency of the structure write_stat from the Field structure + for the field 'table_field'. + The value of the k-th column in the table columns_stat is set to NULL + if the k-th bit in the bitmap 'column_stat_nulls' is set to 1. + + @note + A value from the field min_value/max_value is always converted + into a utf8 string. If the length of the column 'min_value'/'max_value' + is less than the length of the string the string is trimmed to fit the + length of the column. + */ + void store_stat_fields() + { + 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++) + { + Field *stat_field= stat_table->field[i]; + if (check_null_for_write_column_stat_value(table_field, i)) + stat_field->set_null(); + else + { + stat_field->set_notnull(); + switch (i) { + case COLUMN_STAT_MIN_VALUE: + if (table_field->type() == MYSQL_TYPE_BIT) + stat_field->store(table_field->write_stat.min_value->val_int()); + else + { + table_field->write_stat.min_value->val_str(&val); + stat_field->store(val.ptr(), val.length(), &my_charset_utf8_bin); + } + break; + case COLUMN_STAT_MAX_VALUE: + if (table_field->type() == MYSQL_TYPE_BIT) + stat_field->store(table_field->write_stat.max_value->val_int()); + else + { + table_field->write_stat.max_value->val_str(&val); + stat_field->store(val.ptr(), val.length(), &my_charset_utf8_bin); + } + break; + case COLUMN_STAT_NULLS_RATIO: + stat_field->store(table_field->write_stat.nulls_ratio); + break; + case COLUMN_STAT_AVG_LENGTH: + stat_field->store(table_field->write_stat.avg_length); + break; + case COLUMN_STAT_AVG_FREQUENCY: + stat_field->store(table_field->write_stat.avg_frequency); + break; + } + } + } + } + + /* + @brief + Read statistical data from statistical fields of column_stat + + @details + This implementation of a purely virtual method first looks for a record + the statistical table column_stat by its primary key set the record + buffer with the help of Column_stat::set_key_fields. Then, if the row is + found, the function reads the values of the columns 'min_value', + 'max_value', 'nulls_ratio', 'avg_length' and 'avg_frequency' of the + table column_stat and sets accordingly the value of the bitmap + read_stat.column_stat_nulls' and the values of the fields min_value, + max_value, nulls_ratio, avg_length and avg_frequency of the structure + read_stat from the Field structure for the field 'table_field'. + */ + void get_stat_values() + { + set_nulls_for_read_column_stat_values(table_field); + + if (table_field->read_stat.min_value) + table_field->read_stat.min_value->set_null(); + if (table_field->read_stat.max_value) + table_field->read_stat.max_value->set_null(); + + if (find_stat()) + { + 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++) + { + Field *stat_field= stat_table->field[i]; + + if (!stat_field->is_null() && + (i > COLUMN_STAT_MAX_VALUE || + (i == COLUMN_STAT_MIN_VALUE && table_field->read_stat.min_value) || + (i == COLUMN_STAT_MAX_VALUE && table_field->read_stat.max_value))) + { + set_not_null_for_read_column_stat_value(table_field, i); + + switch (i) { + case COLUMN_STAT_MIN_VALUE: + stat_field->val_str(&val); + table_field->read_stat.min_value->store(val.ptr(), val.length(), + &my_charset_utf8_bin); + break; + case COLUMN_STAT_MAX_VALUE: + stat_field->val_str(&val); + table_field->read_stat.max_value->store(val.ptr(), val.length(), + &my_charset_utf8_bin); + break; + case COLUMN_STAT_NULLS_RATIO: + table_field->read_stat.nulls_ratio= stat_field->val_real(); + break; + case COLUMN_STAT_AVG_LENGTH: + table_field->read_stat.avg_length= stat_field->val_real(); + break; + case COLUMN_STAT_AVG_FREQUENCY: + table_field->read_stat.avg_frequency= stat_field->val_real(); + break; + } + } + } + } + } + +}; + + +/* + An object of the class Index_stat is created to read statistical + data on index prefixes from the statistical table index_stat or + to update index_stat with such statistical data. + Rows from the statistical table are read and updated always by + primary key. +*/ + +class Index_stat: public Stat_table +{ +private: + Field *db_name_field; /* Field for the column index_stat.db_name */ + Field *table_name_field; /* Field for the column index_stat.table_name */ + Field *index_name_field; /* Field for the column index_stat.table_name */ + Field *prefix_arity_field; /* Field for the column index_stat.prefix_arity */ + + KEY *table_key_info; /* Info on the index to read/update statistics on */ + uint prefix_arity; /* Number of components of the index prefix of interest */ + +public: + + /* + @details + The constructor 'tunes' the private and protected members of the + constructed object for the statistical table index_stat to read/update + statistics on prefixes of different indexes of the table 'tab'. + The TABLE structure for the table index_stat must be passed as a value + for the parameter 'stat'. + */ + Index_stat(TABLE *stat, TABLE *tab) :Stat_table(stat, tab) + { + db_name_field= stat_table->field[INDEX_STAT_DB_NAME]; + table_name_field= stat_table->field[INDEX_STAT_TABLE_NAME]; + index_name_field= stat_table->field[INDEX_STAT_INDEX_NAME]; + prefix_arity_field= stat_table->field[INDEX_STAT_PREFIX_ARITY]; + + } + + /* + @brief + Set the key fields for the statistical table index_stat + + @param + index_info Info for the index of 'table' to read/update statistics on + @param + index_prefix_arity Number of components in the index prefix of interest + + + @details + The function sets the values of the fields db_name, table_name and + index_name, prefix_arity in the record buffer for the statistical + table index_stat. These fields comprise the primary key for the table. + + @note + The function is supposed to be called before any use of the + method find_stat for an object of the Index_stat class. + */ + void set_key_fields(KEY *index_info, uint index_prefix_arity) + { + db_name_field->store(db_name, db_name_len, &my_charset_bin); + table_name_field->store(table_name, table_name_len, &my_charset_bin); + table_key_info= index_info; + char *index_name= index_info->name; + index_name_field->store(index_name, strlen(index_name), &my_charset_bin); + prefix_arity= index_prefix_arity; + prefix_arity_field->store(index_prefix_arity, TRUE); + } + + /* + @brief + Store statistical data into statistical fields of tableindex_stat + + @details + This implementation of a purely virtual method sets the value of the + column 'avg_frequency' of the statistical table index_stat according to + the value of write_stat.avg_frequency[Index_stat::prefix_arity] + from the KEY_INFO structure 'table_key_info'. + If the value of write_stat. avg_frequency[Index_stat::prefix_arity] is + equal to 0, the value of the column is set to NULL. + */ + void store_stat_fields() + { + Field *stat_field= stat_table->field[INDEX_STAT_AVG_FREQUENCY]; + double avg_frequency= + table_key_info->write_stat.avg_frequency[prefix_arity-1]; + if (avg_frequency == 0) + stat_field->set_null(); + else + { + stat_field->set_notnull(); + stat_field->store(avg_frequency); + } + } + + /* + @brief + Read statistical data from statistical fields of index_stat + + @details + This implementation of a purely virtual method first looks for a record the + statistical table index_stat by its primary key set the record buffer with + the help of Index_stat::set_key_fields. If the row is found the function + reads the value of the column 'avg_freguency' of the table index_stat and + sets the value of read_stat.avg_frequency[Index_stat::prefix_arity] + from the KEY_INFO structure 'table_key_info' accordingly. If the value of + the column is NULL, read_stat.avg_frequency[Index_stat::prefix_arity] is + set to 0. Otherwise, read_stat.avg_frequency[Index_stat::prefix_arity] is + set to the value of the column. + */ + void get_stat_values() + { + double avg_frequency= 0; + if(find_stat()) + { + Field *stat_field= stat_table->field[INDEX_STAT_AVG_FREQUENCY]; + if (!stat_field->is_null()) + avg_frequency= stat_field->val_real(); + } + table_key_info->read_stat.avg_frequency[prefix_arity-1]= avg_frequency; + } + +}; + + +/* + The class Count_distinct_field is a helper class used to calculate + the number of distinct values for a column. The class employs the + Unique class for this purpose. + The class Count_distinct_field is used only by the function + collect_statistics_from_table to calculate the values for + column avg_frequency of the statistical table column_stat. +*/ + +class Count_distinct_field: public Sql_alloc +{ +protected: + + /* Field for which the number of distinct values is to be find out */ + Field *table_field; + Unique *tree; /* The helper object to contain distinct values */ + uint tree_key_length; /* The length of the keys for the elements of 'tree */ + +public: + + /* + @param + field Field for which the number of distinct values is + to be find out + @param + max_heap_table_size The linit for the memory used by the RB tree container + of the constructed Unique object 'tree' + + @details + The constructor sets the values of 'table_field' and 'tree_key_length', + and then calls the 'new' operation to create a Unique object for 'tree'. + The type of 'field' and the value max_heap_table_size of determine the set + of the parameters to be passed to the constructor of the Unique object. + */ + 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_key_length, max_heap_table_size); + } + + virtual ~Count_distinct_field() + { + delete tree; + tree= NULL; + } + + /* + @brief + Check whether the Unique object tree has been succesfully created + */ + bool exists() + { + return (tree != NULL); + } + + /* + @brief + Add the value of 'field' to the container of the Unique object 'tree' + */ + virtual bool add() + { + return tree->unique_add(table_field->ptr); + } + + /* + @brief + Calculate the number of elements accumulated in the container of 'tree' + */ + ulonglong get_value() + { + ulonglong count; + if (tree->elements == 0) + return (ulonglong) tree->elements_in_tree(); + count= 0; + tree->walk(count_distinct_walk, (void*) &count); + return count; + } +}; + + +/* + 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. + The class provides a different implementation for the method add +*/ + +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) {} + bool add() + { + longlong val= table_field->val_int(); + return tree->unique_add(&val); + } +}; + + +/* + The class Index_prefix_calc is a helper class used to calculate the values + for the column 'avg_frequency' of the statistical table index_stat. + For any table t from the database db and any k-component prefix of the + index i for this table the row from index_stat with the primary key + (db,t,i,k) must contain in the column 'avg_frequency' either NULL or + the number that is the ratio of N and V, where N is the number of index + entries without NULL values in the first k components of the index i, + and V is the number of distinct tuples composed of the first k components + encountered among these index entries. + Currently the objects of this class are used only by the function + collect_statistics_for_index. +*/ + +class Index_prefix_calc: public Sql_alloc +{ +private: + /* Table containing index specified by index_info */ + TABLE *index_table; + /* Info for the index i for whose prefix 'avg_frequency' is calculated */ + KEY *index_info; + /* The maximum number of the components in the prefixes of interest */ + uint prefixes; + bool empty; + + /* This structure is created for every k components of the index i */ + class Prefix_calc_state + { + public: + /* + The number of the scanned index entries without nulls + in the first k components + */ + ulonglong entry_count; + /* + The number if the scanned index entries without nulls with + the last encountered k-component prefix + */ + ulonglong prefix_count; + /* The values of the last encoutered k-component prefix */ + Cached_item *last_prefix; + }; + + /* + Array of structures used to calculate 'avg_frequency' for different + prefixes of the index i + */ + Prefix_calc_state *calc_state; + +public: + Index_prefix_calc(TABLE *table, KEY *key_info) + : index_table(table), index_info(key_info) + { + uint i; + Prefix_calc_state *state; + uint key_parts= key_info->key_parts; + empty= TRUE; + prefixes= 0; + if ((calc_state= + (Prefix_calc_state *) sql_alloc(sizeof(Prefix_calc_state)*key_parts))) + { + uint keyno= key_info-table->key_info; + for (i= 0, state= calc_state; i < key_parts; i++, state++) + { + /* + Do not consider prefixes containing a component that is only part + of the field. This limitation is set to avoid fetching data when + calculating the values of 'avg_frequency' for prefixes. + */ + if (!key_info->key_part[i].field->part_of_key.is_set(keyno)) + break; + + if (!(state->last_prefix= + new Cached_item_field(key_info->key_part[i].field))) + break; + state->entry_count= state->prefix_count= 0; + prefixes++; + } + } + } + + /* + @breif + Change the elements of calc_state after reading the next index entry + + @details + This function is to be called at the index scan each time the next + index entry has been read into the record buffer. + For each of the index prefixes the function checks whether nulls + are encountered in any of the k components of the prefix. + If this is not the case the value of calc_state[k-1].entry_count + is incremented by 1. Then the function checks whether the value of + any of these k components has changed. If so, the value of + calc_state[k-1].prefix_count is incremented by 1. + */ + void add() + { + uint i; + Prefix_calc_state *state; + uint first_changed= prefixes; + for (i= prefixes, state= calc_state+prefixes-1; i; i--, state--) + { + if (state->last_prefix->cmp()) + first_changed= i-1; + } + if (empty) + { + first_changed= 0; + empty= FALSE; + } + for (i= 0, state= calc_state; i < prefixes; i++, state++) + { + if (state->last_prefix->null_value) + break; + if (i >= first_changed) + state->prefix_count++; + state->entry_count++; + } + } + + /* + @brief + Calculate the values of avg_frequency for all prefixes of an index + + @details + This function is to be called after the index scan to count the number + of distinct index prefixes has been done. The function calculates + the value of avg_frequency for the index prefix with k components + as calc_state[k-1].entry_count/calc_state[k-1].prefix_count. + If calc_state[k-1].prefix_count happens to be 0, the value of + avg_frequency[k-1] is set to 0, i.e. is considered as unknown. + */ + void get_avg_frequency() + { + uint i; + Prefix_calc_state *state; + for (i= 0, state= calc_state; i < prefixes; i++, state++) + { + if (i < prefixes) + { + index_info->write_stat.avg_frequency[i]= + state->prefix_count == 0 ? 0 : + (double) state->entry_count / state->prefix_count; + } + } + } +}; + + +/** + @brief + Create fields for min/max values to collect/read column statistics + + @param + table Table the fields are created for + @param + for_write Those fields are created that are used to collect statistics + + @note + The function first allocates record buffers to store min/max values + for 'table's fields. Then for each table field f it creates Field structures + that points to these buffers rather that to the record buffer as the + Field object for f does. The pointers of the created fields are placed + either in the write_stat or in the read_stat structure of the Field + object for f, depending on the value of the 'for_write' parameter. + + @note + The buffers allocated when min/max values are used to read statistics + from the persistent statistical tables differ from those buffers that + are used when statistics on min/max values for column is collected. + The same is true for the fields created for min/max values. +*/ + +static +void create_min_max_stistical_fields(TABLE *table, bool for_write) +{ + Field *table_field; + Field **field_ptr; + uchar *record; + uint rec_buff_length= table->s->rec_buff_length; + + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + table_field= *field_ptr; + if (for_write) + table_field->write_stat.max_value= + table_field->write_stat.min_value= NULL; + else + table_field->read_stat.max_value= + table_field->read_stat.min_value= NULL; + } + + if ((record= (uchar *) alloc_root(&table->mem_root, 2*rec_buff_length))) + { + for (uint i=0; i < 2; i++, record+= rec_buff_length) + { + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + Field *fld; + table_field= *field_ptr; + my_ptrdiff_t diff= record-table->record[0]; + if (!(fld= table_field->clone(&table->mem_root, table, diff, TRUE))) + continue; + if (i == 0) + { + if (for_write) + table_field->write_stat.min_value= fld; + else + table_field->read_stat.min_value= fld; + } + else + { + if (for_write) + table_field->write_stat.max_value= fld; + else + table_field->read_stat.max_value= fld; + } + } + } + } +} + + +/** + @brief + Collect statistical data on an index + + @param + table The table the index belongs to + index The number of this index in the table + + @details + The function collects the value of 'avg_frequency' for the prefixes + on an index from 'table'. The index is specified by its number. + If the scan is successful the calculated statistics is saved in the + elements of the array write_stat.avg_frequency of the KEY_INFO structure + for the index. The statistics for the prefix with k components is saved + in the element number k-1. + + @retval + 0 If the statistics has been successfully collected + @retval + 1 Otherwise + + @note + The function collects statistics for the index prefixes for one index + scan during which no data is fetched from the table records. That's why + statistical data for prefixes that contain part of a field is not + collected. + The function employs an object of the helper class Index_prefix_calc to + count for each index prefix the number of index entries without nulls and + the number of distinct entries among them. + +*/ + +static +int collect_statistics_for_index(TABLE *table, uint index) +{ + int rc= 0; + KEY *key_info= &table->key_info[index]; + ha_rows rows= 0; + Index_prefix_calc index_prefix_calc(table, key_info); + DBUG_ENTER("collect_statistics_for_index"); + + table->key_read= 1; + table->file->extra(HA_EXTRA_KEYREAD); + + table->file->ha_index_init(index, TRUE); + rc= table->file->ha_index_first(table->record[0]); + while (rc != HA_ERR_END_OF_FILE) + { + if (rc) + break; + rows++; + index_prefix_calc.add(); + rc= table->file->ha_index_next(table->record[0]); + } + table->key_read= 0; + table->file->ha_index_end(); + + rc= (rc == HA_ERR_END_OF_FILE) ? 0 : 1; + + if (!rc) + index_prefix_calc.get_avg_frequency(); + + DBUG_RETURN(rc); +} + + +/** + @brief + Collect statistical data for a table + + @param + thd The thread handle + @param + table The table to collect statistics on + + @details + The function collects data for various statistical characteristics on + the table 'table'. These data is saved in the internal fields that could + be reached from 'table'. The data is prepared to be saved in the persistent + statistical table by the function update_statistics_for_table. + The collected statistical values are not placed in the same fields that + keep the statistical data used by the optimizer. Therefore, at any time, + there is no collision between the statistics being collected and the one + used by the optimizer to look for optimal query execution plans for other + clients. + + @retval + 0 If the statistics has been successfully collected + @retval + 1 Otherwise + + @note + The function first collects statistical data for statistical characteristics + to be saved in the statistical tables table_stat and column_stat. To do this + it performs a full table scan of 'table'. At this scan the function collects + statistics on each column of the table and count the total number of the + scanned rows. To calculate the value of 'avg_frequency' for a column the + function constructs an object of the helper class Count_distinct_field + (or its derivation). Currently this class cannot count the number of + distinct values for blob columns. So the value of 'avg_frequency' for + blob columns is always null. + After the full table scan the function calls collect_statistics_for_index + for each table index. The latter performs full index scan for each index. + + @note + Currently the statistical data is collected indiscriminately for all + columns/indexes of 'table', for all statistical characteristics. + TODO. Collect only specified statistical characteristics for specified + columns/indexes. + + @note + Currently the process of collecting statistical data is not optimized. + For example, 'avg_frequency' for a column could be copied from the + 'avg_frequency' collected for an index if this column is used as the + first component of the index. Min and min values for this column could + be extracted from the index as well. +*/ + +int collect_statistics_for_table(THD *thd, TABLE *table) +{ + int rc; + Field **field_ptr; + Field *table_field; + ha_rows rows= 0; + handler *file=table->file; + + DBUG_ENTER("collect_statistics_for_table"); + + table->write_stat.cardinality_is_null= TRUE; + table->write_stat.cardinality= 0; + + create_min_max_stistical_fields(table, TRUE); + + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + table_field= *field_ptr; + uint max_heap_table_size= thd->variables.max_heap_table_size; + set_nulls_for_write_column_stat_values(table_field); + table_field->nulls= 0; + table_field->column_total_length= 0; + if (table_field->flags & BLOB_FLAG) + table_field->count_distinct= NULL; + else + { + table_field->count_distinct= + table_field->type() == MYSQL_TYPE_BIT ? + new Count_distinct_field_bit(table_field, max_heap_table_size) : + new Count_distinct_field(table_field, max_heap_table_size); + } + if (table_field->count_distinct && + !table_field->count_distinct->exists()) + table_field->count_distinct= NULL; + } + + bitmap_set_all(table->read_set); + + /* Perform a full table scan to collect statistics on 'table's columns */ + if (!(rc= file->ha_rnd_init(FALSE))) + { + while ((rc= file->ha_rnd_next(table->record[0])) != HA_ERR_END_OF_FILE) + { + if (rc) + break; + + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + table_field= *field_ptr; + if (table_field->is_null()) + table_field->nulls++; + else + { + table_field->column_total_length+= table_field->value_length(); + if (table_field->write_stat.min_value && + table_field->update_min(table_field->write_stat.min_value, + rows == table_field->nulls)) + set_not_null_for_write_column_stat_value(table_field, + COLUMN_STAT_MIN_VALUE); + if (table_field->write_stat.max_value && + table_field->update_max(table_field->write_stat.max_value, + rows == table_field->nulls)) + set_not_null_for_write_column_stat_value(table_field, + COLUMN_STAT_MAX_VALUE); + if (table_field->count_distinct) + table_field->count_distinct->add(); + } + } + rows++; + } + file->ha_rnd_end(); + } + rc= rc == HA_ERR_END_OF_FILE ? 0 : 1; + + /* + Calculate values for all statistical characteristics on columns and + and for each field f of 'table' save them in the write_stat structure + from the Field object for f. + */ + if (!rc) + { + table->write_stat.cardinality_is_null= FALSE; + table->write_stat.cardinality= rows; + + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + table_field= *field_ptr; + table_field->write_stat.nulls_ratio= (double) table_field->nulls/rows; + table_field->write_stat.avg_length= + (double) table_field->column_total_length / (rows-table_field->nulls); + if (table_field->count_distinct) + { + table_field->write_stat.avg_frequency= + (double) (rows-table_field->nulls) / + table_field->count_distinct->get_value(); + set_not_null_for_write_column_stat_value(table_field, + COLUMN_STAT_AVG_FREQUENCY); + delete table_field->count_distinct; + table_field->count_distinct= NULL; + } + + set_not_null_for_write_column_stat_value(table_field, + COLUMN_STAT_NULLS_RATIO); + set_not_null_for_write_column_stat_value(table_field, + COLUMN_STAT_AVG_LENGTH); + } + } + + if (!rc) + { + uint keys= table->s->keys ; + + /* Collect statistics for indexes */ + for (uint i= 0; i < keys; i++) + { + if ((rc= collect_statistics_for_index(table, i))) + break; + } + } + + DBUG_RETURN(rc); +} + + +/** + @brief + Update statistics for a table in the persistent statistical tables + + @param + thd The thread handle + @param + table The table to collect statistics on + + @details + For each statistical table st the function looks for the rows from this + table that contain statistical data on 'table'. If rows with given + statistical characteritics exist they are updated with the new statistical + values taken from internal structures for 'table'. Otherwise new rows + with these statistical characteristics are added into st. + It is assumed that values stored in the statistical tables are found and + saved by the function collect_statistics_for_table. + + @retval + 0 If all statistical tables has been successfully updated + @retval + 1 Otherwise + + @note + The function is called when executing the ANALYZE actions for 'table'. + The function first unlocks the opened table the statistics on which has + been collected, but does not closes it, so all collected statistical data + remains in internal structures for 'table'. Then the function opens the + statistical tables and writes the statistical data for 'table'into them. + It is not allowed just to open statistical tables for writing when some + other tables are locked for reading. + After the statistical tables have been opened they are updated one by one + with the new statistics on 'table'. Objects of the helper classes + Table_stat, Column_stat and Index_stat are employed for this. + After having been updated the statistical system tables are closed. +*/ + +int update_statistics_for_table(THD *thd, TABLE *table) +{ + TABLE_LIST tables[STATISTICS_TABLES]; + Open_tables_backup open_tables_backup; + uint i; + int err; + int rc= 0; + TABLE *stat_table; + uint keys= table->s->keys; + + DBUG_ENTER("update_statistics_for_table"); + + init_table_list_for_stat_tables(tables, TRUE); + init_mdl_requests(tables); + + if (unlock_tables_n_open_system_tables_for_write(thd, + tables, + &open_tables_backup)) + DBUG_RETURN(1); + + /* Update the statistical table table_stat */ + stat_table= tables[TABLE_STAT].table; + Table_stat table_stat(stat_table, table); + restore_record(stat_table, s->default_values); + table_stat.set_key_fields(); + err= table_stat.update_stat(); + if (err) + rc= 1; + + /* Update the statistical table colum_stat */ + stat_table= tables[COLUMN_STAT].table; + Column_stat column_stat(stat_table, table); + for (Field **field_ptr= table->field; *field_ptr; field_ptr++) + { + Field *table_field= *field_ptr; + restore_record(stat_table, s->default_values); + column_stat.set_key_fields(table_field); + err= column_stat.update_stat(); + if (err & !rc) + rc= 1; + } + + /* Update the statistical table index_stat */ + stat_table= tables[INDEX_STAT].table; + Index_stat index_stat(stat_table, table); + KEY *key_info, *key_info_end; + + for (key_info= table->key_info, key_info_end= table->key_info+keys; + key_info < key_info_end; key_info++) + { + uint key_parts= key_info->key_parts; + for (i= 0; i < key_parts; i++) + { + restore_record(stat_table, s->default_values); + index_stat.set_key_fields(key_info, i+1); + err= index_stat.update_stat(); + if (err & !rc) + rc= 1; + } + } + + close_system_tables(thd, &open_tables_backup); + + DBUG_RETURN(rc); +} + + +/** + @brief + Read statistics for a table from the persistent statistical tables + + @param + thd The thread handle + @param + table The table to read statistics on + + @details + For each statistical table the function looks for the rows from this + table that contain statistical data on 'table'. If such rows is found + the data from statistical columns of it is read into the appropriate + fields of internal structures for 'table'. Later at the query processing + this data are supposed to be used by the optimizer. + The function is called in function open_tables. + + @retval + 0 If data has been succesfully read from all statistical tables + @retval + 1 Otherwise + + @note + The function first calls the function open_system_tables_for_read to + be able to read info from the statistical tables. On success the data is + read from one table after another after which the statistical tables are + closed. Objects of the helper classes Table_stat, Column_stat and Index_stat + are employed to read statistical data from the statistical tables. + TODO. Consider a variant when statistical tables are opened and closed + only once for all tables, not for every table of the query as it's done + now. +*/ + +int read_statistics_for_table(THD *thd, struct TABLE *table) +{ + uint i; + TABLE *stat_table; + Field *table_field; + Field **field_ptr; + KEY *key_info, *key_info_end; + TABLE_LIST tables[STATISTICS_TABLES]; + Open_tables_backup open_tables_backup; + + DBUG_ENTER("read_statistics_for_table"); + + init_table_list_for_stat_tables(tables, FALSE); + init_mdl_requests(tables); + + if (open_system_tables_for_read(thd, tables, &open_tables_backup)) + DBUG_RETURN(1); + + create_min_max_stistical_fields(table, FALSE); + + /* Read statistics from the statistical table index_stat */ + stat_table= tables[TABLE_STAT].table; + Table_stat table_stat(stat_table, table); + table_stat.set_key_fields(); + table_stat.get_stat_values(); + + /* Read statistics from the statistical table column_stat */ + stat_table= tables[COLUMN_STAT].table; + Column_stat column_stat(stat_table, table); + for (field_ptr= table->field; *field_ptr; field_ptr++) + { + table_field= *field_ptr; + column_stat.set_key_fields(table_field); + column_stat.get_stat_values(); + } + + /* Read statistics from the statistical table index_stat */ + stat_table= tables[INDEX_STAT].table; + Index_stat index_stat(stat_table, table); + for (key_info= table->key_info, key_info_end= key_info+table->s->keys; + key_info < key_info_end; key_info++) + { + + for (i= 0; i < key_info->key_parts; i++) + { + index_stat.set_key_fields(key_info, i+1); + index_stat.get_stat_values(); + } + } + + close_system_tables(thd, &open_tables_backup); + + DBUG_RETURN(0); +} + diff --git a/sql/sql_statistics.h b/sql/sql_statistics.h new file mode 100644 index 00000000000..1a8cbf6c3f3 --- /dev/null +++ b/sql/sql_statistics.h @@ -0,0 +1,63 @@ +/* Copyright 2006-2008 MySQL AB, 2008 Sun Microsystems, Inc. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ + +#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. +*/ + +enum enum_stat_tables +{ + TABLE_STAT, + COLUMN_STAT, + INDEX_STAT, +}; + +enum enum_table_stat_col +{ + TABLE_STAT_DB_NAME, + TABLE_STAT_TABLE_NAME, + TABLE_STAT_CARDINALITY +}; + +enum enum_column_stat_col +{ + COLUMN_STAT_DB_NAME, + COLUMN_STAT_TABLE_NAME, + COLUMN_STAT_COLUMN_NAME, + COLUMN_STAT_MIN_VALUE, + COLUMN_STAT_MAX_VALUE, + COLUMN_STAT_NULLS_RATIO, + COLUMN_STAT_AVG_LENGTH, + COLUMN_STAT_AVG_FREQUENCY +}; + +enum enum_index_stat_col +{ + INDEX_STAT_DB_NAME, + INDEX_STAT_TABLE_NAME, + INDEX_STAT_INDEX_NAME, + INDEX_STAT_PREFIX_ARITY, + INDEX_STAT_AVG_FREQUENCY +}; + +#endif /* SQL_STATISTICS_H */ diff --git a/sql/structs.h b/sql/structs.h index ae71819ae09..51c71db7cd2 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -115,6 +115,31 @@ typedef struct st_key { For temporary heap tables this member is NULL. */ ulong *rec_per_key; + + /* Statistical data on an index prefixes */ + class Index_statistics + { + public: + /* + The k-th element of this array contains the ratio N/D, + where N is the number of index entries without nulls + in the first k components, and D is the number of distinct + k-component prefixes among them + */ + double *avg_frequency; + }; + + /* + This structure is used for statistical data on the index + that has been read from the statistical table index_stat + */ + Index_statistics read_stat; + /* + This structure is used for statistical data on the index that + is collected by the function collect_statistics_for_table + */ + Index_statistics write_stat; + union { int bdb_return_if_eq; } handler; diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 72e9525db72..05c2ba9c063 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3604,6 +3604,15 @@ static Sys_var_ulong Sys_progress_report_time( SESSION_VAR(progress_report_time), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, UINT_MAX), DEFAULT(56), BLOCK_SIZE(1)); +const char *optimizer_use_stat_tables_modes[] = + {"NEVER", "COMPLEMENTARY", "PREFERABLY", "EXCLUSIVELY", 0}; +static Sys_var_enum Sys_optimizer_use_stat_tables( + "optimizer_use_stat_tables", + "Specifies how to use system statistics tables. Possible values are " + "NEVER, COMPLEMENTARY, PREVERABLY, EXCLUSIVELY", + SESSION_VAR(optimizer_use_stat_tables), CMD_LINE(REQUIRED_ARG), + optimizer_use_stat_tables_modes, DEFAULT(0)); + 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 f8430b9575a..eb8d43deefa 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -544,6 +544,12 @@ inline bool is_system_table_name(const char *name, uint length) my_tolower(ci, name[2]) == 'm' && my_tolower(ci, name[3]) == 'e') || + /* one of mysql.*_stat tables */ + (my_tolower(ci, name[length-4]) == 's' && + my_tolower(ci, name[length-3]) == 't' && + my_tolower(ci, name[length-2]) == 'a' && + my_tolower(ci, name[length-1]) == 't') || + /* mysql.event table */ (my_tolower(ci, name[0]) == 'e' && my_tolower(ci, name[1]) == 'v' && @@ -753,9 +759,11 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, uchar forminfo[288]; uchar *record; uchar *disk_buff, *strpos, *null_flags, *null_pos; - ulong pos, record_offset; + ulong pos, record_offset; ulong *rec_per_key= NULL; ulong rec_buff_length; + double *read_avg_frequency= 0; + double *write_avg_frequency= 0; handler *handler_file= 0; KEY *keyinfo; KEY_PART_INFO *key_part= NULL; @@ -938,6 +946,14 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, if (!(rec_per_key= (ulong*) alloc_root(&share->mem_root, sizeof(ulong) * ext_key_parts))) goto err; + if (!(read_avg_frequency= (double*) alloc_root(&share->mem_root, + sizeof(double) * + ext_key_parts))) + goto err; + if (!(write_avg_frequency= (double*) alloc_root(&share->mem_root, + sizeof(double) * + ext_key_parts))) + goto err; first_key_part= key_part; first_key_parts= first_keyinfo.key_parts; keyinfo->flags= first_keyinfo.flags; @@ -950,9 +966,13 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, keyinfo->key_part= key_part; keyinfo->rec_per_key= rec_per_key; + keyinfo->read_stat.avg_frequency= read_avg_frequency; + keyinfo->write_stat.avg_frequency= write_avg_frequency; for (j=keyinfo->key_parts ; j-- ; key_part++) { *rec_per_key++=0; + *read_avg_frequency++= 0; + *write_avg_frequency++= 0; key_part->fieldnr= (uint16) (uint2korr(strpos) & FIELD_NR_MASK); key_part->offset= (uint) uint2korr(strpos+2)-1; key_part->key_type= (uint) uint2korr(strpos+5); diff --git a/sql/table.h b/sql/table.h index d2337022724..e7f41576215 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1006,6 +1006,25 @@ public: */ query_id_t query_id; + /* Statistical data on a table */ + class Table_statistics + { + public: + my_bool cardinality_is_null; /* TRUE if the cardinality is unknown */ + ha_rows cardinality; /* Number of rows in the table */ + }; + + /* + This structure is used for statistical data on the table + that has been read from the statistical table table_stat + */ + Table_statistics read_stat; + /* + This structure is used for statistical data on the table that + is collected by the function collect_statistics_for_table + */ + Table_statistics write_stat; + /* For each key that has quick_keys.is_set(key) == TRUE: estimate of #records and max #key parts that range access would use. |