Sling Academy
Home/MySQL/MySQL 8: How to get the size of a database

MySQL 8: How to get the size of a database

Last updated: January 25, 2024

Introduction

Understanding the size of a database is crucial for database management. It allows administrators to monitor growth trends, plan for capacity expansion, and optimize database performance. In MySQL 8, multiple techniques exist to determine the database size, from simple queries to comprehensive reporting. This tutorial will walk you through different methods to find the size of a database in MySQL 8 using code examples.

Using the Information Schema

The Information Schema in MySQL contains metadata about all other databases managed by MySQL. To find the size of a specific database, you can use the information_schema database, which provides access to database metadata including table size data.

SELECT table_schema AS `Database`, 
    SUM(data_length + index_length) / 1024 / 1024 AS `Size in MB`
FROM information_schema.TABLES 
WHERE table_schema = 'your_database_name'
GROUP BY table_schema;

Output:

+------------+-----------+
| Database   | Size in MB |
+------------+-----------+
| your_database_name | 128.00    |
+------------+-----------+

This query sums the data_length and index_length, which represent the size of all rows and the size of index files respectively, then converts bytes into megabytes for readability. Replace 'your_database_name' with the name of your database.

Calculating the Size for Each Table

You can also break down the size by each table within the database:

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

Output:

+------------+-----------+
| Table      | Size in MB |
+------------+-----------+
| table1     | 64.00      |
| table2     | 32.00      |
| table3     | 16.00      |
| ...        | ...        |
+------------+-----------+

This query will list out all the tables within your database along with their sizes in MB, ordered from largest to smallest.

Advanced Query: Including Free Space and Row Count

For more advanced insight, you might want information about free space within the database files and the number of rows in each table. Here is an enhanced query that provides that:

SELECT table_name, 
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Data Size in MB`, 
    ROUND((data_free) / 1024 / 1024, 2) AS `Free Space in MB`, 
    table_rows 
FROM information_schema.TABLES 
WHERE table_schema = 'your_database_name';

Output:

+------------+----------------+----------------+-----------+
| Table      | Data Size in MB| Free Space in MB| Row Count |
+------------+----------------+----------------+-----------+
| table1     | 62.99          | 1.01           | 1000      |
| table2     | 31.50          | 0.50           | 500       |
| table3     | 15.75          | 0.25           | 250       |
| ...        | ...            | ...            | ...       |
+------------+----------------+----------------+-----------+

In this query, data_free represents the free space allocated by the DB engine but not yet used. The field table_rows approximates the number of rows in the table. Do note that table_rows displays exact values for InnoDB tables that have a primary key.

Determining Database Size Using SQL Scripts

You may also want to automate or extend the functionality of these SQL commands through the use of scripts. For instance, this could be used to generate regular reports about your database sizes, or set up alerts if the size exceeds a prescribed threshold.

Here’s an example script that you might save as db_size_report.sql and run from the MySQL command line:

SELECT CONCAT('Database Size Report for ', DATE(NOW()), ':') AS header;
SELECT table_schema AS `Database`,
    SUM(data_length + index_length) / 1024 / 1024 AS `Size in MB`
FROM information_schema.TABLES 
GROUP BY table_schema;

Conclusion

In this article, we’ve explored different methods to determine the database size in MySQL 8. Whether you are planning for capacity, auditing your current usage, or simply curious about your databases, these techniques and queries offer a solid foundation for understanding and managing your database sizes.

Next Article: Using SHOW and DESCRIBE statements in MySQL 8: A Practical Guide

Previous Article: MySQL 8: Add column with default value to an existing table

Series: MySQL Tutorials: From Basic to Advanced

MySQL

You May Also Like

  • MySQL: How to reset the AUTO_INCREMENT value of a table
  • MySQL: How to add a calculated column to SELECT query
  • MySQL: Eliminate orphan rows in one-to-many relationships
  • MySQL: Using R-Tree Indexes for Spatial Data Types
  • How to Create Custom Collations in MySQL
  • Using Hash Indexes in MySQL: A Practical Guide
  • Understanding Full-Text Indexes in MySQL
  • Partial Indexes in MySQL: A Practical Guide
  • MySQL: How to Remove FOREIGN KEY Constraints
  • Using ENUM in MySQL 8: A Practical Guide (with Examples)
  • MySQL: Creating a Fixed-Size Table by Using Triggers
  • One-to-Many Relationship in MySQL 8: A Practical Guide
  • Using Regular Expressions in MySQL 8: The Complete Guide
  • Using Loops in MySQL: A Practical Guide (with Examples)
  • How to Execute an SQL File in VS Code
  • Making use of the JSON_REMOVE() function in MySQL 8
  • MySQL 8: How to count rows in related tables
  • Replication in MySQL 8: A Comprehensive Guide
  • MySQL 8: FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions – Explained with examples