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?

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.