SUSE Conversations


Check MySQL Database Size Using SQL Query



By: mendesdomnic

July 30, 2009 3:05 pm

Reads:9229

Comments:1

Rating:4.8

Don’t have access to the command prompt? Here’s how you can find the database size in MySQL. Become a power MySQL user without access to the power of command line.

Execute the following SQL Queries as per your need:

Get Size of all database tables

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES;

Replace YOUR_DB_NAME with your database name in following 2 examples:

Get Size of specific database tables

SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table',
CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;

Get size of entire DB

SELECT 
CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA like '%YOUR_DB_NAME%' ;

Notes:

CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") 

Used to convert to Mb and concat Mb to output making it more meaningful

DATA_LENGTH + INDEX_LENGTH - DATA_FREE

Gives actual size in bytes

That’s all for now. I will be back with more MySQL tips soon.

VN:D [1.9.22_1171]
Rating: 4.8/5 (4 votes cast)
Check MySQL Database Size Using SQL Query, 4.8 out of 5 based on 4 ratings

Tags: ,
Categories: SUSE Linux Enterprise Server, Technical Solutions

Disclaimer: As with everything else at SUSE Conversations, this content is definitely not supported by SUSE (so don't even think of calling Support if you try something and it blows up).  It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test, test, test before you do anything drastic with it.

1 Comment

  1. By:jhanger

    That’s very wonderful, I’ve noticed on loaded servers it’s very slow to execute. I also noted there’s a discrepancy between this number and the numbers the OS sees particularly using `du -sh `


    Joel

Comment

RSS