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

Question

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.

Answer 1

You can run

mysqlcheck -a --all-databases

Answer 2

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.

Answer 3

I wanted a way of getting the exact number of rows in each table in my db, and found that ANALYZE TABLE table_name is needed to make sure the value is correct. I could not pass a variable table_name into ANALYZE TABLE, so this is the best I could do.

In summary:

• create a stored procedure script_to_analyse_all_tables() which loops through each table name, producing the query ANALYZE TABLE table_name for each table.
• call this stored procedure
• delete this stored procedure afterwards (if you dont want it to remain)

Then, manually:

• Copy the output
• Paste & run the output in MySQL workbench.

Just update db_name with the name of your database before you run it.

DROP PROCEDURE IF EXISTS `script_to_analyse_all_tables`;
DELIMITER $$
CREATE PROCEDURE script_to_analyse_all_tables()
BEGIN
    -- declare variables
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE this_table_name VARCHAR(256); 
    DECLARE full_statement VARCHAR(10000); 
    -- declare cursor for table names
    DECLARE cursor_table_name CURSOR FOR 
        SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '[db_name]';
    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
-- open the cursor
OPEN cursor_table_name;

myLoop: LOOP
    FETCH cursor_table_name INTO this_table_name;
    IF finished = 1 THEN 
        LEAVE myLoop;
    END IF;
    SET full_statement = concat( IFNULL(full_statement,''), "ANALYZE TABLE ", this_table_name, ";");
END LOOP myLoop;
-- close the cursor
CLOSE cursor_table_name;
-- view results:
SELECT full_statement as "run the following statement:";


END $$
DELIMITER ;
CALL script_to_analyse_all_tables;
DROP PROCEDURE IF EXISTS script_to_analyse_all_tables;

Then check the results with:

SELECT table_name, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '[db_name]';

Answer 4

Here is a bash script that will run ANALYZE TABLE against all tables in a given database

RUN_SQL=/tmp/analyze_all_tables.sql
RUN_LOG=/tmp/analyze_all_tables.log
MYSQL_HOST=whateverhostname
MYSQL_AUSER=root
MYSQL_PASS=rootpass
MYSQL_AUTH="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
DB=whateverdb
SQL="SELECT CONCAT('ANALYZE LOCAL TABLE `',table_schema,'`.`',table_name,'`;')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB}'"
SQL="${SQL} AND engine IS NOT NULL"
Create SQL Commands to run ANALYZE TABLE
mysql ${MYSQL_AUTH} -ANe"${SQL}" > ${RUN_SQL}
Execute ANALYZE TABLE Commands
mysql ${MYSQL_AUTH} --table < ${RUN_SQL} > ${RUN_LOG} 2>&1

Here is a bash script that will run ANALYZE TABLE against all tables in all databases

RUN_SQL=/tmp/analyze_all_tables.sql
RUN_LOG=/tmp/analyze_all_tables.log
MYSQL_HOST=whateverhostname
MYSQL_AUSER=root
MYSQL_PASS=rootpass
MYSQL_AUTH="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('ANALYZE LOCAL TABLE `',table_schema,'`.`',table_name,'`;')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql','sys','innodb')"
SQL="${SQL} AND engine IS NOT NULL"
Create SQL Commands to run ANALYZE TABLE
mysql ${MYSQL_AUTH} -ANe"${SQL}" > ${RUN_SQL}
Execute ANALYZE TABLE Commands
mysql ${MYSQL_AUTH} --table < ${RUN_SQL} > ${RUN_LOG} 2>&1

NOTE

The SQL Clause AND engine IS NOT NULL will skip views

Answer 5

On further research, I found the following, neater answer here: https://stackoverflow.com/a/24707827/12032648

Code below lifted straight from the referenced answer, wrapped in a procedure.

DROP PROCEDURE IF EXISTS `script_to_analyse_all_tables`;
DELIMITER $$
CREATE PROCEDURE script_to_analyse_all_tables()
BEGIN
zgwp_tables_rowcounts
TableName RowCount
Outputs a result set listing all tables and their row counts
for the current database
SET SESSION group_concat_max_len = 1000000;
SET @sql = NULL;
SET @dbname = DATABASE();
SELECT
  GROUP_CONCAT( 
    CONCAT (
      'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', 
       table_name, ' '
    ) 
    SEPARATOR 'UNION '

  ) AS Qry
FROM
  information_schema.TABLES AS t
WHERE
  t.TABLE_SCHEMA = @dbname AND
  t.TABLE_TYPE = "BASE TABLE"
ORDER BY
  t.TABLE_NAME ASC
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
END $$
DELIMITER ;
CALL script_to_analyse_all_tables;
DROP PROCEDURE IF EXISTS script_to_analyse_all_tables;