diff options
Diffstat (limited to 'mysql-test/main/stat_tables.test')
| -rw-r--r-- | mysql-test/main/stat_tables.test | 51 |
1 files changed, 49 insertions, 2 deletions
diff --git a/mysql-test/main/stat_tables.test b/mysql-test/main/stat_tables.test index 79a7a17550b..b5a804598a4 100644 --- a/mysql-test/main/stat_tables.test +++ b/mysql-test/main/stat_tables.test @@ -6,6 +6,8 @@ select @@session.use_stat_tables; set @save_use_stat_tables=@@use_stat_tables; set use_stat_tables='preferably'; +set @tmp_stt_hs=@@histogram_size, @tmp_stt_ht=@@histogram_type; +set histogram_size=0, histogram_type='single_prec_hb'; --disable_warnings DROP DATABASE IF EXISTS dbt3_s001; @@ -191,7 +193,7 @@ FLUSH TABLE t1; SET use_stat_tables='never'; EXPLAIN SELECT * FROM t1; ---move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD.save +--move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MAD $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MAD.save FLUSH TABLES; SET use_stat_tables='preferably'; @@ -200,7 +202,7 @@ EXPLAIN SELECT * FROM t1; --enable_warnings # Cleanup ---move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD.save $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MYD +--move_file $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MAD.save $MYSQLTEST_VARDIR/mysqld.1/data/mysql/table_stats.MAD DROP TABLE t1; set use_stat_tables=@save_use_stat_tables; @@ -400,6 +402,8 @@ CREATE OR REPLACE TABLE t1 (pk INT PRIMARY KEY, t TEXT); SELECT MAX(pk) FROM t1; DROP TABLE t1; +set use_stat_tables=@save_use_stat_tables; + --echo # @@ -544,3 +548,46 @@ set @@sql_mode= @save_sql_mode; set use_stat_tables=@save_use_stat_tables; set @@histogram_size= @save_histogram_size; set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-17255: New optimizer defaults and ANALYZE TABLE +--echo # + +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; +--echo # +--echo # with use_stat_tables= PREFERABLY_FOR_QUERIES +--echo # analyze table t1 will not collect statistics +--echo # + +analyze table t1; +select * from mysql.column_stats; +analyze +select * from t1 where a = 1 and b=3; + +--echo # +--echo # with use_stat_tables= PREFERABLY_FOR_QUERIES +--echo # analyze table t1 will collect statistics if we use PERSISTENT +--echo # for columns, indexes or everything +--echo # + +analyze table t1 persistent for columns (a) indexes (); +select * from mysql.column_stats; +--echo # filtered shows that we used the data from stat tables +analyze +select * from t1 where a = 1 and b=3; + +--echo # +--echo # with use_stat_tables= PREFERABLY +--echo # analyze table t1 will collect statistics +--echo # + +set use_stat_tables=PREFERABLY; +analyze table t1; +select * from mysql.column_stats; +--echo # filtered shows that we used the data from stat tables +analyze +select * from t1 where a=1 and b=3; +drop table t1; +set histogram_size=@tmp_stt_hs, histogram_type=@tmp_stt_ht; |