When do we need to optimize MySQL tables?

To Nha Notes | Feb. 17, 2021, 5:46 p.m.

Some space consumed by tables isn't actively in use, but is allocated to the table anyway. If innodb_file_per_table is enabled, we can run OPTIMIZE TABLE to return this unuse storage to back table space.

To check are there any such storage need to be returned to table space or not. We run below SQL to check.

mysql> SELECT
    -> ROUND(SUM(data_length + index_length + data_free)/1024/1024/1024, 1) AS total_gb,
    -> ROUND(SUM(data_length)/1024/1024/1024, 1) AS data_gb,
    -> ROUND(SUM(index_length)/1024/1024/1024, 1) AS index_gb,
    -> ROUND(SUM(data_free)/1024/1024/1024, 1) AS free_gb,
    -> COUNT(*) AS tables,
    -> CURDATE() AS today
    -> FROM
    -> information_schema.tables
    -> WHERE table_schema='<replace_by_your_db>';

And says here is result of above SQL:

+----------+---------+----------+---------+--------+------------+
| total_gb | data_gb | index_gb | free_gb | tables | today      |
+----------+---------+----------+---------+--------+------------+
|   2095.2 |  1065.9 |    953.7 |    75.5 |    226 | 2021-02-17 |
+----------+---------+----------+---------+--------+------------+

The data says free_gb is storage which is not inuse which we can return to table space.

To list up top tables which we need to optimize.

SELECT 
    table_name,
    ROUND(SUM(data_length + index_length + data_free)/1024/1024/1024, 1) AS total_gb,
    ROUND(SUM(data_length)/1024/1024/1024, 1) AS data_gb,
    ROUND(SUM(index_length)/1024/1024/1024, 1) AS index_gb,
    ROUND(SUM(data_free)/1024/1024/1024, 1) AS free_gb,
    COUNT(*) AS tables,
    CURDATE() AS today 
FROM 
    information_schema.tables
WHERE table_schema='<replace_by_your_db>'
    GROUP BY table_name
    ORDER BY 5 DESC LIMIT 10;

See this blog for more information.