8

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?

14

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.

4

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';
1

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 !!!

  • This seems to take a long time on my server and I cannot see aprogress. can you add some output which table is optimized in the loop? – rubo77 Nov 5 '15 at 9:42
  • I adapted your script to optimize all databases so it is run one by one: stackoverflow.com/a/33541722/1069083 – rubo77 Nov 5 '15 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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