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.

(Visited 1 times, 1 visits today)
Tags: ,
Category: SUSE Linux Enterprise Server, Technical Solutions
This entry was posted Thursday, 30 July, 2009 at 3:05 pm
You can follow any responses to this entry via RSS.

Comments

  • jhanger says:

    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

  • Leave a Reply

    Your email address will not be published. Required fields are marked *