optimize table in mysql

The SQL query that help  to find tables which are non-optimal :

SHOW TABLE STATUS WHERE Data_free > [integer value]

substituting [integer value] for an integer value, which is the free data space in bytes. This could be e.g. 102400 for tables with 100k of free space. This will then only return the tables which have more than 100k of free space.

An alternative way of searching would be to look for tables that have e.g. 10% of overhead free space by doing this:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1

The downside with this is that it would include small tables with very small amounts of free space so it could be combined with the first SQL query to only get tables with more than 10% overhead and more than 100k of free space:

SHOW TABLE STATUS WHERE Data_free / Data_length > 0.1 AND Data_free > 102400

for d in `mysql -e "show databases"|g -v Database|g -v information_schema` ; do echo " -------- $d ----------" && for t in `mysql -D  $d  -e "SHOW TABLE STATUS WHERE Data_free > 0 " | awk '{ if ($2 != "MyISAM") printf $1 "\n"}'`; do echo $t && mysql -D  $d  -e "optimize table $t"  ; done   ; done

You can optimize all table in all mysql databases with shell command  :

# for d in `mysql -e "show databases"|grep  -v Database|g -v information_schema` ; do echo " ----- Database:  $d  ----------"  ;  for t in `mysql -D  $d  -e "SHOW TABLE STATUS WHERE Data_free  > 0 "  | grep -v Name  |  awk '{print $1 }' `; do echo "optimize table $t"  ; mysql -D  $d  -e "optimize table $t"  ; done   ; done

or you can optimize only some one database

# for t in `mysql -D    -e "SHOW TABLE STATUS WHERE Data_free  > 0 "  | grep -v Name  |  awk '{print $1 }' `; do echo "optimize table $t"  ; mysql -D    -e "optimize table $t"  ; done   

Remember:  optimization of big mysql tables takes a long time