How to check mysql table size
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_LENGTH
is the length (or size) of all data in the table (inbytes
).INDEX_LENGTH
is the length (or size) of the index file for the table (also inbytes
).
List Table Sizes From a Single Database
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "DB_NAME"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
One specific table
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA= "DB_NAME"
AND
TABLE_NAME= "Table_Name"
ORDERBY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
List All Table Sizes From ALL Databases
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;