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 (in bytes).
  • INDEX_LENGTH is the length (or size) of the index file for the table (also in bytes).

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;