MySQL :: How to run ANALYZE TABLE for all tables in a database

I'm using MySQL 5.7

How can I run ANALYZE TABLE for all tables in a database. It should work if new tables are added to the database in future.

This is to refresh the statistics of table indexes.

If you are using InnoDB tables (which you should be):

SET @old_innodb_stats_on_metadata = @@global.innodb_stats_on_metadata;
SET GLOBAL innodb_stats_on_metadata='ON';
SHOW TABLE STATUS FROM db;
SET GLOBAL innodb_stats_on_metadata = @old_innodb_stats_on_metadata;

For InnoDB, the actions performed by ANALYZE TABLE are also performed by merely viewing the table status, if the option I show above is ON. That is, viewing the table status, or querying INFORMATION_SCHEMA.TABLES, will trigger the same refresh of table statistics that ANALYZE TABLE does.

The statement SHOW TABLE STATUS FROM db shows all tables from the named schema by default, so there's no need to code a loop or anything as other answers have shown.

StackExchange Database Administrators Q#264326, answer score: 6