Category Archives: Tips and Trix

Get sizes of database and tables in specific database

Often in my day to day work I need to figure out which database on a particular database that take up all the space, one easy way to do this is the following command that you run in your MySQL prompt or preferred MySQL manager:

´┐╝SELECT table_schema "Database Name", 
       SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB" 
       FROM information_schema.TABLES GROUP BY table_schema;

If you need to know which table in a database run the following query:

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

This was inspired from: StackOverflow – How to get the sizes of the tables of a mysql database?