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!

Diffident

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)

Seineetmarne

+1 even to you. I'd like to know if there is any performance difference between your query and Mark's one. – Eudosia

I'm curious too but have to go to bed now :) I may try to compare when I have some time. – Seineetmarne

actually it is not the fastest solution, i did a quick workbench with 116 rows, Frosty's solution use 0.017 secs while mine is using 0.005secs, quite a trivial difference, it should be tested on large scale – Tartary

Sorry, i didn`t test your code, cause changed architecture of db and execute script only one time. Thanx anyway, later will test all codes. – Diffident

Argh! Query is now fixed (see below). – Pokeberry

@Frosty: very very nice workbench! +1 for that! anyway my previous comment is related to your first query which is retriving data with a cartesian product which has to be slower than a join! Am I annoying? I think so ;) Bye – Tartary

@Dalen, thanks for your feedback :) As I stated in my edited answer, strangely the "cartesian product" + join constraints in the WHERE (my 1st answer) appears to be as fast as using JOIN. I'll update the benchmark with Mark's fixed answer ASAP (I'm not writing from my own PC right now). – Seineetmarne

ok, this kind of confrontation leads to some new discover! great job! – Tartary

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!

Pokeberry

Answer 3

DELETE FROM friends
WHERE (id,friend_id) IN
(   
   SELECT * FROM
   (
      SELECT t1.id,t1.friend_id
      FROM friends t1 JOIN friends t2
         ON t1.id=t2.friend_id AND t1.friend_id = t2.id
      WHERE t1.id > t1.friend_id
   ) t3
)

Tartary

Answer 4

create table friends (
id int,
friend_id int ) engine = myisam;

insert into friends values (1,5),(5,1),(2,6),(6,2),(3,7);


delete from friends where (id,friend_id) in
(select * from (
select id,friend_id from friends
union all
select friend_id,id from friends ) as t
where id > friend_id
group by id,friend_id
having count(*) >  1)

Eudosia