Languages
[Edit]
EN

MySQL - get size of every table in database (in MB)

0 points
Created by:
Dirask Community
6930

In this article, we would like to show you how to get the size of each table in the database in MySQL.

Quick solution:

SELECT
     TABLE_SCHEMA as `Database`,
     TABLE_NAME AS `Table`,
     ROUND(((data_length + index_length) / 1024 / 1024), 3) `Size (in MB)` 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "your_database_name"
ORDER BY (data_length + index_length);

Note:

In the example above replace only your_database_name.

Practical example

Let's say we have the following database:

MySQL - example data used to get sizes of tables
MySQL - example data used to get sizes of tables

What interests us will be the size of every table in the dirask database (users and locations table) shown in the image above. We want to get them with a query and display in descending order.

Query:

SELECT
    TABLE_SCHEMA as `Database`,
    TABLE_NAME AS `Table`,
    ROUND(((data_length + index_length) / 1024 / 1024), 3) `Size (in MB)`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "dirask"
ORDER BY (data_length + index_length) DESC;

Result:

MySQL - get tables size using query - result
MySQL - get tables size using query - result

Related posts

MySQL - Problems

MySQL - get size of every table in database (in MB)
Native Advertising
ūüöÄ
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

‚̧ԳŹūüíĽ ūüôā

Join