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?
This question is related to
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)
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;
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
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.
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;
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"
)
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.
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).
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;
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
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".
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
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 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;
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
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;
Result
DB Name | DB Size in MB
mydatabase_wrdp 39.1
information_schema 0.0
You can get additional details in here.
Source: Stackoverflow.com