MySQL/MariaDB – Limiting User to SELECT and SHOW Queries

Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.

Your indexes involve all of 4 fields in the table. i.e. PK implicitly exists in all other secondary indexes. After each insert/update/delete, the indexes will be re-organizes, and from time to time will be optimized, which lead to slow queries some times. One way to improve that is to optimize the indexes, and that is highly dependent on the queries you run.

For example, for your delete query, you need an index on filter only.

Optimizing indexes helps in doing less memory paging, and higher hit ration for cached data.