sql – MySQL OPTIMIZE all tables?

The Question :

254 people think this question is useful

MySQL has an OPTIMIZE TABLE command which can be used to reclaim unused space in a MySQL install. Is there a way (built-in command or common stored procedure) to run this optimization for every table in the database and/or server install, or is this something you’d have to script up yourself?

The Question Comments :
  • Be careful in that this will not necessarily reclaim space. If you are using InnoDB with a single file (probably the most common setup these days) rather than separate files per table, you will still use the same amount of disk space at the end. In fact I have seen it actually use significantly more disk space when all was said and done. With large tables, the table may be locked for a very long time as well.
  • OPTIMIZE TABLE was useful for MyISAM. Now that that Engine is going away, the need for OPTIMIZE TABLE is going away, especially the need to periodically optimize all tables.
  • +1 for good info rick — but given standard real world database practices, I wouldn’t be surprised if old MyISAM tables remain around for another decade

The Answer 1

426 people think this answer is useful

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

One database:

mysqlcheck -o <db_schema_name>

All databases:

mysqlcheck -o --all-databases

The Answer 2

29 people think this answer is useful

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

The Answer 3

20 people think this answer is useful

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());

   }
}

?>

The Answer 4

15 people think this answer is useful

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

The Answer 5

11 people think this answer is useful

for all databases:

mysqlcheck -Aos -uuser -p 

For one Database optimization:

mysqlcheck -os -uroot -p dbtest3

The Answer 6

9 people think this answer is useful

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

The Answer 7

9 people think this answer is useful

From phpMyAdmin and other sources 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: way to see whole statement in phpmyadmin

The Answer 8

5 people think this answer is useful

From command line:

mysqlcheck -o <db_name> -u<username> -p

then type password

The Answer 9

4 people think this answer is useful

You can optimize/check and repair all the tables of database, using mysql client.

First, you should get all the tables list, separated with ‘,’:

mysql -u[USERNAME] -p[PASSWORD] -Bse 'show tables' [DB_NAME]|xargs|perl -pe 's/ /,/g'

Now, when you have all the tables list for optimization:

mysql -u[USERNAME] -p[PASSWORD] -Bse 'optimize tables [tables list]' [DB_NAME]

The Answer 10

3 people think this answer is useful

The MySQL Administrator (part of the MySQL GUI Tools) can do that for you on a database level.

Just select your schema and press the Maintenance button in the bottom right corner.

Since the GUI Tools have reached End-of-life status they are hard to find on the mysql page. Found them via Google: http://dev.mysql.com/downloads/gui-tools/5.0.html

I don’t know if the new MySQL Workbench can do that, too.

And you can use the mysqlcheck command line tool which should be able to do that, too.

The Answer 11

2 people think this answer is useful

If you are accessing database directly then you can write following query:

OPTIMIZE TABLE table1,table2,table3,table4......;

The Answer 12

1 people think this answer is useful

This bash script will accept the root password as option and optimize it one by one, with status output:

#!/bin/bash

if [ -z "$1" ] ; then
  echo
  echo "ERROR: root password Parameter missing."
  exit
fi
MYSQL_USER=root
MYSQL_PASS=$1
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
    echo OPTIMIZE TABLE "${DBTB};"
    SQL="OPTIMIZE TABLE ${DBTB};"
    mysql ${MYSQL_CONN} -ANe"${SQL}"
done

The Answer 13

1 people think this answer is useful

A starter bash script to list and run a tool against the DBs…

#!/bin/bash

declare -a dbs
unset opt

for each in $(echo "show databases;" | mysql -u root) ;do

        dbs+=($each)

done



echo " The system found [ ${#dbs[@]} ] databases." ;sleep 2
echo
echo "press 1 to run a check"
echo "press 2 to run an optimization"
echo "press 3 to run a repair"
echo "press 4 to run check,repair, and optimization"
echo "press q to quit"
read input

case $input in
        1) opt="-c"
        ;;
        2) opt="-o"
        ;;
        3) opt="-r"
        ;;
        4) opt="--auto-repair -c -o"
        ;;
        *) echo "Quitting Application .."; exit 7
        ;;
esac

[[ -z $opt ]] &amp;&amp; exit 7;

echo " running option:  mysqlcheck $opt in 5 seconds  on all Dbs... "; sleep 5

for ((i=0; i<${#dbs[@]}; i++)) ;do
        echo "${dbs[$i]} : "
        mysqlcheck $opt ${dbs[$i]}  -u root
    done

The Answer 14

0 people think this answer is useful

my 2cents: start with table with highest fragmentation

for table in `mysql -sss -e "select concat(table_schema,".",table_name) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema') order by data_free desc;"
do
mysql -e "OPTIMIZE TABLE $table;"
done

Tags:,

Add a Comment