How to get the sizes of the tables of a MySQL database?
MysqlMysql Problem Overview
I can run this query to get the sizes of all tables in a MySQL database:
show table status from myDatabaseName;
I would like some help in understanding the results. I am looking for tables with the largest sizes.
Which column should I look at?
Mysql Solutions
Solution 1 - Mysql
You can use this query to show the size of a table (although you need to substitute the variables first):
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"
AND table_name = "$TABLE_NAME";
or this query to list the size of every table in every database, largest first:
SELECT
table_schema as `Database`,
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
Solution 2 - Mysql
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.
Solution 3 - Mysql
SELECT
table_name AS "Table",
round(((data_length + index_length) / 1024 / 1024), 2) as size
FROM information_schema.TABLES
WHERE table_schema = "YOUR_DATABASE_NAME"
ORDER BY size DESC;
This sorts the sizes (DB Size in MB).
Solution 4 - Mysql
If you want a query to use currently selected database. simply copy paste this query. (No modification required)
SELECT table_name ,
round(((data_length + index_length) / 1024 / 1024), 2) as SIZE_MB
FROM information_schema.TABLES
WHERE table_schema = DATABASE() ORDER BY SIZE_MB DESC;
Solution 5 - Mysql
-
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.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:
+---------+-----------+ | 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-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:
+-------+-----------+ | Table | Size (MB) | +-------+-----------+ | news | 0.08 | +-------+-----------+ 1 row in set (0.00 sec)
Solution 6 - Mysql
There is an easy way to get many informations using Workbench:
-
Right-click the schema name and click "Schema inspector".
-
In the resulting window you have a number of tabs. The first tab "Info" shows a rough estimate of the database size in MB.
-
The second tab, "Tables", shows Data length and other details for each table.
Solution 7 - Mysql
Try the following shell command (replace DB_NAME
with your database name):
> mysql -uroot <<<"SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"DB_NAME\" ORDER BY (data_length + index_length) DESC;" | head
For Drupal/drush solution, check the following example script which will display the biggest tables in use:
#!/bin/sh
DB_NAME=$(drush status --fields=db-name --field-labels=0 | tr -d '\r\n ')
drush sqlq "SELECT table_name AS 'Tables', round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"${DB_NAME}\" ORDER BY (data_length + index_length) DESC;" | head -n20
Solution 8 - Mysql
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
Solution 9 - Mysql
If you are using phpmyadmin then just go to the table structure
e.g.
Space usage
Data 1.5 MiB
Index 0 B
Total 1.5 Mi
Solution 10 - Mysql
Adapted from ChapMic's answer to suite my particular need.
Only specify your database name, then sort all the tables in descending order - from LARGEST to SMALLEST table inside selected database. Needs only 1 variable to be replaced = your database name.
SELECT
table_name AS `Table`,
round(((data_length + index_length) / 1024 / 1024), 2) AS `size`
FROM information_schema.TABLES
WHERE table_schema = "YOUR_DATABASE_NAME_HERE"
ORDER BY size DESC;
Solution 11 - 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
Solution 12 - Mysql
Another way of showing the number of rows and space occupied and ordering by it.
SELECT
table_schema as `Database`,
table_name AS `Table`,
table_rows AS "Quant of Rows",
round(((data_length + index_length) / 1024 / 1024/ 1024), 2) `Size in GB`
FROM information_schema.TABLES
WHERE table_schema = 'yourDatabaseName'
ORDER BY (data_length + index_length) DESC;
The only string you have to substitute in this query is "yourDatabaseName".
Solution 13 - Mysql
If you have ssh
access, you might want to simply try du -hc /var/lib/mysql
(or different datadir
, as set in your my.cnf
) as well.
Solution 14 - Mysql
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;
Solution 15 - Mysql
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
Solution 16 - Mysql
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"
)
Solution 17 - Mysql
select x.dbname as db_name, x.table_name as table_name, x.bytesize as the_size from
(select
table_schema as dbname,
sum(index_length+data_length) as bytesize,
table_name
from
information_schema.tables
group by table_schema
) x
where
x.bytesize > 999999
order by x.bytesize desc;
Solution 18 - Mysql
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
Solution 19 - Mysql
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 :
> 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 for the same.