Delete big chunk of data with where clause gives time-out

I’m trying to delete over 2 millions records with this statement

DELETE FROM `table` where event_tstamp<1612212312

And I get 504 error time-out, when I try the same statement with select instead of delete it works, there is an index on event_tstamp column. Any idea what could be the problem?

Answer

delete is a much more expensive operation than select. The deletes have to be logged, for instance, and all take place in the context of a transaction.

One work-around is to create a new table, empty the current one, and reload:

create table temp_table as
    select t.*
    from `table` t
    where event_tstamp >= 1612212312;

truncate table `table`;

insert into `table`
    select *
    from temp_table;

It turns out that inserting into a table is often faster than deleting rows.

Note: This requires special consideration if your table has triggers or auto-incremented columns. You can also do what Akina suggests and batch the deletes. If you don’t want to write a stored procedure, just keep running something like:

DELETE FROM `table` 
    WHERE event_tstamp < 1612212312
    LIMIT 10000;  -- or whatever convenient batch size you want

until no rows are deleted.

If this is the type of operation you need to perform periodically, then you probably want to use table partitioning. Dropping a partition is much, much faster than deleting rows individually.