Re: Using partitioning with ARCHIVE engine for data archiving

[Mattias Jonsson]

It seems like you understand the big picture of partitioning.
Since archive does not support any indexes, partitioning can help both with performance (through pruning) as well as maintenance (through alter partition).
The best way to avoid expensive operations (like delete or alter table) would be to partition the table into chunks that also is meaningful to drop or truncate (to simulate delete).
If you need to delete a smaller set than a full partition, you can do as you described in (1) divide a partition into two by ALTER TABLE t REORGANIZE PARTITION p INTO (...) and then drop the partition containing the rows you want to delete. REORGANIZE PARTITION will copy all rows in the affected partitions into new partitions, which will be less work than copying the full table, which happens in most cases with ALTER TABLE for non partitioned commands.

If you alter the whole table, all rows in the table will be copied to a new table internally in the server. To simulate delete on an archive table you could instead do 'create tmp_table () engine archive; insert into tmp_table select * from curr_table WHERE <condition matching all rows to not delete>; rename table curr_table to old_table, tmp_table to curr_table;'.

So changing engine of a table only to be able to delete from an archive table seems like a bad idea, since it will need to copy the full table two times, first for all rows, then for all rows left after the delete operation. As well as the delete operation when the engine is set to other than archive. By doing insert select, it will only copy the remaining data once, and internally rename the tables.

I hope I answered your question, otherwise can you clarify your question?