From 9207a838ededa0d5f40129848de20a2075ab0e44 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sun, 9 Dec 2018 13:25:27 +0530 Subject: MDEV-17255: New optimizer defaults and ANALYZE TABLE Added to new values to the server variable use_stat_tables. The values are COMPLEMENTARY_FOR_QUERIES and PREFERABLY_FOR_QUERIES. Both these values don't allow to collect EITS for queries like analyze table t1; To collect EITS we would need to use the syntax with persistent like analyze table t1 persistent for columns (col1,col2...) index (idx1, idx2...) / ALL Changing the default value from NEVER to PREFERABLY_FOR_QUERIES. --- mysql-test/main/stat_tables_innodb.result | 56 +++++++++++++++++++++++++++++++ 1 file changed, 56 insertions(+) (limited to 'mysql-test/main/stat_tables_innodb.result') diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index a72e3cec1d4..fc37ece99cb 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -652,5 +652,61 @@ MAX(pk) NULL DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; +# +# MDEV-17255: New optimizer defaults and ANALYZE TABLE +# +create table t1 (a int, b int); +insert into t1(a,b) values (1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(3,8),(3,9),(3,9),(4,10); +set use_stat_tables= preferably_for_queries; +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will not collect statistics +# +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY_FOR_QUERIES +# analyze table t1 will collect statistics if we use PERSISTENT +# for columns, indexes or everything +# +analyze table t1 persistent for columns (a) indexes (); +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a = 1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 25.00 10.00 Using where +# +# with use_stat_tables= PREFERABLY +# analyze table t1 will collect statistics +# +set use_stat_tables=PREFERABLY; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +select * from mysql.column_stats; +db_name table_name column_name min_value max_value nulls_ratio avg_length avg_frequency hist_size hist_type histogram +test t1 a 1 4 0.0000 4.0000 2.5000 0 NULL NULL +test t1 b 2 10 0.0000 4.0000 1.1111 0 NULL NULL +# filtered shows that we used the data from stat tables +analyze +select * from t1 where a=1 and b=3; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10.00 2.78 10.00 Using where +drop table t1; +set use_stat_tables=@save_use_stat_tables; set optimizer_switch=@save_optimizer_switch_for_stat_tables_test; SET SESSION STORAGE_ENGINE=DEFAULT; -- cgit v1.2.1