When should run Analyze table statement in mysql

Question

Hello DBA's i have also some question

1. Why should we never run ANALYZE TABLE?
2. How do I know for sure this table/db needs to be analyzed ? It seems that MySQL doesn't store when the last time "stat" was updated ?

Just for Example :- Assume i have queried

mysql> show index from goods;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| goods |          0 |  PRIMARY |            1 |          id |         A |     7765796 |     NULL |   NULL |      |      BTREE |         |               |
| goods |          1 |  shop_id |            1 |     shop_id |         A |       14523 |     NULL |   NULL |      |      BTREE |         |               |
| goods |          1 |  shop_id |            2 | create_date |         A |      168168 |     NULL |   NULL |  YES |      BTREE |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

So to see this output,how do i get to know for this table need to run analyze table command ?

Answer 1

I just implemented a service at my company to run ANALYZE TABLE regularly on InnoDB tables.

The reason I did this is that we were affected by this bug: InnoDB persistent statistics not persistent enough .

When using InnoDB persistent statistics, automatic table statistics are refreshed after about 10% of the rows are modified. There's a counter that keeps track of this, but the counter itself is not persistent; it starts over at 0 if the MySQL Server restarts.

We have some tables that are very large, with hundreds of millions of rows. We also restart MySQL Server regularly, because we stay up to date faithfully on MySQL updates and OS updates. The combination of these two things causes us to do restarts before the 10% of rows are modified, and therefore the InnoDB table stats never update.

So I created a service that visits every database replicaset every day. It checks the table mysql.innodb_table_stats and specifically the last_update column. This will tell me how long it has been since the table stats were updated.

If that value is more than 90 days old for a given table, I definitely need to run ANALYZE TABLE.

Otherwise I do a random sampling and pick 10% of the tables to analyze. This should result in every table getting analyzed on average every ten days.

Naturally by checking mysql.innodb_table_stats, only InnoDB tables are considered, but we made a policy that we only support InnoDB tables in our company.

Answer 2

under normal circumstances you don't need to analyse the table, the index cares for himself, it updates the index and stats automatically.

But on high traffic tables, with lots of deletes the index gets "confused" and needs more time to select the right rows, then you run ANALYSE when no other traffic is made.

Because it time consuming on large tables and every new entry would can change tge dynamic.

Please read carefully https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html