MySQL delete duplicate reverse values

Question

I have MySQL MyISAM table:

Table friends(id, friend_id):

1, 5

5, 1

2, 6

6, 2

3, 7

How to delete reverse records? If for record values «1, 5» exist record with values «5, 1» i need to delete «5, 1».

Thanx for help!

Answer 1

DELETE F1
FROM friends F1, friends F2
WHERE F1.friend_id = F2.id
  AND F2.friend_id = F1.id
  AND F1.id > F1.friend_id

EDIT

A bit nicer syntax would be:

DELETE F1
FROM friends F1
  JOIN friends F2 ON F1.friend_id = F2.id AND F2.friend_id = F1.id
WHERE F1.id > F1.friend_id

But execution time is the same.

As well, I've created this small script for a quick & dirty benchmark.

Results:

No index:

Dalen: 600 => 400 rows. Time: 0.0274
Mark: 600 => 400 rows. Time: 0.4323
Frosty: 600 => 400 rows. Time: 0.4081
Nick: 600 => 400 rows. Time: 0.3201

Separate index on columns id and friend_id:

Dalen: 600 => 400 rows. Time: 0.0201
Mark: 600 => 400 rows. Time: 0.0095
Frosty: 600 => 400 rows. Time: 0.0059
Nick: 600 => 400 rows. Time: 0.3257

Unique index on (id, friend_id):

Dalen: 600 => 400 rows. Time: 0.0168
Mark: 600 => 400 rows. Time: 0.0057
Frosty: 600 => 400 rows. Time: 0.0041
Nick: 600 => 400 rows. Time: 0.3209

Conclusions:

• Dalen: fastest when columns are not indexed
• Frosty: fastest when columns are indexed (Mark's being close, even faster in "indexed cases" without considering the tmp table creation time. However, the additional time required by the tmp table creation increases when the fields are indexed)

Answer 2

Try this:

create temporary table tmp    
  select a.* from friends as a,friends as b 
  where a.id = b.friend_id 
    and a.friend_id = b.id    /* left out of original post */
    and a.id < b.id;

delete from friends using friends inner join tmp 
   on friends.id = tmp.id
  and friends.friend_id=tmp.friend_id;

The join is more efficient than other approaches if the friends table is very large.

EDIT: I fixed the "create...select" statement above. Tested it, works ok. Sorry about that!