Database and Table Size

“How big is my database?” is a question you will ask when disk space runs low or before a backup. MySQL answers it with INFORMATION_SCHEMA.TABLES.

Let us create a table and fill it with a few thousand rows to have something to measure.

1
2
3
4
5
6
7
8
9
10
11
12
13

SET SESSION cte_max_recursion_depth = 10000;

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(200) NOT NULL,
    created_at DATETIME NOT NULL
);

INSERT INTO logs (message, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 5000)
SELECT CONCAT('Event number ', i), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;

SELECT COUNT(*) AS rows_inserted FROM logs;

1
2
3
4
5
6

+---------------+
| rows_inserted |
+---------------+
|          5000 |
+---------------+
1 row in set

(The WITH RECURSIVE query is a recursive CTE. It generates the numbers 1 to 5000. MySQL stops a recursive query after 1000 steps by default, so the first line raises the limit for this session. You will learn it in the CTE chapter.)

Size of one database

1
2
3
4
5

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

1
2
3
4
5
6

+-------+---------+
| db    | size_mb |
+-------+---------+
| learn |    0.27 |
+-------+---------+
1 row in set

(The exact number differs from one server to the next, because it depends on page layout and statistics. That is why the output is not compared here.)

Size of each table

1
2
3
4
5
6
7

SELECT table_name,
       table_rows AS approx_rows,
       ROUND(data_length / 1024, 1) AS data_kb,
       ROUND(index_length / 1024, 1) AS index_kb
FROM information_schema.tables
WHERE table_schema = 'learn'
ORDER BY data_length + index_length DESC;

1
2
3
4
5
6

+------------+-------------+---------+----------+
| table_name | approx_rows | data_kb | index_kb |
+------------+-------------+---------+----------+
| logs       |        5000 |   272.0 |      0.0 |
+------------+-------------+---------+----------+
1 row in set

Caveats

• The numbers are approximations, taken from statistics. After a large change they may lag. Run ANALYZE TABLE logs; to refresh them.
• information_schema values are cached (information_schema_stats_expiry, 24 hours by default in MySQL 8). Run ANALYZE TABLE or set the variable to 0 for fresh numbers during a check.
• The size on disk can be bigger than data_length + index_length, because of free space, the redo log, the undo log and the binary logs.
• Large text and BLOB columns may be stored off-page and still count in data_length.

Why it matters

• Backups and restores take time proportional to size.
• A table that grows by gigabytes should have a retention plan: archive or delete old rows (DELETE).
• Index size is part of the cost of an index (Indexes).