How To Quickly Analyze All Tables in MySQL Database

ANALYZE TABLE analyzes and stores the key distribution for a table. The MySQL query optimizer is the magic inside MySQL that decides which keys, if any, to use to in the query. ANALYZE helps query optimizer to make accurate decisions by detailed analysis of the data, unlike query optimizer which makes quick analysis.

The command to analyze all tables in a running database is:
mysqlcheck -Aa -uroot -p

Provide the password when prompted.

You can add it to your daily / weekly cron job for optimum performance of the database. The password can be provided in command line too.

During the analysis, the table is locked with a read lock for MyISAM and BDB. ANALYZE works with MyISAM, BDB, and InnoDB tables.

Note: Running ANALYZE is equivalent to running myisamchk -a or myismachk –analyze.