How to find out a particular tablesize in mysql


mysql> 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(data_length+index_length)/LOG(1024)) pz
    ->         FROM information_schema.tables
    ->         WHERE table_schema='cyclexaftest'
    ->         AND table_name='log_url'
    ->     ) AA
    -> ) A,(SELECT 'B KBMBGBTB' units) B;
+---------+---------+----------+
| DATSIZE | NDXSIZE | TBLSIZE  |
+---------+---------+----------+
| 5.52 MB | 6.03 MB | 11.55 MB |
+---------+---------+----------+
1 row in set (0.00 sec)


-----------------------------------------------------
Another query:-

mysql> SELECT table_name AS `Table`,
    -> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "cyclexaftest"
    ->  AND table_name = "log_url";
+---------+------------+
| Table   | Size in MB |
+---------+------------+
| log_url |      11.55 |
+---------+------------+
1 row in set (0.00 sec)



Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...