This can be accomplished easily with the following query: SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.
Analyze the total space used on the DB instance (user-created databases) To find the size of each user-created database, run the following query: mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.
For a table mydb.mytable
run this for:
SELECT (data_length+index_length) tablesize
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';
SELECT (data_length+index_length)/power(1024,1) tablesize_kb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';
SELECT (data_length+index_length)/power(1024,2) tablesize_mb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';
SELECT (data_length+index_length)/power(1024,3) tablesize_gb
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='mytable';
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/
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!
Taken from 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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With