3

I have a database named 'phonebook'. It contains 10+ tables with a moderate amount of data.
Now I want to know the database size of this 'phonebook' database using MySQL query.
How can I do that?

Prodip Kirtania
  • 99
  • 1
  • 11
  • Possible duplicate of [How to get size of mysql database?](https://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database) – Madhur Bhaiya Jul 14 '19 at 06:50

2 Answers2

12

Try this, it provides the size of a specified database in MBs.

Make sure you specify DB_NAME

    SELECT table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
    FROM information_schema.tables  WHERE table_schema='DB_NAME'
    GROUP BY table_schema ;

Hope this will help you ! .

Efrain Plaza
  • 423
  • 1
  • 6
  • 13
Taha
  • 450
  • 4
  • 18
  • 1
    thank you @taha for quick response, it is my desired answer. – Prodip Kirtania Jul 14 '19 at 07:03
  • To get size of all databases, do: ```SELECT table_schema AS 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'DB Size in MB' FROM information_schema.tables GROUP BY table_schema;``` – vintagexav Jul 17 '23 at 09:42
1

The following query can show the size of all the database individually in a table view:

SELECT table_schema 'DB_NAME',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

The following query can show only the size of a specific database:

SELECT table_schema,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables WHERE table_schema='DB_NAME'
GROUP BY table_schema ;

The following query can show total tables, the total table row, DB size of a specific database:

SELECT
TABLE_SCHEMA AS DB_Name,
count(TABLE_SCHEMA) AS Total_Tables,
SUM(TABLE_ROWS) AS Total_Tables_Row,
ROUND(sum(data_length + index_length)/1024/1024) AS "DB Size (MB)",
ROUND(sum( data_free )/ 1024 / 1024) AS "Free Space (MB)"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME'
GROUP BY TABLE_SCHEMA ;

Prodip Kirtania
  • 99
  • 1
  • 11