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?
There are many solutions. See http://mysql.rjweb.org/doc.php/deletebig .
Which takes 25 seconds? A batch of 2000 rows? Or several of those? Are they in a single, big, transaction? Lots of little transactions would be slower, but less invasive. And there would be no ACID problem since re-deleting the same 'date' should be idempotent.
If the table is "locked" at some level for 25 seconds, then I understand your concern. If it is a bunch of sub-second deletes, then does it really matter that it takes a long time?
Furthermore, instead of deleting once a day, you could delete once an hour. This might decrease the tasks to 2 seconds instead of 25.
25 indexes is terribly large. Please provide SHOW CREATE TABLE. It is all too common that there are "redundant" indexes that could (should) be dropped. The main example is INDEX(a,b) takes care of INDEX(a) (but not b), so if you have both, drop the latter.
It may be impractical to make the change now, but PARTITION BY RANGE(TO_DAYS(...)) lets you DROP PARTITION, which is virtually instantaneous. (Adding partitioning to a 700M row table would take a long time.)
SHOW anyway. And RAM size and innodb_buffer_pool_size.
PRIMARY KEY. See mysql.rjweb.org/doc.php/deletebig .