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.

1 files changed, 56 insertions, 0 deletions

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;