How to calculate the MySQL database size

In this guide, we’ll explore methods to calculate the size of your MySQL database using SQL queries and system commands.

Usage

Method 1: Using SQL Query

You can calculate the size of your MySQL database by running a SQL query. Simply execute the following query:

SELECT 
	table_schema "DB name", SUM(data_length + index_length) / 1024 / 1024 "Size (MB)"
FROM 
	information_schema.tables
GROUP BY 
	table_schema;

This query will display the size of each database in megabytes. Sum up the data_length + index_length is equal to the total table size.

1. data_length – store the real data.
2. index_length – store the table index.

Another SQL script to list out one database size, and each table’s size in detail

SELECT
	table_name,
	table_rows,
	data_length,
	index_length,
	round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM
	information_schema.TABLES
WHERE
	table_schema = "database_name";

Replace database_name with your DB name.

Before running the SQL query to calculate the size of the MySQL database, it’s recommended to run the command OPTIMIZE TABLE {table_name}; or ANALYZE TABLE {table_name}; for each table in the database. This command updates table statistics, ensuring more accurate size calculations.

The OPTIMIZE TABLE command not only updates table statistics like ANALYZE TABLE, but it also defragments the table, potentially reducing its size and improving performance. However, note that OPTIMIZE TABLE may require exclusive access to the table, which can impact database availability during the optimization process.

Method 2: Using System Commands

Alternatively, you can use system commands to calculate the size of your MySQL database. Execute the following command in your terminal:

# macos
➜ du -sh /usr/local/var/mysql/{your_database_name}

# linux
➜ du -sh /var/lib/mysql/{your_database_name}

Replace {your_database_name} with the name of your database. This command will display the size of your database directory in a human-readable format.

FAQ

References

• MySQL Documentation: information_schema.tables
• Linux Man Page: du command