Get MySQL Table Disk Usage with Stored Procedure

Table Data Usage

MySQL stores the amount of disk space it uses per table in the information_schema database. Getting the information out is a simple SQL statement, but it is stored in bytes, so if you want to see MB, mB, GB, etc it is a different query. To make this a simpler task, I wrote the stored procedure below. It gives the option to the user which data unit size they will see.

DELIMITER ~
DROP PROCEDURE IF EXISTS ADMIN_getDiskUsage~
CREATE PROCEDURE ADMIN_getDiskUsage(
_size ENUM('k', 'm', 'g', 'kk', 'mm', 'gg')
)
READS SQL DATA
SQL SECURITY DEFINER
BEGIN

DECLARE sizeDivisor DOUBLE DEFAULT 1.0;
DECLARE totalSize_data INT UNSIGNED DEFAULT 1;
DECLARE totalSize_indexes INT UNSIGNED DEFAULT 1;

SELECT SUM(data_length), SUM(index_length) INTO totalSize_data, totalSize_indexes FROM information_schema.tables WHERE table_schema = DATABASE();

CASE IFNULL(_size, '')
    WHEN 'k' THEN SET sizeDivisor = 1000;
    WHEN 'm' THEN SET sizeDivisor = 1000000;
    WHEN 'g' THEN SET sizeDivisor = 1000000000;
    WHEN 'kk' THEN SET sizeDivisor = 1024;
    WHEN 'mm' THEN SET sizeDivisor = 1048576;
    WHEN 'gg' THEN SET sizeDivisor = 1073741824;
    ELSE SET sizeDivisor = 1.0;
END CASE;

SELECT CAST(totalSize_data/sizeDivisor AS DECIMAL(20,2)) AS total_size_data, CAST(totalSize_indexes/sizeDivisor AS DECIMAL(20,2)) AS total_size_indexes;

SELECT table_name, CAST(data_length/sizeDivisor AS DECIMAL(20,2)) AS size_data, CAST(index_length/sizeDivisor AS DECIMAL(20,2)) AS size_indexes, CAST((data_length/totalSize_data) * 100 AS DECIMAL(20,2)) AS size_data_percentOfTotal, CAST((index_length/totalSize_indexes) * 100 AS DECIMAL(20,2)) AS size_indexes_percentOfTotal
    FROM information_schema.tables
        WHERE table_schema = DATABASE()
        ORDER BY data_length DESC
;

COMMIT;END~
DELIMITER ;
COMMIT;

Example calls

CALL ADMIN_getDiskUsage('k'); -- kilobytes (1000 bytes)
CALL ADMIN_getDiskUsage('kk'); -- kilobytes (1024 bytes)
CALL ADMIN_getDiskUsage('gg'); -- gigabytes (1024 megabytes)
{o}
Otreva is a custom software product development company focusing on user experience, responsive web development, & mobile application development.

Interested in seeing what it costs to build an app?

Start Quote See Stats
224 Wyoming Ave. #100
Scranton, PA 18503

You've found the secret footer!

Tweets about @Otreva

Or check us out on:

Shopify Experts
Awwwards