Check MySQL Database And Table Sizes

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.
check MySQL by phpmyadmin
  • 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.
database tables sizes

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;
				
			
  • 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;
				
			

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.

Share Article

Related Articles