How to Get the Size of MySQL Database

1. Use the SELECT Statement to Get the Size of a MySQL Database
2. Using MySQL Workbench

In this tutorial, we will learn how to find the size of a MySQL database.

Metadata is critically important while trying to understand the scope of our data. MySQL assists the user with useful metadata that has information about various tables and databases within the system. This feature can be useful in understanding exactly how much data is allotted or needs to be allotted to a particular database.

There are two main ways to identify the size of a database.

• Using the SELECT statement in MySQL.
• Using MySQL Workbench.

Let us try to understand each of the techniques above in detail.

First, we create a dummy dataset to work with. Here we create a table, student_details.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Use the SELECT Statement to Get the Size of a MySQL Database

We can use the SELECT statement to get the size of a database. We can do this with the following syntax.

SELECT TABLE_SCHEMA AS `database_name`, 
ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Size in MB` 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA="<database name>";

Here, database_name represents the name of the database under consideration.

To find the size of the database named student with the student_details table, we can use the following query.

SQL for getting value in Kb:
SELECT TABLE_SCHEMA AS student,
SUM(DATA_LENGTH + INDEX_LENGTH) AS Size in Kb
FROM information_schema.TABLES;

The above code would give the following output.

Using MySQL Workbench

One of the simpler ways to identify the size of a database is to use the MySQL Workbench. This process can be illustrated as follows.

Thus, with the help of the above two techniques, we can efficiently find the size of any database in MySQL.