How to get the sizes of the tables of a MySQL database?

Calculate the total size of the database at the end:

(SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
)
UNION ALL
(SELECT
'TOTAL:',
SUM(round(((data_length + index_length) / 1024 / 1024), 2) )
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
)

SELECT TABLE_NAME AS table_name,
table_rows AS QuantofRows,
ROUND((data_length + index_length) /1024, 2 ) AS total_size_kb
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'db'
ORDER BY (data_length + index_length) DESC;

all 2 above is tested on mysql

I find the existing answers don't actually give the size of tables on the disk, which is more helpful.
This query gives more accurate disk estimate compared to table size based on data_length
& index. I had to use this for an AWS RDS instance where you cannot physically examine the disk and check file sizes.

select NAME as TABLENAME,FILE_SIZE/(1024*1024*1024) as ACTUAL_FILE_SIZE_GB
, round(((data_length + index_length) / 1024 / 1024/1024), 2) as REPORTED_TABLE_SIZE_GB
from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s
join INFORMATION_SCHEMA.TABLES t
on NAME = Concat(table_schema,'/',table_name)
order by FILE_SIZE desc

This should be tested in mysql, not postgresql:

SELECT table_schema, # "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) # "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

- **Size of all tables:**

Suppose your database or `TABLE_SCHEMA` name is ***"news_alert".*** Then this query will show the size of all tables in the database.
```sql
SELECT
TABLE_NAME AS `Table`,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "news_alert"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
```

**Output:**
```sql
+---------+-----------+
| Table | Size (MB) |
+---------+-----------+
| news | 0.08 |
| keyword | 0.02 |
+---------+-----------+
2 rows in set (0.00 sec)
```

- **For the specific table:**

Suppose your `TABLE_NAME` is ***"news"***. Then SQL query will be-
```sql
SELECT
TABLE_NAME AS `Table`,
ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024),2) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "news_alert"
AND
TABLE_NAME = "news"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
```

**Output:**
```sql
+-------+-----------+
| Table | Size (MB) |
+-------+-----------+
| news | 0.08 |
+-------+-----------+
1 row in set (0.00 sec)
```

I've made this shell script to keep a track of table size (in bytes and in number of rows)

#!/bin/sh

export MYSQL_PWD=XXXXXXXX
TABLES="table1 table2 table3"

for TABLE in $TABLES;
do
FILEPATH=/var/lib/mysql/DBNAME/$TABLE.ibd
TABLESIZE=`wc -c $FILEPATH | awk '{print $1}'`
#Size in Bytes
mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$TABLESIZE', 'BYTES');"
#Size in rows
ROWSCOUNT=$(mysql -D scarprd_self -e "SELECT COUNT(*) AS ROWSCOUNT FROM $TABLE;")
ROWSCOUNT=${ROWSCOUNT//ROWSCOUNT/}
mysql -D scarprd_self -e "INSERT INTO tables_sizes (table_name,table_size,measurement_type) VALUES ('$TABLE', '$ROWSCOUNT', 'ROWSCOUNT');"
mysql -D scarprd_self -e "DELETE FROM tables_sizes WHERE measurement_datetime < TIMESTAMP(DATE_SUB(NOW(), INTERVAL 365 DAY));"
done

It presuppose to have this MySQL table

CREATE TABLE `tables_sizes` (
`table_name` VARCHAR(128) NOT NULL,
`table_size` VARCHAR(25) NOT NULL,
`measurement_type` VARCHAR(10) NOT NULL CHECK (measurement_type IN ('BYTES','ROWSCOUNT')),
`measurement_datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
) ENGINE=INNODB DEFAULT CHARSET=utf8

SELECT TABLE_NAME AS "Table Name",
table_rows AS "Quant of Rows", ROUND( (
data_length + index_length
) /1024, 2 ) AS "Total Size Kb"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = 'YOUR SCHEMA NAME/DATABASE NAME HERE'
LIMIT 0 , 30

You can get schema name from "**information_schema**" -> **SCHEMATA** table -> "**SCHEMA_NAME**" column


----------


**Additional**
You can get **size of the mysql databases** as following.

SELECT table_schema "DB Name",
Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema
ORDER BY `DB Size in MB` DESC;


**Result**

DB Name | DB Size in MB

mydatabase_wrdp 39.1
information_schema 0.0


You can [get additional details in here.][1]


[1]:

[To see links please register here]

Heres another way of working this out from using the bash command line.

```
for i in `mysql -NB -e 'show databases'`; do echo $i; mysql -e "SELECT table_name AS 'Tables', round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema =\"$i\" ORDER BY (data_length + index_length) DESC" ; done
```

This is just a note for future reference. All answers are relying on the `I_S.TABLES`. It doesn't tell correct size for instance if you have blob fields in the table. LOB pages are stored in external pages so they are not accounted in the clustered index.
In fact there is a [note][1] :

> For NDB tables, the output of this statement shows appropriate values
> for the AVG_ROW_LENGTH and DATA_LENGTH columns, with the exception
> that BLOB columns are not taken into account.

I found to be true for InnoDB as well.

I have created community [Bug][2] for the same.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

SELECT
table_schema AS `Database`,
table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024 / 1024),as `Size in GB`
FROM information_schema.TABLES `enter code here`
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;