How to free unused mysql space - OPTIMIZE tables

If you delete a lot of rows, especially if they are big (have text column with a lot of text), then you

1. Have a lot of unused space, you can free
2. Queries against these tables will be a bit slower

Display if you have a lot of free unused space

SELECT table_name, round(data_length/1024/1024) AS data_length_mb, round(data_free/1024/1024) AS data_free_mb
FROM information_schema.tables
WHERE table_schema='my_database_name'
ORDER BY data_free_mb DESC;

The column data_free_mb will show how much space is you can get back for free ;)

Optimize table

Choose your target table you want to optimize and run

--single
OPTIMIZE TABLE my_table_name;
--multiple
OPTIMIZE TABLE my_table_name1, my_table_name2, my_table_name3;

Additional commands

Show table status will show some general info about table

SHOW table status LIKE "my_table_name" \G