2

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
  • 89
  • 1
  • 9
  • 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

8

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
  • 373
  • 4
  • 10
Taha
  • 340
  • 3
  • 14
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
  • 89
  • 1
  • 9