How can you determine how much disk space a particular MySQL table is taking up?

Is there a quick way to determine how much disk space a particular MySQL table is taking up? The table may be MyISAM or Innodb.

For a table mydb.mytable run this for:

#BYTES

SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

#KILOBYTES

SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

#MEGABYTES

SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

#GIGABYTES

SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';

#GENERIC

Here is a generic query where the maximum unit display is TB (TeraBytes)

SELECT 
    CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
FROM
(
    SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    FROM 
    (
        SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
        FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
        FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
        FLOOR(LOG(IF(data_length+index_length=0,1,data_length+index_length))/LOG(1024)) pz
        FROM information_schema.tables
        WHERE table_schema='mydb'
        AND table_name='mytable'
    ) AA
) A,(SELECT 'B KBMBGBTB' units) B;

Give it a Try !!!

Quick bit of SQL to get the top 20 biggest tables in MB.

SELECT table_schema, table_name,
  ROUND((data_length+index_length)/POWER(1024,2),2) AS tablesize_mb
FROM information_schema.tables
ORDER BY tablesize_mb DESC LIMIT 20;

Hope that's useful to somebody!

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via query.

Please see this link from Percona for more information.

https://www.percona.com/blog/2008/12/16/how-much-space-does-empty-innodb-table-take/

Taken from http://support.modwest.com/content/6/199/en/how-do-i-check-how-much-disk-space-my-database-is-using.html">How do I check how much disk space my database is using?

> You can check MySQL table size either by looking at phpMyAdmin in > your control panel by clicking on the database name in the left frame > and reading the size for the tables in there in the right frame.

The below query will as well help to get the same information in bytes

select SUM(data_length) + SUM(index_length) as total_size 
from information_schema.tables 
where table_schema = 'db_name' 
and table_name='table_name';

In linux with mysql installed by default:

[you@yourbox]$ ls -lha /var/lib/mysql/<databasename>

based on NIXCRAFT's mysql db location

Based on the RolandMySQLDBA's answer I think we can use the above to get the size of each schema in a table:

SELECT table_schema, SUM((data_length+index_length)/power(1024,1)) tablesize_kb 
    FROM information_schema.tables GROUP BY table_schema;

Really liked it!

You could perhaps look at the size of the files...

Each table is stored in a couple of separate files inside a folder that is named whatever you called your database. These folders are stored within the mysql data directory.

From there you can do a 'du -sh .*' to get the size of the table on disk.

I would just use 'mysqldiskusage' tool as follow

$ mysqldiskusage --server=user:password@localhost mydbname
# Source on localhost: ... connected.

# Database totals:
+------------+----------------+
| db_name    |         total  |
+------------+----------------+
| mydbaname  | 5,403,033,600  |
+------------+----------------+

Total database disk usage = 5,403,033,600 bytes or 5.03 GB

Most answers above are inaccurate for InnoDB tables. You can replicate this easily.

Add 10,000 dummy records to a table, and run the above suggested queries. Then delete all 10,000 records using delete command (not truncate).

Run above suggested queries again, you will see the size is not 0.

Files on disk only grow but dont shrink even when data is deleted. If you want to shrink, you need to truncate or run something such as OPTIMIZE TABLE.

There seems no reliable way to know how much space InnoDB table records are really consuming. Even if file on disk says 100MB, actual records in DB could be 0.