MySQL OPTIMIZE all tables?

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%';).