Get table or DB size im Megabyte

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB:

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

This query selects all tables from all connected databases. If you want to select a certain one, you can add a where-clause like that:

WHERE table_schema = "$DBNAME"

And if you need only the size of a single table, you can take this where clause:

WHERE table_name = "$TABLENAME"

And if you need the all over size of your database, you can use the following query:

SELECT 
    SUM(round(((data_length + index_length) / 1024 / 1024), 2)) AS "Overall size in MB"
FROM information_schema.TABLES;

Leave a Reply

Your email address will not be published.

 

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