If you have looked at the size of your database through phpMyAdmin and compared it to the size displayed in our Admin Panel, chances are that you’ve noticed a discrepancy in size. At most times it’s a slight discrepancy, but it can become a higher number under the right circumstances. In this article we’ll explain the discrepancy and how the numbers are calculated.
Understanding How Database Size is Calculated
To understand the reasoning behind the discrepancy in size, it’s important to know how MySQL generally saves the changes made to the database to the filesystem.
When you update, delete, or insert data in the database, MySQL writes these changes to the filesystem as quickly as possible. However, this process can lead to fragmentation, where the data is scattered across different locations on the disk.
How Our Admin Panel Calculates the Size
Our Admin Panel counts the total size of the database as it’s stored on the filesystem. If any fragmentation has occurred, the empty bits inside the database storage will also account for that.
How phpMyAdmin Calculates the Size
On the other hand, phpMyAdmin will look at the actual size of your data. Fragmentation is accounted for, and it will only display the size of your tables.
Database Fragmentation Impacts the Database Size
Let’s take a quick example to show what this means. Say our website is a blog with an added store. The blog has been running for quite some time already. The database has grown over time, and its size is now 150MB. We decide to update some of the blog posts as new information emerges. When we’re done, we delete some products from the store, as they’ve been unavailable for at least a year.
First, we update our posts. They become a bit bigger than before, but our site sends the needed update commands to MySQL. When rows are deleted or updated, gaps are left behind, and new data might not fit perfectly into these gaps, causing the database to split data across different locations on the file system.
For our second action, we are deleting some entries from the products. Again, this will leave gaps that are not filled right away.
Now, when we look at the database size in the Admin Panel again, it’s suddenly at 155MB! What happened here is that the data got fragmented, taking up more room on the filesystem than it actually should. If we look at the size in phpMyAdmin, we saved some space. If we want to keep our database at top performance, it’s important to optimize it every now and then. This can be achieved by following the steps listed in this tutorial. Following the tutorial will take care of any fragmentation and boost the performance of your database queries!
If you’d like to learn more about database performance and how to set up indexes, check out our guide on Speeding Up Database Queries With Indexes.
As always, should you have any additional questions, please don’t hesitate to contact us through our support chat at servebolt.com!
