MySQL’s SHOW TABLE STATUS and phpMyAdmin provide an overview of the usage of each table. This often differs (a lot) from what is actually in use on disk because of multiple reasons and possibly a combination of them.
Why is there a difference between MySQL and phpMyAdmin?
Indices
When creating a table, you can define one or more indices. An index contains (part of) the data for a specific column to filter the results with references to the full record information. This means that part of the data may be duplicated on disk.
Fragmentation or “empty” spaces in the data file
MySQL doesn’t actually remove data after deleting a record in the middle of a table, but marks it as “deleted” so new data can reuse the space. In situations where this doesn’t happen the data still occupies some disk space, causing your table to be larger on disk than what MySQL shows.
Over time, for certain types of tables (primarily tables used to store cached data) with volatile data, the space taken up by deleted records grows fast. Making up 50% or even >100% of the size of the actual useful data.
Lazy counting by MySQL
Because it’s resource intensive to read the whole data file from disk to calculate the usage, MySQL tries to be smart by estimating some numbers based on a sample of data from the table. Otherwise the performance of your MySQL queries would suffer, resulting in high latency and slow websites.
This data sample might no longer be realistic over time, causing an underestimation of the effective disk usage.
Amount of tables
Next to having smart queries and adding the right indices, the structure of the database is very important. Only add the tables you really need. Every table creates a bit overhead and the more tables the bigger the overhead becomes.
Solutions
Re-analyzing or optimizing the tables suspected of using more on disk than what MySQL reports often helps. There are a few ways to do this:
- To solve the lazy counting, it might be sufficient to just run
ANALYZE TABLE <tablename>; - To solve the fragmentation (which can also occur in indices), you could trigger a rebuild with either:
OPTIMIZE TABLE <tablename>;ALTER TABLE <tablename> ENGINE=InnoDB;(only if this is an InnoDB table, the previous option is a safer method since it works for all types)
Benefits of having an optimised database
Besides using less disk space, an optimised database has some other advantages.
- Accessing larger databases require more memory and CPU from the system, resources that could be used for other important processes.
- Making a backup of an optimised database takes less time, which in turn will benefit the general server performance during backup windows.
- Querying unoptimised tables may slow down your website.