Re: Delete statement taking more time for deleting 1 million records

[Rick James]

> which is the best way to delete all 1 million records in less time.

How fast do disks spin? That is your limiting factor.

OTOH, there is a possible solution. PARTITIONing. DROP PARTITION takes less than a second, even for a millions of records. See
http://mysql.rjweb.org/doc.php/deletebig

If you think partitioning won't work for you, or need further guidance, then please provide
SHOW CREATE TABLE
and explain which million rows you need to delete.

On the other hand...

> 2 millon records on the mysql database table and wanted to delete 1 million records

That smells like a one-time cleanup, not a monthly purge? In that case, it _may_ be faster to
CREATE TABLE new LIKE real;
INSERT INTO new SELECT * FROM real WHERE <rows to keep>;
RENAME TABLE real TO old, new TO real;
DROP TABLE old;