Calculate and Optimize MySQL Database Size

Computing MySQL database size can provide you useful informations : disk usage, index size, number of rows, lack of index, collations and many more.
Calculating these values use the information database : INFORMATION_SCHEMA, the place that stores information about all the other databases that the MySQL server maintains.

Simple Query to show MySQL Database Usage

This query will show the index and data size in Mb, number of rows per table with database type, partitions and collation.

SELECT TABLE_NAME AS "Table", 
       TABLE_ROWS AS "Rows", 
       CONCAT((FORMAT((DATA_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Data Size", 
       CONCAT((FORMAT((INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Index Size",
       CONCAT((FORMAT((DATA_LENGTH+ INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Total Size",
       TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "${Your Database Name}"

This MySQL query execution will output something like this :

 Table                |    Rows | Data Size|Index Size|Total Size| Type
MySQL_TABLE_A         |      342|   0.19 Mb|   0.00 Mb|   0.19 Mb| InnoDB, utf8_general_ci
MySQL_TABLE_B         |     8747|   6.52 Mb|   0.70 Mb|   7.22 Mb| InnoDB, utf8_general_ci
MySQL_TABLE_C         |        0|   0.02 Mb|   0.00 Mb|   0.02 Mb| InnoDB, latin1_swedish_ci
MySQL_TABLE_D         |     4265|   1.52 Mb|   0.42 Mb|   1.94 Mb| InnoDB, utf8_general_ci
MySQL_TABLE_E         |     1934|   0.44 Mb|   0.16 Mb|   0.59 Mb| InnoDB, utf8_general_ci

This is also a good example why I also added database engine and MySQL collation in this query, one table is not in utf8_general_ci but in latin1_swedish_ci.

Another way to show MySQL Database Usage

This query use a MySQL function to compute size in more readable number by changing size numbers units.

DELIMITER $
DROP FUNCTION IF EXISTS byteResize$
CREATE FUNCTION byteResize(bytes FLOAT(9)) 
RETURNS VARCHAR(50)
 
BEGIN
        # Unit list
        DECLARE unit INTEGER UNSIGNED DEFAULT 1;
 
        # Resizing
        WHILE bytes > 1024 DO
            SET bytes = bytes / 1024;
            SET unit = unit + 1;
        END WHILE;
 
RETURN CONCAT(ROUND(bytes, 2), ' ', ELT(unit, '', 'K', 'M', 'G', 'T'), 'b');
 
END$
DELIMITER ;
 
SELECT TABLE_NAME AS "Table", 
       TABLE_ROWS AS "Rows", 
       byteResize(DATA_LENGTH) AS "Data Size", 
       byteResize(INDEX_LENGTH) AS "Index Size",
       byteResize(DATA_LENGTH+ INDEX_LENGTH) AS "Total Size",
       TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type"
FROM information_schema.TABLES
WHERE information_schema.TABLES.table_schema = "${Your Database Name}"

This MySQL query will output :

 Table                |    Rows | Data Size|Index Size|Total Size| Type
MySQL_TABLE_A         |    40119|   3.52 Mb|   4.03 Mb|   7.55 Mb| InnoDB, utf8_general_ci
MySQL_TABLE_B         |  1042959| 757.45 Mb|  37.28 Mb| 794.73 Mb| InnoDB, utf8_general_ci, partitioned
MySQL_TABLE_C         | 36788508|   2.43 Gb|   3.10 Gb|   5.53 Gb| InnoDB, utf8_general_ci, partitioned
MySQL_TABLE_D         |     1876| 112.00 Kb|    0.00 b| 112.00 Kb| InnoDB, utf8_general_ci
MySQL_TABLE_E         |        0|  16.00 Kb|  16.00 Kb|  32.00 Kb| InnoDB, utf8_general_ci
MySQL_TABLE_F         |     1925| 480.00 Kb| 144.00 Kb| 624.00 Kb| InnoDB, utf8_general_ci
MySQL_TABLE_G         |  1210839|  70.78 Mb| 124.14 Mb| 194.92 Mb| InnoDB, utf8_general_ci, partitioned
MySQL_TABLE_H         |     1762|  96.00 Kb|  64.00 Kb| 160.00 Kb| InnoDB, utf8_general_ci
MySQL_TABLE_I         |    44384|  10.52 Mb|   7.55 Mb|  18.06 Mb| InnoDB, utf8_general_ci
MySQL_TABLE_J         |     1536| 176.00 Kb|    0.00 b| 176.00 Kb| InnoDB, utf8_general_ci
MySQL_TABLE_K         |    10849|   1.52 Mb|    0.00 b|   1.52 Mb| InnoDB, utf8_general_ci

This will make the MySQL table size values more readable as the first query returned 5,711.73 Mb as Table B total size.

Hope this will help you.