Once a month I need to remove all the old data from the table. Previously I was copying all the needed data to another table, then drop the original table and rename the new table as original. When I ran drop table the space is reclaimed.
But now I am deciding to use Partitioning in the table. So my question is when I will do:
ALTER TABLE mytable TRUNCATE PARTITION partition
Will it reclaims the space? If it does not reclaims the space then am I left with only choice to OPTIMIZE the table or is there anything else I am missing? Since doing OPTIMIZE PARTITION on innodb table will lock the whole table.