This howto outlines the steps to optimize a MySQL table, and shrink the physical file size of the file. This can be useful if you have removed a large number of records from a table, but the actual file size of that table on the disk hasn’t changed.
Run the MySQL client:
$ mysql -u <username> -p <databasename>
Note: Replace ‘<databasename>’ with the name of your database that contains the table you want to shrink / optimize, and replace <username> with a MySQL username that you want to connect to MySQL using.
You will be asked for a password. If you don’t have a password set, simply press enter when prompted.
Run the following command to optimize the table and shrink the file size:
OPTIMIZE TABLE <tablename>;
Note: Replace <tablename> with the name of the table you want to optimize.
The table will now be optimized and you should see the output similar to the following:
mysql> optimize table tablename; +------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------+----------+----------+----------+ | databasename.tablename | optimize | status | OK | +------------------------+----------+----------+----------+ 1 row in set (0.45 sec)
If the table is already optimized, you will get the following output:
mysql> optimize table tablename; +------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------+----------+----------+-----------------------------+ | databasename.tablename | optimize | status | Table is already up to date | +------------------------+----------+----------+-----------------------------+ 1 row in set (0.01 sec)
Exit the MySQL client:
exit
