I see a difference in database storage space between MySQL and the actual disk usage in phpMyAdmin

When you view the overview of your database in phpMyAdmin, you can see at a glance how much data is in use. However, in many cases, the amount displayed does not match the amount of data actually written to the disk.

In this article, we explain why this can happen and what you can do to resolve this difference.

Causes

There are several causes that can lead to this difference in data display:

• Empty spaces are created in the database when data is deleted. These empty spaces still take up space until new data is written to the same location. 
   
• MySQL does not read every file to determine how much data is in use. To save resources, the amount of data is estimated. 
   
• The more complex the database, the larger the difference can be between what is displayed and the actual amount of data in use. Each additional table can result in another discrepancy. Therefore, make sure you do not add unnecessary tables. 
   
• Database indexes are used to improve the speed of lookups in tables. The index contains part of the data in a table, which means this data can be stored twice on the disk.

Solutions

There are two MySQL commands you can run to resolve the difference in data display.

• Reanalyze all data in a table. This analyzes all data instead of using a rough estimate:

ANALYZE TABLE <tablename>

• Optimize a table. This removes empty spaces in a table:

OPTIMIZE TABLE <tablename>

You can perform these actions using SQL commands, but phpMyAdmin has convenient built-in options for these actions that you only need to click:

1. Click the name of your database in the top right to open it. 
2. Then click ‘Check all’ to select all tables in your database. 
3. Next, click the drop down menu next to it.
4. Select the action you want to perform under ‘Table maintenance’. As soon as you select the desired option, it will be executed immediately.

In the article ‘Cleaning up your database’, you can find more information about the other Table maintenance options.