Author: matheusdba

It happens on MySQL, as you know. Run an Optimize Table solve the question.
BUT, be careful! During the optimize the table stay locked (writing is not possible).

(Fragmented Table)

So what?
To not cause a lock in every table, the script below shows and runs (if you want to list but not run, comment the line) only for tables that have fragmentation.

It was very useful to me! 🙂

#!/bin/sh
echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo
mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done

The resul will be like:

MySQL username: root
MySQL password:
...
mysql.db is 12% fragmented.
mysql.db optimize status OK
mysql.user is 9% fragmented.
mysql.db optimize status OK
...

This script is a full copy from this post by Robert de Bock.
Thanks, Robert!

Matheus.