I've two or more databases in my mysql server. Total memory used by the databases are around 14GB. I want to know, memory used by each database and their tables.
Add a comment
|
4 Answers
This should work
SELECT table_schema "table name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;
It will display the database sizes in two column format => | database name | database size |
Comments
Try following:
SHOW TABLE STATUS LIKE 'TABLENAME'
1 Comment
Rads
This I've tried. But I need to get details on a database level. Ex: I've two databases DB1 & DB2, DB1 may be using around 10GB of data & DB2 may be using around 4GB of data. How do I get this result?
You can go to this location-
C:\ProgramData\MySQL\MySQL Server 5.5\data\
Under this location u will find the dtabasename folder under which there will be files with the name of the tables, So you can simply right click and check the size of the table on disk.
I hope this is what you were looking for, or you can use:-
SHOW TABLE STATUS LIKE 'tableName';
Note:- Turn on Show hidden files, i am using engine=MYISAM, i m using C: -drive as my operating system is installed in it.
Comments
Use this below query to to verify all the tables data size in MB from single database
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "yourDatabaseName"
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;
