Mysql - Shink table after delete

Question

I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.

What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:

1. First itaration, 30k records to delete, time 35 seconds
2. Second iteration, 126k records to delete, time 3.4 mins
3. Third iteration, 350k records to delete, time 24 mins

Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.

I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting...)

I'm trying to find a good solution before this post, with no luck.

I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.

How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc

I can give you more details if needed

Answer 1

For a "large" delete (more than, say, half the table), do something like:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE ...  -- what to keep
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

The INSERT..SELECT is slow; the other steps are fast. No need to "shrink" unless the original table was created with innodb_file_per_table=OFF, in which case the disk usage will increase with this process.

More big-delete tips for more cases: http://mysql.rjweb.org/doc.php/deletebig