Delete records using HeidiSQL based on count

Question

I have accidently created a large number (500K+) of incorrect records in a single table. The table has Face_ID as the unique key, and Picture_ID as a foreign key. I want to delete all records where there is a very large number of records linked to the same value of Picture_ID. The following query works perfectly, and very quickly:

Select Picture_ID, COUNT(*) From faces Group BY Picture_ID Having COUNT(*) > 1000

I have tried the following, but event after 20 minutes of execution, the query is still running:

delete from faces where Picture_ID IN (Select Picture_ID From faces Group BY Picture_ID Having COUNT(*) > 1000)

Even if I adjust the number (1000 in the above example) to a value that finds only 3 values of Picture_ID, each with approximately 1000 records, the query still takes a very long time. (Frankly, it scares me how long it takes - makes me wonder what will be left of my database when it finishes!)

What is the most efficient way to code this, using HeidiSQL and MariaDB on Windows 10.

Answer 1

I found an answer:

DELETE a from faces a JOIN (Select Picture_ID From faces Group BY Picture_ID Having COUNT(*) > 1000) b ON a.Picture_ID = b.Picture_ID

It works, and it is FAST!