How to check the size of a MySQL database and table

Databases grow quietly. At some point a backup takes too long, a disk alarm goes off, or a hoster asks for money for more storage, and the first question is always the same: what is actually big in here? You do not need a tool for that. MySQL and MariaDB keep the numbers in information_schema, and three queries are enough to get them.

All databases at a glance

Run this as a user with access to the server, for example root. It lists every database with its size in megabytes, largest first.

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

data_length is the data, index_length the indexes. Counting both is what matters in practice: on a write-heavy table the indexes can be larger than the data itself.

One single database

If you only have access to your own database, name it directly. Replace your_database.

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';

Every table in a database

This is the query that answers the real question — which table is the problem.

SELECT table_name AS tbl,
       engine,
       table_rows AS approx_rows,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
       ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY data_length + index_length DESC;

Two columns are worth reading carefully:

free_mb (data_free) is space inside the table file that is no longer used. A large value usually means many rows were deleted. The file keeps its size until the table is rebuilt.
approx_rows (table_rows) is an estimate for InnoDB, not a count. Treat it as an order of magnitude.

Straight from the shell

For a cron job or a quick look over SSH you do not need a SQL client. The -e flag runs one statement and prints the result as a table.

mysql -u root -p -e "SELECT table_schema AS db, \
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb \
  FROM information_schema.tables GROUP BY table_schema ORDER BY size_mb DESC;"

Add --batch for tab-separated output that another script can read, or --vertical when the lines are too wide for your terminal.

Why the number never matches the disk exactly

A database is more than its tables. du -sh /var/lib/mysql will almost always show more than the queries above, because the data directory also holds the redo log, the undo tablespace, binary logs and temporary files. Binary logs in particular can grow larger than the data — if that is your case, limit how long they are kept.

The other direction happens too: right after a large delete the queries report the old size, because InnoDB statistics are refreshed lazily. ANALYZE TABLE my_table; updates them, and OPTIMIZE TABLE my_table; actually rebuilds the table and hands the free space back to the file system.

A quick check that pays off

Put the per-table query in a monthly reminder. Growth that you see coming is a planning task. Growth that you notice when a backup fails at 3 a.m. is an incident.

Frequently asked questions

Why is the reported size smaller than the files on disk?

For InnoDB the numbers in information_schema are estimates from the storage engine, and they ignore the shared tablespace, the redo log, binary logs and temporary files. A table that lost many rows also keeps its file size until it is rebuilt; the reclaimable part shows up as data_free.

Do these queries work on MariaDB?

Yes. information_schema.tables has the same columns in MySQL 5.7, MySQL 8, MariaDB and Percona Server, so all three queries run unchanged.

Why does the row count look wrong?

table_rows is an estimate for InnoDB, taken from the engine statistics, and can be off by a wide margin. Use SELECT COUNT(*) when you need the exact number, and ANALYZE TABLE to refresh the statistics.

How do I actually free the space after deleting rows?

Rebuild the table with OPTIMIZE TABLE my_table (InnoDB turns this into a table rebuild plus ANALYZE). It needs free disk space of roughly the table size and blocks writes on older versions, so run it in a maintenance window.