Details
Description
ANALYZE will show "Table is already up to date" while it re-calculates and updates table statistics.
I think, this is misleading. For me, "Table is already up to date" means that "statistics were not updated", while actually statistics was updated.
Below is an example where I generate a histogram with size=10, then run another ANALYZE to generate a histogram with size=20, and get "Table is already up to date" message.
MariaDB [test]> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> set @a:=-1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into t1 select @a:=@a+1 from information_schema.session_variables;
Query OK, 404 rows affected (0.03 sec)
Records: 404 Duplicates: 0 Warnings: 0
MariaDB [test]> set histogram_size=10;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> set histogram_type='single_prec_hb';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> analyze table t1 persistent for all;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.01 sec)
MariaDB [test]> select *, hex(histogram) from mysql.column_stats\G
*************************** 1. row ***************************
db_name: test
table_name: t1
column_name: a
min_value: 0
max_value: 403
nulls_ratio: 0.0000
avg_length: 4.0000
avg_frequency: 1.0000
hist_size: 10
hist_type:
histogram: .E\s����
hex(histogram): 162E455C738BA2B9D0E8
1 row in set (0.00 sec)
MariaDB [test]> set histogram_size=20;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> analyze table t1 persistent for all;
+---------+---------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+-----------------------------+
| test.t1 | analyze | status | Table is already up to date |
+---------+---------+----------+-----------------------------+
1 row in set (0.01 sec)
MariaDB [test]> select *, hex(histogram) from mysql.column_stats\G
*************************** 1. row ***************************
db_name: test
table_name: t1
column_name: a
min_value: 0
max_value: 403
nulls_ratio: 0.0000
avg_length: 4.0000
avg_frequency: 1.0000
hist_size: 20
hist_type:
histogram:
$0<HT`my���������
hex(histogram): 0C1824303C4854606D7985919EAAB6C2CEDAE6F2
1 row in set (0.00 sec)
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-4145 Take into account the selectivity of single-table range predicates on non-indexed columns when searching for the best execution plan
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions