MySQL OPTIMIZE all tables?

Question

You can use mysqlcheck to do this at the command line.

One database:

mysqlcheck -o <db_schema_name>

All databases:

mysqlcheck -o --all-databases

I made this 'simple' script:

set @tables_like = null;
set @optimize = null;
set @show_tables = concat("show tables where", ifnull(concat(" `Tables_in_", database(), "` like '", @tables_like, "' and"), ''), " (@optimize:=concat_ws(',',@optimize,`Tables_in_", database() ,"`))");

Prepare `bd` from @show_tables;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;

set @optimize := concat('optimize table ', @optimize);
PREPARE `sql` FROM @optimize;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;

set @show_tables = null, @optimize = null, @tables_like = null;

To run it, simply paste it in any SQL IDE connected to your database.

Notice: this code WON'T work on phpmyadmin.

How it works

It runs a show tables statement and stores it in a prepared statement. Then it runs a optimize table in the selected set.

You can control which tables to optimize by setting a different value in the var @tables_like (e.g.: set @tables_like = '%test%';).

Following example php script can help you to optimize all tables in your database

<?php

dbConnect();

$alltables = mysql_query("SHOW TABLES");

while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("OPTIMIZE TABLE '".$tablename."'")
       or die(mysql_error());

   }
}

?>

Do all the necessary procedures for fixing all tables in all the databases with a simple shell script:

#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze

for all databases:

mysqlcheck -Aos -uuser -p 

For one Database optimization:

mysqlcheck -os -uroot -p dbtest3

If you want to analyze, repair and optimize all tables in all databases in your MySQL server, you can do this in one go from the command line. You will need root to do that though.

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Once you run that, you will be prompted to enter your MySQL root password. After that, it will start and you will see results as it's happening.

Example output:

yourdbname1.yourdbtable1       OK
yourdbname2.yourdbtable2       Table is already up to date
yourdbname3.yourdbtable3
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

etc..
etc...

Repairing tables
yourdbname10.yourdbtable10
warning  : Number of rows changed from 121378 to 81562
status   : OK

If you don't know the root password and are using WHM, you can change it from within WHM by going to: Home > SQL Services > MySQL Root Password

From phpMyAdmin and other sources/editors you can use:

SET SESSION group_concat_max_len = 99999999;
SELECT GROUP_CONCAT(concat('OPTIMIZE TABLE `', table_name, '`;') SEPARATOR '') AS O
FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_TYPE = 'BASE TABLE'
AND table_name!='dual'
AND TABLE_SCHEMA = '<your databasename>'

Then you can copy & paste the result to a new query or execute it from your own source. If you don't see the whole statement in phpMyAdmin: