Im purging a table using pt-archiver , using a where clause of “deleted=1 and <= date_sub(now(),interval 30 day)”. This was very slow , as the select statement was using the Unique key index on the table . I tested the performance of the select query by passing an index hint idx_deleted, which is an index on the deleted column, and this was much faster. So i made use of the -i parameter to use the index on the deleted column. The archiver job was running much faster and not showing up on the slow query log at all . However i noticed that the actual statement running was “DELETE from table where deleted=1 LIMIT 1”. And on checking the data , I see that this statement was actually deleting records without honoring the last_activity date!! Is this a Bug? The version i’m using is pt-archiver 2.2.5
Latest version is 3.0.6. Could you try with an up-to-date version?