mysql,

How to get database and table size in MySQL

Jul 21, 2021 · 2 mins read · Post a comment

In this tutorial, we’ll see how we can show a single and multiple database and table size in MySQL. Let’s get straight to the point.

Prerequisites

  • MySQL

Get database size

Step 1. Login to the MySQL server.

mysql -h <server_name> -u <username> -p

Step 2. Once prompt for password, enter the password and type the following command to determine a single database size.

SELECT
    table_schema AS 'db_name',
    ROUND((data_length + index_length) / 1024 / 1024) AS 'size_in_mb'
FROM
    information_schema.tables
WHERE
    table_schema = '<db_name>'
GROUP BY
    table_schema;

Note(s): Make sure to replace <db_name> with the actual database name in the WHERE clause.

Step 3. Size of all databases in DESC order.

SELECT
    table_schema AS 'db_name',
    ROUND((data_length + index_length) / 1024 / 1024) AS 'size_in_mb'
FROM
    information_schema.tables
GROUP BY
    table_schema;
ORDER BY
    (data_length + index_length)
    DESC;

Get table size

Step 1. Login to the MySQL server.

mysql -h <server_name> -u <username> -p

Step 2. Size of a specific table.

SELECT
    table_name AS 'table_name',
    ROUND((data_length + index_length) / 1024 / 1024) AS 'size_in_mb'
FROM
    information_schema.TABLES
WHERE
    table_schema = '<db_name>'
    AND table_name = '<table_name>'

Note(s): Before running the SQL query, make sure to replace <db_name> and <table_name> with the actual database and table names.

Step 3. Size of all tables in DESC order.

SELECT
    table_name AS 'table_name',
    ROUND((data_length + index_length) / 1024 / 1024) AS 'size_in_mb'
FROM
    information_schema.TABLES
WHERE
    table_schema = '<db_name>'
ORDER BY
    (data_length + index_length)
    DESC;

Note(s): Before running the SQL query, make sure to replace <db_name> with the actual database name.

Conclusion

My 2 cents are to deploy proper monitoring and alerting tools that will keep an eye on the database and table size growth. Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.

mysql