Mysql - Shink table after delete 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:

First itaration, 30k records to delete, time 35 seconds
Second iteration, 126k records to delete, time 3.4 mins
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, &quot;well built indexes&quot; etc
I can give you more details if needed
For a &quot;large&quot; 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 &quot;shrink&quot; 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