1130

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
JPashs
  • 13,044
  • 10
  • 42
  • 65
  • 9
    What do you mean by size? Number of rows? Bytes taken on disk? – Mark Byers Mar 08 '12 at 15:32
  • @Mark i want size on disk is this right method ? # du -sh /mnt/mysql_data/openx/f_scraper_banner_details.MYI 79G /mnt/mysql_data/openx/f_scraper_banner_details.MYI – Ashish Karpe Apr 06 '16 at 14:10
  • 3
    Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38693721). – Drew Aug 01 '16 at 15:40

19 Answers19

2328

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;
George G
  • 7,443
  • 12
  • 45
  • 59
ChapMic
  • 26,954
  • 1
  • 21
  • 20
  • 5
    Thank you, its working just fine, though I am not sure it takes Blobs in consideration. – David Jul 29 '13 at 11:40
  • 7
    Note, you can also use "IN" to specify multiple tables, e.g `AND table_name IN ('table_1', 'table_2', 'table_3');` – David Thomas Nov 07 '13 at 23:01
  • 8
    AFAICT, this will only count the lengths of static size fields correctly. How would you count `VARCHAR` and `BLOB` types? – l0b0 Feb 11 '14 at 15:09
  • 1
    Hi, I notice that the number produced by this query (the top one at least) doesn't always reflect changes I make in my table; i.e. I add rows and the number stays the same after the change. Is there a way to reliably get this number to update according to the present size of the table? – Drewdavid Mar 17 '14 at 18:33
  • 2
    You're dividing the `SUM` by 1024 twice to go from bytes to kilobytes and on to megabytes. This assumes a kilobyte comprises 1024 bytes, but Mac OS uses the 'decimal' system, which says 1 kilobyte = 1000 bytes (http://en.wikipedia.org/wiki/Kilobyte). Shouldn't the `SUM` therefore not be divided by 1000 twice on these systems, or does MySQL somehow compensate/ignore this? – kasimir Aug 04 '14 at 10:07
  • And what if you write a plugin for others and you do not know about their database name $DB_NAME? I can get the database name by `SELECT DATABASE()` but do not know how to combine this with the query above. – Avatar Sep 26 '14 at 13:49
  • 1
    @EchtEinfachTV Replace `"$DB_NAME"` with `(SELECT DATABASE())` – Charlie Gorichanaz Oct 03 '14 at 05:40
  • 8
    @kasimir At some point the world got confusing and some standards organizations and hardware manufacturers decided that it was better that a kilobyte be defined on the decimal system. The IEC standard now calls the base 2 kilobyte (1024 bytes) a kibibyte (KiB). At any rate, MySQL doesn't know, so if you want IEC decimal kilobytes, divide by 1000. – russellpierce Sep 17 '15 at 11:38
  • How to make it when there is no access to information_schema DB? – podarok Sep 07 '16 at 13:26
  • 1
    @Drewdavid You need to run `OPTIMIZE TABLE tablename`. Then you can get a reliable number. – Seperman Oct 05 '16 at 17:18
  • 14
    Will this work for the InnoDB storage engine? According to mysql doc here - http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html, the data_length field for that engine contains the size of the clustered index. That won't correctly represent the size of the data. Will it? – euphoria83 Jan 05 '17 at 20:47
  • optional where clause to restrict to a DB: `where table_schema='mydatabase'` – DynamicDan Jul 09 '17 at 13:20
  • awesome answer, saved me trouble! I used to look at the file sizes on the console, but after migrating to RDS, this is no longer an option – ipolevoy Aug 28 '18 at 15:18
  • Best answer, worked like a treat while other had issues or did not return the expected result. Thanks – Alejandro Moreno Oct 15 '21 at 08:48
  • 2
    It indeed 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. – irsis Mar 31 '22 at 04:34
130
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.

hanshenrik
  • 19,904
  • 4
  • 43
  • 89
Sumith Harshan
  • 6,325
  • 2
  • 36
  • 35
  • How can it be that with the above query I see database sizes of 200 MB, but on the disk it's like 38 GB! Over 2300 mysql-bin.* files, each about 15.6 MB...?! – dokaspar Feb 17 '22 at 09:31
  • @dokaspar the binlogs are separate from the data files. you could rack up a lot of binlogs with lots of activity, insert/delete etc., while the tables are not taking up a lot. Check your expire_log_days https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html - you can save space by keeping less binary logs – mike Oct 09 '22 at 15:23
59
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).

Nicolas Raoul
  • 58,567
  • 58
  • 222
  • 373
Gank
  • 4,507
  • 4
  • 49
  • 45
46

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;
zainengineer
  • 13,289
  • 6
  • 38
  • 28
  • 6
    Or even shorter (without subquery): SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 2) `SIZE_MB` FROM information_schema.TABLES WHERE table_schema=DATABASE() ORDER BY (data_length + index_length) ASC; – Onkeltem Nov 28 '17 at 11:19
32
  • 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)
    
Nurul Akter Towhid
  • 3,046
  • 2
  • 33
  • 35
20

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.

Guppy
  • 426
  • 5
  • 9
  • I didn't have the 'info' tab on my Mac client v 6.0.9 – Neil Mar 29 '16 at 15:04
  • Great!!! In MySQL Workbench there's also a "Table Inspector" for each table. Not properly quick but very handy! – T30 Mar 22 '17 at 09:49
8

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
kenorb
  • 155,785
  • 88
  • 678
  • 743
7

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
Wahyu Kristianto
  • 8,719
  • 6
  • 43
  • 68
user1380599
  • 103
  • 2
  • 6
6

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
Almis
  • 3,684
  • 2
  • 28
  • 58
6

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
Ryum Aayur
  • 95
  • 1
  • 6
  • this should be the answer, for INNODB at least. Just adding up DATA_LENGTH, INDEX_LENGTH & DATA_FREE doesn't get the size on Disk if you have large out-of-row data fields (like blobs). For INNODB you do need to use INNDB_SYS_TABLESPACES.FILE_SIZE to get an *accurate* read of the on disk size, but you also need PROCESS privilege to select from this table. – MNB Sep 03 '20 at 09:23
5

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;
dev101
  • 1,359
  • 2
  • 18
  • 32
4

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.

exic
  • 2,220
  • 1
  • 22
  • 29
  • Finally an answer that doesn't rely on information_schema. In my case it reported 660MB while the actual size on the filesystem is 1.8GB – php_nub_qq May 06 '20 at 14:27
3

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".

Nav
  • 19,885
  • 27
  • 92
  • 135
3

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; 
Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
William
  • 61
  • 3
  • While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find [how to write a good answer](https://stackoverflow.com/help/how-to-answer) very helpful. Please edit your answer - [From Review](https://stackoverflow.com/review/low-quality-posts/21586635) – Nick Dec 04 '18 at 05:51
2
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

William
  • 61
  • 3
1

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"
)
janith1024
  • 1,042
  • 3
  • 12
  • 25
1

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.

irsis
  • 952
  • 1
  • 13
  • 33
0
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;
Zach
  • 517
  • 2
  • 8
-1

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
Thibault Richard
  • 356
  • 2
  • 11