How to Check MySQL Database Size

How to Check MySQL Database Size is not a daunting task for a new developer. MySQL is one of the most popular open-source databases in the world, often being used by some of the world’s biggest websites such as Facebook, Twitter, YouTube, and Wikipedia among others.

MySQL Uses

The uses of MySQL are vast and include powering small blogs, high-volume websites, business applications, and more. In this guide, we are going to show you how to check the size of the MySQL database and tables by using the MySQL shell.

Check out Our Best VPS Hosting and WordPress hosting for scaling your cloud-based applications and processes.

Although this post was made for MySQL, you may still use the same commands for MariaDB.

Check MySQL Database Size

For checking the size of the MySQL databases, we can use a virtual database called ‘information_schema’ .  It has access to database metadata such as the size of the databases and tables.

• Type in the MySQL shell as root user by running the command below.

mysql -u root -p

• In order to print the size of every database in your MySQL, simply execute the command below.

SELECT
table_schema "Database Name",
ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Database Size in MB"
FROM
information_schema.TABLES
GROUP BY table_schema ;
You should receive an output similar to the one below:
+-----------------------+---------------------+
| Database Name         | Database Size in MB |
+-----------------------+---------------------+
| admin_110             |              251.28 |
| admin_120             |               18.99 |
| admin_25              |               11.86 |
| admin_562             |               28.45 |
| admin_877             |                4.20 |
| information_schema    |                0.14 |
| mysql                 |                0.69 |
| performance_schema    |                0.00 |
| user_174              |               23.72 |
| user_379              |                0.26 |
| user_604              |                0.64 |
| user_625              |                1.22 |
| user_820              |                0.73 |
| user_854              |                2.25 |
| user_901              |                1.10 |
| user_961              |                1.60 |
+-----------------------+---------------------+

Checking the size of all Tables in a specific MySQL database

In order to print the size of every table in a particular MySQL database, we are going to use the information from the virtual database ‘information_schema’.

SELECT
table_name as "Table Name",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM
information_schema.TABLES
where
table_schema = 'admin_562';
You should receive an output similar to the one below:
+-----------------------+------------------+
| Table Name            | Table Size in MB |
+-----------------------+------------------+
| wp_commentmeta        |             0.05 |
| wp_comments           |            12.42 |
| wp_links              |             0.03 |
| wp_options            |             1.98 |
| wp_postmeta           |             0.19 |
| wp_posts              |             1.75 |
| wp_term_relationships |             0.06 |
| wp_term_taxonomy      |             0.05 |
| wp_termmeta           |             0.05 |
| wp_terms              |             0.05 |
| wp_usermeta           |             0.05 |
| wp_users              |             0.06 |
| wp_wfBadLeechers      |             0.02 |
| wp_wfBlockedIPLog     |             0.06 |
| wp_wfBlocks           |             0.03 |
| wp_wfBlocksAdv        |             0.02 |
| wp_wfConfig           |             3.28 |
| wp_wfCrawlers         |             0.02 |
| wp_wfFileMods         |             1.52 |
| wp_wfHits             |             2.20 |
| wp_wfHoover           |             0.03 |
| wp_wfIssues           |             2.52 |
| wp_wfKnownFileList    |             0.20 |
| wp_wfLeechers         |             0.05 |
| wp_wfLockedOut        |             0.02 |
| wp_wfLocs             |             0.02 |
| wp_wfLogins           |             0.08 |
| wp_wfNet404s          |             0.03 |
| wp_wfNotifications    |             0.02 |
| wp_wfPendingIssues    |             0.31 |
| wp_wfReverseCache     |             0.02 |
| wp_wfSNIPCache        |             0.06 |
| wp_wfScanners         |             0.02 |
| wp_wfStatus           |             1.17 |
| wp_wfThrottleLog      |             0.03 |
| wp_wfVulnScanners     |             0.02 |
+-----------------------+------------------+

Checking the size of a specific Table in a specific MySQL database

Finally, in order to check a particular table size, we are going to use its database name, the table name, and by querying the virtual database ‘information_schema’:

SELECT
table_name as "Table Name",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Table Size in MB"
FROM
information_schema.TABLES
where
table_schema = 'admin_877' and table_name = 'wp_wfConfig';

You should receive an output similar to the one below:
 +-------------+------------------+
| Table Name  | Table Size in MB |
+-------------+------------------+
| wp_wfConfig |             3.28 |
+-------------+------------------+