This article shows you how to check MySQL database and table sizes. You can do this by using the phpMyAdmin web interface or using the command-line MySQL program.
Check MySQL Database Using phpMyAdmin
To check the sizes of MySQL databases and tables using PHPMyAdmin, follow these steps:
- Log in to cPanel.
- After that, locate the DATABASES section. Then click phpMyAdmin. Then the phpMyAdmin administration page appears in a new window.
- In the left pane, click the name of the database that you want to view.
- Then, in the right pane, locate the Size column. phpMyAdmin lists the size of each table in the database.
Note
If the database contains a large number of tables, you may need to click the > icon to advance to the next page of tables. To obtain the total database size, add together the size totals from each page.
Checking MySQL Databases Using The MySQL Command-line Program
To check the sizes of MySQL databases and tables using the MySQL command-line program, follow these steps:
- Firstly, log in to your account using SSH.
- Type the following command, replacing the username with your username account.
mysql -u username -p
- Then, type your password, at the Enter Password prompt. When you type the correct password, the mysql> prompt appears.
- To check 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;
Note
This command may take a minute or two to complete, depending on how many databases you have and how large they are. After the command finishes, it lists all of your databases and their corresponding size (in megabytes).
- Finally, to check the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command.
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;
Note
Replace database_name with the name of the database that you want to check.
After the command finishes, it lists all of the tables and their corresponding size (in megabytes), with the largest table at the top and the smallest table at the bottom.