Skip to main content

Get MySQL tables having free space for optimize

Submitted by system on
The following query lists tables with free space greater than equal to 50MB to optimize mysql> select table_name, table_schema, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;
Example response: +---------------------------------+-----------------+----------------+--------------+
| TABLE_NAME | TABLE_SCHEMA | data_length_mb | data_free_mb |
+---------------------------------+-----------------+----------------+--------------+
| field_data_field_untested_issue | database1 | 63 | 56 |
| key_value_expire | database2 | 5 | 63 |
| sessions | database2 | 62 | 73 |
| cache_entity | database2 | 140 | 76 |
| cache_menu | database2 | 67 | 213 |
| cache_entity | database2 | 184 | 233 |
| cache_render | database2 | 86 | 406 |
| cache_bootstrap | database2 | 3 | 550 |
| views_data_export_object_cache | database1 | 55 | 738 |
| cache_form | database1 | 8003 | 744 |
| cache_form | database1 | 3301 | 940 |
+---------------------------------+-----------------+----------------+--------------+

Technologies