How to MySQL tune slow delete on huge table?

I’m using MySQL InnoDB, one of the most important tables has over 700 million records with totally 23 actively used indexes.

I am trying to delete the records in a batch of 2000 based on the record date (and order by on primary key column). Each date has around 6 million records, I delete it date by date with limit 2000. Each batch takes around 25 seconds to complete. Since it is a Production database, I want this delete operation to complete faster. Is there a better way to do this?

from StackOverflow question

Deleting large amounts of data from a table with many indexes can be a performance-intensive task, as each index must be updated to reflect the changes made to the table.

Here are a few ways to improve the performance of your delete operation:

Disable foreign key constraints: If your table has foreign key constraints, disabling them during the delete operation can speed up the process as it eliminates the need to check the integrity of the foreign key.

SET FOREIGN_KEY_CHECKS = 0;

Disable triggers: If your table has triggers, disabling them before the delete operation can also speed up the process as it eliminates the need to fire the triggers.

SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,NO_TRIGGERS';

Use partitioning: If you can partition your table based on date, it allows you to delete data in a specific partition rather than the entire table. Although this migh take a long time to do, you can start preparing for it and transition.

ALTER TABLE mytable DROP PARTITION p1;