Neo4j: how do I delete all duplicate relationships in the database through cypher?

Question

I have a huge database with a ton of nodes (10mil+). There is only one type of relationship in the whole database. However, there are a ton of nodes that have duplicated relationships between them. What i have currently is this cypher script that finds all the pairs with duplicates, and then a python script that runs through and cleans up each one (leaving just one unique relationship between those nodes).

match (a)-[r]->(b) with a,b, count(*) as c where c>1 return a.pageid, b.pageid, c LIMIT 100000;

this works fairly well for a small database, but when i run it on a big one it eventually blows up with an exception about running out of memory on the heap (bumping up the box more and more doesn't help).

So, the question is 2-fold: 1) Is there any sort of indexing i can put on relationships (right now there is none) that would help speed this up? 2) Is there a cypher query that can (in a fast manner... or at least reliably) delete all the duplicate relationships in the database leaving just one unique one for each node pair (that already has relationship between them)?

P.S. I'm running neo4j 2.0.1 on an ubuntu (12something) AWS box.

P.P.S. I realize there is this answer: stackoverflow, however what he's asking is something more specific (against 2 already known nodes), and the answer that has full database covered doesn't run anymore (syntax change?)

Thanks in advance!

Answer 1

What error do you get with the db global query in the linked SO question? Try substituting | for : in the FOREACH, that's the only breaking syntax difference that I can see. The 2.x way to say the same thing, except adapted to your having only one relationship type in the db, might be

MATCH (a)-[r]->(b)
WITH a, b, TAIL (COLLECT (r)) as rr
FOREACH (r IN rr | DELETE r)

I think the WITH pipe will carry the empty tails when there is no duplicate, and I don't know how expensive it is to loop through an empty collection–my sense is that the place to introduce the limit is with a filter after the WITH, something like

MATCH (a)-[r]->(b)
WITH a, b, TAIL (COLLECT (r)) as rr
WHERE length(rr) > 0 LIMIT 100000
FOREACH (r IN rr | DELETE r)

Since this query doesn't touch properties at all (as opposed to yours, which returns properties for (a) and (b)) I don't think it should be very memory heavy for a medium graph like yours, but you will have to experiment with the limit.

If memory is still a problem, then if there is any way for you to limit the nodes to work with (without touching properties), that's also a good idea. If your nodes are distinguishable by label, try running the query for one label at the time

MATCH (a:A)-[r]->(b) //etc..
MATCH (a:B)-[r]->(b) //etc..