Stay a moment. The words are near.

Methods to Check Table Size in MySQL - 月光下的键盘声

Methods to Check Table Size in MySQL

(已编辑)

默认分类 / ,

6

This article was last modified on . Some content may be outdated. Feel free to ask the author if you have questions.

This article was originally published on 2023-02-03 and migrated here from the old blog. Compiled by: Hermes

Checking Database and Table Sizes in MySQL

1. View the capacity size of all databases

CodeBlock Loading...

2. View the capacity size of each table in all databases

CodeBlock Loading...

3. Clean up table fragmentation

OPTIMIZE TABLE your_database_name.your_table_name;

SELECT
    table_schema AS 'Database',
    SUM(table_rows) AS 'Number of Records',
    SUM(TRUNCATE(data_length/1024/1024, 2)) AS 'Data Capacity (MB)',
    SUM(TRUNCATE(index_length/1024/1024, 2)) AS 'Index Capacity (MB)',
    SUM(TRUNCATE((data_length + index_length)/1024/1024, 2)) AS 'Total Capacity (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC;

SELECT
    table_schema AS 'Database',
    table_name AS 'Table Name',
    table_rows AS 'Number of Records',
    TRUNCATE(data_length/1024/1024, 2) AS 'Data Capacity (MB)',
    TRUNCATE((data_length + index_length)/1024/1024, 2) AS 'Total Capacity (MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY SUM(data_length) DESC LIMIT 0, 50;