W3hobbyist

When you DELETE row(s) form a MySQL table, it creates an unoccupied empty space in the table. MySQL database remembers this extra space and whenever there is an INSERT, instead of creating new space for the newly inserted data, it uses that unoccupied empty space for the new insert. An empty space is a like non-profit asset for the MySQL database since it has to remember it even though it does not contain any data.

If you want it simpler, then this is how I explain: Assume you have 5 houses which you have rented out to your tenants. If one of the tenants vacates the house, you will get no income from it while you will have to still spend money to maintain it. This extra expenditure on a non-performing asset is termed as overhead.

The simplest way to eliminate this overhead is to fill new data into the empty space. However, if the overload is high, you should consider eliminating the empty space. This can be done by a query to optimize the table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<?php
mysql_connect($host,$username,$password);
mysql_select_db($db);
$table_name="Your_table_name_here"
$sql="OPTIMIZE TABLE '$table_name'";
if(mysql_query($sql))
{
echo "The $table_name has been optimized";
}
else
{
echo "Failed to optimize $table_name ";
}
?>

Alternatively, you can do the same optimization using PhpMyAdmin. If over head is present, it is displayed in the ‘Structure’ tab with a ‘optimize table’ link adjacent to it.