Ralph J. Smit Laravel Software Engineer
Getting the total database size in MySQL is not hard. It basically requires a simple if-statement.
NB.: this article might work on other platforms as well, but I didn't personally test those. If you do, please let me know!
Run the following
SELECT statement on your database. Make sure to add the name of your database at the end:
SELECTsize_mbFROM (SELECTtable_schema as name,ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) as size_mbFROMinformation_schema.tablesGROUP BYtable_schema) alias_oneWHEREname = 'your_database_name'
And the result:
The query will get the total size of the database in MB. If you want the size in GB, you should add another divide by 100 like the below query. For more precise numbers, update the second parameter of the
ROUND function to the number of decimals you want (e.g. 2 decimals for a precision like 4.71GB:
SELECTsize_gbFROM (SELECTtable_schema as name,ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1000, 2) as size_gbFROMinformation_schema.tablesGROUP BYtable_schema) alias_oneWHEREname = 'your_database_name'
Getting the size of all databases in the current MySQL instance
You can get even get a list of all the database sizes for the current MySQL instance. You can do so by removing the outer
SELECT with the
WHERE and only keep the inner
SELECTtable_schema AS name,ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1000, 2) AS size_gbFROMinformation_schema.tablesGROUP BYtable_schema
Published by Ralph J. Smit on in
Like what you read?
Get notified when I publish something new, and unsubscribe at any time.