Checking MySQL Database Sizes
Quick tip: want to know how large each of your MySQL 5 databases is? This query pulls the row counts, data size, index size, and total size from `information_schema`:
```sql
mysql> SELECT
table_schema,
concat(round(sum(table_rows)/1000000,2),'M') as rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') as data,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') as idx,
concat(round(sum((data_length+index_length))/(1024*1024*1024),2),'G') as total_size
FROM information_schema.TABLES
GROUP BY table_schema;
+-----------------------------+-------+-------+-------+------------+
| table_schema | rows | data | idx | total_size |
+-----------------------------+-------+-------+-------+------------+
| information_schema | NULL | 0.00G | 0.00G | 0.00G |
| xxxxxxxxx_xxxx_xxxx_staging | 0.93M | 0.08G | 0.01G | 0.09G |
+-----------------------------+-------+-------+-------+------------+
2 rows in set (0.03 sec)
```
It's one of those queries worth keeping in your back pocket. Handy for capacity planning, spotting unexpectedly large databases, or just satisfying your curiosity about where all that disk space went.
These posts are LLM-aided. Backbone, original writing, and structure by Craig. Research and editing by Craig + LLM. Proof-reading by Craig.