10

Is there a query or function that I can use to determine the size of a database in MySQL? If not what is the typical way to find the size of database in MySQL?

I was googling and found SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;

And it returns a database that I know is 400MB to be 474989023196466.25 MB!

Improve this question
6
  • In all of my tables, data_free is much larger than data_length, meaning that your query would return a negative number. Perhaps it is interpreting it as an unsigned positive integer, hence the nonsensical result. When I try pasting in that query, I get the error "BIGINT UNSIGNED value is out of range". Commented Aug 7, 2012 at 18:38
  • For size of a specific database/table answer provided in this page will help you rathishkumar.in/2017/12/… Commented Dec 29, 2017 at 12:45
  • 1
    Does this answer your question? How to get size of mysql database? Commented Jun 6, 2021 at 16:27
  • @frederj your link is to question asked AFTER this one Commented Jun 6, 2021 at 19:56
  • @Prophet The date of the question doesn't really matter. The target question should be the best one, based on the question, and posted answers. Commented Jun 6, 2021 at 23:31

3 Answers 3

Reset to default
25

Try with this query :

SELECT table_schema AS "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 AS "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Or with this, if you want to ROUND : 

SELECT table_schema AS "Data Base Name", ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Try:

SELECT table_schema, sum(data_length + index_length) FROM information_schema.TABLES GROUP BY table_schema;
Improve this answer

Comments

0 
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;
Improve this answer

Comments