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 ?

2 Answers 2


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.

  • Hi. In new versions of MySQL ` innodb_stats_persistent` has been added to avoid this. According to the Documentation: dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html. But I am using the latest version and I still have the same issue as you mentioned. Is the bug really fixed or not? and are you still experiencing the issue in new versions? Mar 6, 2022 at 8:59
  • 1
    @SoheilRahsaz I have not worked on MySQL 8.0 instances in production, so I can't prove that the issue still affects that version. But I think it does, because the bug I linked to is not fixed. I think it's a different issue from the purpose of persistent stats. Persistent stats helps retain stats through a restart. But it doesn't affect the threshold for recalculating stats. Apr 29, 2022 at 15:03

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

  • 2
    InnoDB does a small number of probes; it is not "time consuming on large tables". (MyISAM, on the other hand, looks at the entire table.)
    – Rick James
    Jun 9, 2020 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.