Getting MySQL Database Storage Sizes

If you’re managing a MySQL database, it’s essential to monitor how much disk space your tables and databases are consuming. Whether you’re troubleshooting performance issues or planning storage upgrades, knowing the exact size of your tables can be incredibly helpful.

📊 View Table Sizes in MySQL

To get the size of each table in a specific database, use the following SQL query. This query uses the information_schema to calculate the total size of each table including both data and indexes.

SELECT 
    table_name AS `Table`,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size_MB`
FROM 
    information_schema.tables
WHERE 
    table_schema = 'your_database_name'
ORDER BY 
    `Size_MB` DESC;

Replace 'your_database_name' with the actual name of your database.

🧠 What This Query Does

• data_length: The size of the actual data stored in the table
• index_length: The size of the indexes associated with the table
• ROUND(... / 1024 / 1024, 2): Converts the size from bytes to megabytes (MB)

📦 Get Total Database Size

If you want to get the total size of an entire database, run this query:

SELECT 
    table_schema AS `Database`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Total_Size_MB`
FROM 
    information_schema.tables
WHERE 
    table_schema = 'your_database_name'
GROUP BY 
    table_schema;

🛠 Useful for Maintenance

This type of query is especially useful when you’re trying to:

• Find which tables are consuming the most space
• Optimize or archive large tables
• Monitor database growth over time
• Plan for storage and backup requirements

✅ Conclusion

Keeping an eye on table and database sizes in MySQL can help you proactively manage performance and avoid storage bottlenecks. You can run these queries directly from tools like phpMyAdmin, MySQL Workbench, or from the command line using the mysql client.

Have any questions or tips to share? Drop them in the comments below!