Fast DELETE with double subquery on big table for dupe remover I have a table with 9million rows.
At some point in time i changed unique key from one column to two columns(for partitioning).

After this i created duplicates of my first column which is unwanted, so i try to delete all records that are duplicates 

I asked about it (Find a longest sequence that doesn&#39;t have duplicates inside) and modified the code i received to make it MySql compliable.
Here's the code:

DELETE FROM t USING `mytable` AS t WHERE EXISTS 
(SELECT *
    FROM (SELECT * FROM mytable) AS t2
    WHERE t2.Text = t.Text
    AND t2.Id &lt; t.Id); 


The Table looks like this:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+


Problem is that the query takes over 2 hours now without sign of progress.

How i can optimize this query?

I'm running on 8 core/16GB Windows 10 machine.
I wonder if that works at all, MySQL always had a stupid restriction: You cannot delete from a table and select from the same table in a subquery :-)

So create a temporary table first with the result of

select min(id)
from mytable
group by text; 


And use this in your delete

select from mytable
where id not in 
 ( select id from mytemptable);