Deleting multiple records from a very big table (query takes forever)

Question

I am using the following query to delete multiple records except one from my table. It works well with small tables, but it got stuck when I tried it with a table that has >130000 records. The thing is, I don't even get an error. phpMyAdmin just gets stuck and the query ("loading... yellow line) basically takes forever.

My table structure

person_id (AI & PK)
person_name ( I want to delete multiple person_name records except one)

query

DELETE t2
FROM `person` t1
INNER JOIN `person` t2
    ON t1.person_name = t2.person_name
    AND t1.person_id < t2.person_id;

UPDATE : I don't have an index on person table. But my three other tables (person_job & person_image, book_who_wrote_it) contains foreign keys from person table (person_id)

Answer 1

First, do you have an index on person(person_name, person_id)? That would be the place to start.

Deleting lots of rows incurs overhead. Often, it is faster to put the results in another table and reinsert them:

create temporary table tmp_person as
    select p.*
    from person p join
         (select person_name, max(person_id) as max_person_id
          from person
         ) pp
         on p.person_id = pp.max_person_id;

truncate table person;

insert into person
    select * from tmp_person;

Be sure you validate tmp_person before truncating person! Truncate does not log the deletion of each row, so it is much, much, much faster than delete under most circumstances.

NOTE:

If you really only have two columns in person, then you can simplify the first query to:

create temporary table tmp_person as
    select person_name, max(person_id) as max_person_id
    from person;

Answer 2

try this

       DELETE 
       FROM `person` t1
       where person_id not in
                  (select * from 
                      (select person_id from person group by person_name)x)