In MySQL, when you delete records from a table, space is reallocated automatically. It is as empty space and forming new attachments will advantage.
The problem is that if a table perform many DELETE operations, the physical space of the table will become increasingly fragmented and the performance is reduced.
In the MyISAM and InnoDB, OPTIMIZE TABLE command available to perform an optimization on any table that, among other things, performs an automatic defragmentation of the table.
It is highly recommended to use this command regularly especially on tables that are more statements of disposal of records.
As a precaution, keep in mind that during implementation, of course, the table is blocked. You have to remember when you are going to use with large tables and busy.
Supersimples The syntax is:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;
To make a fragmented table optimization can be selected to have free space, probably as a result of DELETE statements:
SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
Data_free > 0
In the post Defragmenting all fragmented MyISAM tables of Chistian's Blog I found this simple script that uses this statement to defragment the tables having 'gaps'.
#!/bin/bash
# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME \
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND \
Data_free > 0' | grep -v "^+" | sed "s,\t,.," )"
for fragment in $FRAGMENTED_TABLES; do
database="$( echo $fragment | cut -d. -f1 )"
table="$( echo $fragment | cut -d. -f2 )"
[ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;\
OPTIMIZE TABLE $table;" > /dev/null 2>&1
done
I connect also a good article that I found on fragmentation of data in databases, with examples of PostgreSQL, but with concepts applicable to most relational DB:
