Optimize MySQL Table and Shrink the File Size

 

These are the given 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