Yesterday I was trying to investigate through an odd size increment affecting one of our MySQL databases. Since I didn't knew about the table(s) affected by the issue, the first thing I needed to do was to take a look at each table size among the various DB that were there.
I managed to perform such analysis in few minutes with these great queries, loosely based upon those present within this StackOverflow answer.
Size of a single table in a single database
|
1 2 3 4 5 6 7 |
SELECT table_name AS `Table`, data_length + index_length `Size (Bytes)`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "[yourDB]" AND table_name = "[yourTABLE]"; |
Size of all tables in a single database (largest first)
|
1 2 3 4 5 6 7 8 |
SELECT table_name AS `Table`, data_length + index_length `Size (Bytes)`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "[yourDB]" AND table_name = "$TABLE_NAME" ORDER BY (data_length + index_length) DESC; |
Size of all tables in every database (largest first)
|
1 2 3 4 5 6 7 |
SELECT table_schema AS `Database`, table_name AS `Table`, data_length + index_length `Size (Bytes)`, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; |
Size of all Databases (largest first)
|
1 2 3 4 5 6 |
SELECT table_schema "DB Name", SUM(data_length + index_length) `Size (Bytes)`, ROUND(SUM((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.tables GROUP BY table_schema ORDER BY SUM(data_length + index_length) DESC; |
- Right-click the schema name and click "Schema inspector".
- In the resulting window you have a number of tabs. The first tab "Info" shows a rough estimate of the database size in MB.
- The second tab, "Tables", shows Data length and other details for each table.
That's it for now... happy querying!
