Mysql tuner telling you to optimize your tables? or you just happen to be bored? Mysql optimize Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table. This statement does not work with views. You should use OPTIMIZE TABLE in these cases, depending on the type of table:
-
After doing substantial insert, update, or delete operations on an
InnoDBtable that has its own .ibd file because it was created with theinnodb_file_per_tableoption enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system. -
After deleting a large part of a
MyISAMorARCHIVEtable, or making many changes to aMyISAMorARCHIVEtable with variable-length rows (tables that haveVARCHAR,VARBINARY,BLOB, orTEXTcolumns). Deleted rows are maintained in a linked list and subsequentINSERToperations reuse old row positions. You can useOPTIMIZE TABLEto reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.
This statement requires SELECT and INSERT privileges for the table.
well use this one liner to repair/optimize all your DBs in one go.
mysqlcheck --auto-repair --optimize --all-databasesvoila!
But Wait, There’s More to Know Before You Nuke Your Tables
That one-liner is great, but there are a few things that’ll bite you if you’re not paying attention.
Authentication
By default, mysqlcheck will prompt for a password. If you’re running this as a cron job or just want to skip the prompt dance, you’ve got options:
# Pass credentials inline (works, but shows up in process list — not ideal)mysqlcheck -u root -p'yourpassword' --auto-repair --optimize --all-databases
# Better: use a credentials filemysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize --all-databasesSet up /root/.my.cnf like this:
[client]user=rootpassword=yourpasswordThen lock it down so you’re not just leaving credentials lying around:
chmod 600 /root/.my.cnfNow your cron job can run it without a password prompt and without embedding credentials in the crontab itself. Worth the extra 30 seconds.
The InnoDB Elephant in the Room
Here’s the gotcha that’ll have you scratching your head: on InnoDB tables, OPTIMIZE TABLE doesn’t actually do what you think it does. MySQL just runs ALTER TABLE ... FORCE under the hood, which rebuilds the table. That works, but it acquires a full table lock for the duration. On a busy production server with a 10GB table, that’s a bad time.
If you’re running InnoDB and you can’t afford downtime, look into pt-online-schema-change from Percona Toolkit. But if this is a maintenance window situation or a dev/staging box, the one-liner is fine.
What the Output Actually Means
mysqlcheck is chatty. You’ll see a wall of output like:
mydb.users OKmydb.orders Table is already up to datemydb.sessions warning : Table does not support optimize, doing recreate + analyze insteadmydb.sessions status : OK“Table does not support optimize” sounds alarming but it’s just MySQL telling you it’s InnoDB and it handled it differently. Not a problem.
What you do want to watch for:
mydb.broken_table error : Table './mydb/broken_table' is marked as crashedmydb.broken_table repairedThat’s the --auto-repair flag doing its job. If you see “repaired” in the output, something was actually corrupt and got fixed — worth digging into why it crashed in the first place (usually an unclean shutdown).
Run It as a Cron Job
This is genuinely useful to run weekly during off-hours. Something like this in your crontab:
# Run every Sunday at 3am, log output0 3 * * 0 mysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize --all-databases >> /var/log/mysqlcheck.log 2>&1Your 2 AM self will appreciate having a log to check instead of wondering if the thing even ran.
MariaDB Users
If you’re on MariaDB (and in 2024+ there’s a decent chance you are), the command is identical — mysqlcheck ships with MariaDB client tools and works exactly the same way. No adjustments needed.
Skipping Specific Databases (Because You Don’t Always Want Everything)
The --all-databases flag is convenient but sometimes too convenient. Maybe you’ve got a test database full of garbage you don’t care about, or a performance_schema that has no business being optimized. Running mysqlcheck on everything wastes time and occasionally causes confusion when MySQL tells you tables in information_schema can’t be optimized.
You can target specific databases instead:
# Just one databasemysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize myapp_production
# Multiple specific databases — just list themmysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize myapp_production myapp_staging
# One specific table within a databasemysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize myapp_production usersOr, if you want to stick with --all-databases but skip the system schemas, wrap it in a quick shell loop using the MySQL client to list only your app databases:
mysql --defaults-file=/root/.my.cnf -e "SHOW DATABASES;" \ | grep -vE "^(Database|information_schema|performance_schema|sys|mysql)$" \ | xargs -I{} mysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize {}This pulls the database list from MySQL directly, strips the system schemas with grep -v, then runs mysqlcheck against each one individually. A bit more verbose than the one-liner, but you get precise control without accidentally touching tables you shouldn’t.
One last thing: if you’re optimizing on a live system and want to see what’s happening in real time, mysqlcheck outputs as it goes — you can tee the output to a log file while still watching it scroll by:
mysqlcheck --defaults-file=/root/.my.cnf --auto-repair --optimize --all-databases \ | tee -a /var/log/mysqlcheck.logThat way you get the live view and a permanent record. Future you running grep repaired /var/log/mysqlcheck.log will owe present you a coffee.
