How to get the size of a table in MySQL
Posted by: AJ Welch
Like most relational databases, MySQL provides useful metadata about the database itself. While most other databases refer to this information as a catalog, the official MySQL documentation refers to the INFORMATION_SCHEMA metadata as tables.
Regardless of the name, what matters is the information provided by these INFORMATION_SCHEMA tables. Everything from views and user_privilieges to columns and tables can be found in the INFORMATION_SCHEMA. For our purposes we’re particularly interested in the tables metadata, which we can query to actually extract the size of various tables in the system.
List table sizes from a single database
As can seen in the official documentation, the INFORMATION_SCHEMA.TABLES table contains around 20 columns, but for the purpose of determining the amount of disk space used by tables, we’ll focus on two columns in particular: DATA_LENGTH and INDEX_LENGTH.
DATA_LENGTHis the length (or size) of all data in the table (inbytes).INDEX_LENGTHis the length (or size) of the index file for the table (also inbytes).
Armed with this information, we can execute a query that will list all tables in a specific database along with the disk space (size) of each. We can even get a bit fancier and convert the normal size values from bytes into something more useful and understandable to most people like megabytes.
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "bookstore"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;In this example using the bookstore database, we’re combining the DATA_LENGTH and INDEX_LENGTH as bytes, then dividing it by 1024 twice to convert into kilobytes and then megabytes. Our result set will look something like this:
+----------------------------------+-----------+
| Table | Size (MB) |
+----------------------------------+-----------+
| book | 267 |
| author | 39 |
| post | 27 |
| cache | 24 |
...If you don’t care about all tables in the database and only want the size of a particular table, you can simply add AND TABLE_NAME = "your_table_name" to the WHERE clause. Here we only want information about the book table:
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "bookstore"
AND
TABLE_NAME = "book"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;The results, as expected, are now:
+-------+-----------+
| Table | Size (MB) |
+-------+-----------+
| book | 267 |
+-------+-----------+
1 row in set (0.00 sec)List all table sizes from ALL databases
If you’re running into an issue where your database is growing in size but you don’t know which table is the culprit, it may be useful to query for the size of all tables within all databases in the entire system. This can be accomplished easily with the following query:
SELECT
TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;This will return not only the size of the table, but also the table name and parent database it is associated with.