|
1 2 |
mysql> use information_schema; mysql> select table_schema, sum(DATA_LENGTH)/(1024*1024), sum(INDEX_LENGTH)/(1024*1024) from tables group by table_schema; |
For a table mydb.mytable run this for:
BYTES
|
1 2 3 |
SELECT (data_length+index_length) tablesize FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable'; |
KILOBYTES
|
1 2 3 |
SELECT (data_length+index_length)/power(1024,1) tablesize_kb FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable'; |
MEGABYTES
|
1 2 3 |
SELECT (data_length+index_length)/power(1024,2) tablesize_mb FROM information_schema.tables WHERE table_schema='mydb' and table_name='mytable'; |
GIGABYTES
|
1 2 3 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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; |