How to calculate the sizes of your databases

You may have seen several great queries how to get useful information out of information_schema – so here’s one more:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY size_mb DESC

This query lists all of your databases with their sizes in megabytes in descending order.

If you only want to see those databases exceeding 1 MB, do the following:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
HAVING size_mb > 1
ORDER BY size_mb DESC

Quite simple, but very effective!

Leave a Reply

Your email address will not be published.

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