How to determine the size of MySQL databases and tables

This article demonstrates how to determine the size of MySQL databases and tables. You can do this by using the phpMyAdmin web interface or by using the command line.

Using phpMyAdmin

You can use the phpMyAdmin web interface to determine the sizes of MySQL databases and tables. To do this, follow these steps:

1. Log in to cPanel.
   If you do not know how to log in to your cPanel account, please see this article.
2. In the Databases section of the cPanel home screen, click phpMyAdmin. The phpMyAdmin administration page appears in a new window.
3. In the left pane, click the name of the database that you want to view.
4. In the right pane, locate the Size column. phpMyAdmin displays the size of each table in the database:

   

5. To obtain the total size of the database, scroll down to the end of the Size column:
   

   

   If the database contains a large number of tables, you may need to click the > icon to advance to the next page of tables. Add together the size totals on each page to obtain the total database 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:

1. Log in to your account using SSH.
2. At the command line, type the following command, replacing username with your Webhost.Berlin account username:

   mysql -u username -p

3. At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.

4. 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).

5. 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.

Related Articles

• phpMyAdmin and phpPgAdmin

  You can use phpMyAdmin and phpPgAdmin to manage all aspects of your databases. You can create and browse tables, import data, and much more.

• Connecting to MySQL from the command line

  You use the mysql program to connect directly to MySQL from the command line. Learn how here.

• The information_schema database

  When you view MySQL databases in phpMyAdmin or by using the mysql command-line program, you may also notice an information_schema database. Learn about this database and its purpose here.