Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer
    
Discussion moved to chat – ursitesion Apr 27 '14 at 17:34

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';
share|improve this answer

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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