How to optimize cypher when I want to find a path from a node to another?

Question

I want to optimize cypher because its too slow to get the result.

My code is :

MATCH (e0{name:"dacomitinib"})-[r01]-(e1)-[r12]-(e2)-[r23]-(e3{name:"rucaparib camsylate"})
WHERE (e1:GeneEntity or e1:CompoundEntity or e1:DrugsEntity or e1:DiseaseEntity or e1:ProteinEntity) 
and (e2:GeneEntity or e2:CompoundEntity or e2:DrugsEntity or e2:DiseaseEntity or e2:ProteinEntity) 
RETURN e0.name,r01.confidence,e1.name,r12.confidence,e2.name,r23.confidence,e3.name

What should I do?

update one:

The PROFILE of my code is

Cypher version: CYPHER 3.5, planner: COST, runtime: INTERPRETED. 86876729 total db hits in 53454 ms.

Can you run your query with adding `PROFILE` in the start before `MATCH` and share the expanded query plan? — Rajendra Kadam, Apr 28 '19 at 07:10
Hi,@Raj.the count of all nodes is 230,000,and the count of all relationships is 13,000,000. — DachuanZhao, Apr 28 '19 at 07:26

Rajendra Kadam · Accepted Answer · 2019-04-28T09:10:31.487

1

There some ways you can improve the performance of your query.

1. Create Index on name property:

Do the same for the other labels as well.

CREATE INDEX ON :GeneEntity(name)

2. Use labels when matching (Here for e0 and e3): Consider using labels for reducing the nodes to scan. If you don't use labels Neo4j will compare all the nodes.

Solution:

Your query is internally resulting in an AllNodesScan. AllNodesScan this is a bad Idea!.

A better solution could be:

MATCH (e0{name:"dacomitinib"}), (e3{name:"rucaparib camsylate"})
WITH e0, e3 
MATCH (e0)-[r01]-(e1)-[r12]-(e2)-[r23]-(e3) 
WHERE  
    head(labels(e1)) IN ['GeneEntity','CompoundEntity','DrugsEntity','DiseaseEntity','ProteinEntity'] 
    AND 
    head(labels(e2)) IN ['GeneEntity','CompoundEntity','DrugsEntity','DiseaseEntity','ProteinEntity'] 
RETURN e0.name, r01.confidence, e1.name, r12.confidence, e2.name, r23.confidence, e3.name

edited Apr 28 '19 at 09:10

answered Apr 28 '19 at 07:27

Rajendra Kadam

4,004
1
10
24

Hi,@Raj.I try them but nothing helps.What else should I do? ○|￣|_ – DachuanZhao Apr 28 '19 at 07:43
Did you create an index for other labels as well? – Rajendra Kadam Apr 28 '19 at 07:47
Also, there are no relationship directions in your query, can you add these and update the above query? – Rajendra Kadam Apr 28 '19 at 07:49
Yes,there are 10 indexes on ```name``` in the database since the number of nodes' category is 10. – DachuanZhao Apr 28 '19 at 07:50
I can't do that because I want to find all the relationships between the two nodes. – DachuanZhao Apr 28 '19 at 07:52
I try the query, but the result is the same as before.I think my question is like this https://stackoverflow.com/questions/20003769/neo4j-match-multiple-labels-2-or-more – DachuanZhao Apr 28 '19 at 08:14
Do you have this neo4j hosted somewhere? or is it on your laptop? – Rajendra Kadam Apr 28 '19 at 08:21
It's in a cloud server. ``` $ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 24 On-line CPU(s) list: 0-23 Thread(s) per core: 2 Core(s) per socket: 12 ``` – DachuanZhao Apr 28 '19 at 08:34
how much time does it take without where condition? – Rajendra Kadam Apr 28 '19 at 08:36
Amazing.It only cost 400ms without ```where``` while it cost 50s with ```where``` – DachuanZhao Apr 28 '19 at 08:36

How to optimize cypher when I want to find a path from a node to another?

1 Answers1

Solution: