Forum Topic

Thanks for your participation and contribution.


Back to MySQL Queries

MySQL Database Size Information

Post a reply
2658 views
i90runner
i90runner
MySQL Database Size Information
 

SELECT DBName,
       CONCAT(LPAD(FORMAT(SDSize / POWER(1024, pw), 3), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') 'Data Size',
       CONCAT(LPAD(FORMAT(SXSize / POWER(1024, pw), 3), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') 'Index Size',
       CONCAT(LPAD(FORMAT(STSize / POWER(1024, pw), 3), 17, ' '), ' ', SUBSTR(' KMGTP', pw + 1, 1), 'B') 'Total Size'
FROM
(
    SELECT IFNULL(DB, 'All Databases') DBName,
           SUM(DSize) SDSize,
           SUM(XSize) SXSize,
           SUM(TSize) STSize
    FROM
    (
        SELECT TABLE_SCHEMA DB,
               data_length DSize,
               index_length XSize,
               data_length + index_length TSize
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'information_schema', 'performance_schema' )
    ) AAA
    GROUP BY DB WITH ROLLUP
) AA ,
(SELECT 3 pw) BB
ORDER BY (SDSize + SXSize);

SELECT IFNULL(db,'Total') 'Database',
datsum / power(1024,pw) 'Data Size',
ndxsum / power(1024,pw) 'Index Size',
totsum / power(1024,pw) 'Total'
FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum
FROM (SELECT table_schema db,data_length dat,index_length ndx
FROM information_schema.tables WHERE engine IS NOT NULL
AND table_schema NOT IN ('information_schema','mysql')) AA
GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;

-- Database Size Information by Table

SELECT Statistic,DataSize 'Data Size',IndexSize 'Index Size',TableSize 'Table Size'
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,'Storage for All Databases',
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT('Storage for ',B.table_schema),
CONCAT(B.engine,' Tables for ',B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;


Last updated

1-1 of 1

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.