TL;DR: In this quick tip, I provide a simple query for determine the size of a database (that includes all tables). This is useful when determine what the largest tables are, especially if there are custom tables in your WordPress installation, and how large they actually are.
Get the Size of a MySQL Database
To determine the size of a MySQL database, you can run the following query (I’ll explain the details of it after the code):
This will generate two columns:
- The name of the database,
- The size of the database in megabytes.
That that this uses the tables property in the information_schema database to help generate the size of each database. Functions that are helpful to understand here are:
round, will take the values placed in the function and round them to two decimal places (by default; there are other arguments to be passed).sum, as if it wasn’t evident, adds the total of the values placed in the function.- Dividing the results by
1024twice is what will yield megabytes (as opposed to bytes or kilobytes).
And note the other values such as data_length and index_length come from information already stored in the database.
Additional Tools
For reference, I recommend the following tools:
