Recursive query

[lx2pwnd]

In my db I have some nodes Message which could have a relationship Replay, in a form like below:

(:Message)<-[:REPLAY]-(:Message)<-[:REPLAY]-(:Message)...

I want to return a Message which is the start of conversation and all his REPLAY relationships, so I write this query:

MATCH (n) 
WHERE
     ID(n) = ' + id + ' 
OPTIONAL MATCH x=(n)<-[:REPLAY*]-(r)
RETURN n AS StartOfConversation, r AS Replay ORDER by r.timestamp ASC

But when the path is too long, the query keeps running.
If i limit the path length with -[:REPLAY*100]- I get the results

I also get this warning:

Binding relationships to a list in a variable length pattern is deprecated. (Binding a variable length relationship pattern to a variable ('r') is deprecated and will be unsupported in a future version. The recommended way is to bind the whole path to a variable, then extract the relationships: MATCH p = (...)-[...]-(...) WITH *, relationships(p) AS r)

How can I improve this query to work on every path ?

[cristiscu]

Some quick optimizations I can suggest right away:

1. Add :Message node labels for n and r, if you use indeed only that label as you said (to avoid full database scans)
2. Create an index on :Message(timestamp), because your last sort operation can be time consuming for large depths.
3. No need for the x named path
4. *100 brings only your nodes 100 hops away, you should use *..100 instead (and this will be much slower!). Or why not *..10000? (I just hope you have less than 10K messages in a single thread )

But I must say, you have a rather data modeling problem here, thread messages should not be organized as linked lists. You already have them ordered by date, and they should all point to their single original :Thread parent. Message dependencies should exist only if someone replies to some other message, in a hierarchical layout.

It's easy to change their relationships, if you're allowed to. And your processing time will be improved by 1-2 orders of magnitude.