Is there any command to optimize all MySQL tables one by one automatically?

Question

I execute below command line statement to optimize a table: optimize table tablename;

Is there any command or statement which can optimize all the tables one by one of the selected database?

Answer 1

I usually use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators to check, optimize and repair tables.

mysqlcheck -uroot -p --optimize --databases myDatabase

Here is the MySQL doc : http://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html

Max.

Answer 2

You can use information schema to generate the statements you need:

SELECT Concat('OPTIMIZE TABLE ',TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
   WHERE table_schema='yourdb';

Answer 3

You could script it and place all tables on a single line

ALL TABLES IN THE CURRENT DATABASE

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_DATA=mydb
TBLLIST=""
COMMA=""
SQL="SELECT table_name FROM information_schema.tables WHERE table_schema=DATABASE()"
for TBL in `mysql ${MYSQL_CONN} -D${MYSQL_DATA} -ANe"${SQL}"`
do
    TBLLIST="${TBLLIST}${COMMA}${TBL}"
    COMMA=","
done
SQL="OPTIMIZE TABLE ${TBLLIST};"
mysql ${MYSQL_CONN} -D${MYSQL_DATA} -ANe"${SQL}"

ALL TABLES IN ALL USER DATABASES

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
TBLLIST=""
COMMA=""
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
for DBTB in `mysql ${MYSQL_CONN} -ANe"${SQL}"`
do
    TBLLIST="${TBLLIST}${COMMA}${DBTB}"
    COMMA=","
done
SQL="OPTIMIZE TABLE ${TBLLIST};"
set -x
mysql ${MYSQL_CONN} -ANe"${SQL}"

GIVE IT A TRY !!!