Check for Fragmentation in MySQL and fix it

What are fragmented tables? If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages or that there are many unused pages in the 64-page blocks that were allocated to the index. The symptoms of fragmented tables can be that table can take more disk space than needed or the results may return slower with more disk I/O than needed. INNODB users need to check the fragmentation often because when INNODB marks data as deleted, it never overwrites the blocks with new data. It just marks them as unusable. As a result, the data size is artificially inflated and data retrieval is slowed.

SELECT
TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA,
CONCAT(ROUND(data_free  / ( 1024 * 1024 ), 2), 'MB')FREE
              from information_schema.TABLES
			       where TABLE_SCHEMA
				   NOT IN ('information_schema','mysql') and Data_free < 0;

 #!/bin/bash

MYSQL_LOGIN='-u<user name> --password=<passowrd>'

for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN >/dev/null
                echo "done."
        done
done