This article demonstrates how to determine MySQL database size and MySQL table size. You can check this by using the command line.
Determine MySQL databases and tables Size Using the Command Line
You can use the MySQL command-line program to determine the sizes of MySQL databases and tables. To do this, follow these steps:
- Log in to your MySQL account using SSH/CMD/Terminal.
At the command line, type the following command with your MySQL username
mysql -u [type username] -p
It will ask password prompt so type your password. When you type the correct password, the mysql> prompt appears.
- To determine the sizes of all of your databases, at the mysql> prompt type the following command:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;
Depending on how many databases you have and how large they are, this command may take a minute or two to complete. After the command finishes, it displays a list of all of your databases and their corresponding size (in megabytes).
- To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check:
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;
After the command finishes, it displays a list of all of the tables and their corresponding size (in megabytes), with the largest table at the top and smallest table at the bottom.
