Are you trying to find which databases in your MySQL/MariaDB database server has large tablespace?. People have varying reasons for finding database sizes in MySQL/MariaDB. It could be for data truncation, data archiving, table optimizations among many other applications.

In this how-to guide, I’ll share with you a query you can use in your MySQL/MariaDB database server to find the size of each Database.

Launch MySQL / MariaDB shell console:

$ mysql -u root -p

Run the query below:

SELECT
	COUNT(*) AS Total_Table_Count
	,table_schema
	,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS Total_Row_Count
	,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS Total_Table_Size
	,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS Total_Table_Index
	,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') Total_Size
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length+index_length)
DESC LIMIT 10;

This query will:

  • Find the number of tables in each database
  • Total number of rows in a database
  • Total table size and Index
  • Total table size in GB

Below is a sample output:

+-------------------+--------------------+-----------------+------------------+-------------------+------------+
| Total_Table_Count | table_schema       | Total_Row_Count | Total_Table_Size | Total_Table_Index | Total_Size |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
|               124 | b4gaags_db         | 1.72M           | 0.10G            | 0.06G             | 0.16G      |
|                33 | mutima             | 0.44M           | 0.10G            | 0.03G             | 0.13G      |
|                55 | gitea              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                48 | kanboard           | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                31 | mysql              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                76 | information_schema | NULL            | 0.00G            | 0.00G             | 0.00G      |
|                 3 | zourfs             | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                52 | performance_schema | 0.07M           | 0.00G            | 0.00G             | 0.00G      |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
8 rows in set (0.027 sec)

The ORDER BY DESC LIMIT 10 will show the database sizes from the largest to smallest. Adjust the LIMIT 10 to print more records.

Best Udemy Video Courses to Learn MySQL / MariaDB Databases:

Your support is our everlasting motivation,
that cup of coffee is what keeps us going!


As we continue to grow, we would wish to reach and impact more people who visit and take advantage of the guides we have on our blog. This is a big task for us and we are so far extremely grateful for the kind people who have shown amazing support for our work over the time we have been online.

Thank You for your support as we work to give you the best of guides and articles. Click below to buy us a coffee.

LEAVE A REPLY

Please enter your comment!
Please enter your name here