[Neo4j] Cypher Query Optimizing
Hey,
I'm currently converting some SQL Queries to Cypher and I'm very happy
to see what is possible with the language.
I was wondering if the following query can be optimized, it selects the
edges between neighbours of a given node (137) who also know each other:
SQL:
select w1.w1_id,w1.w2_id,w1.freq,w1.sig from co_s w1
where
w1.w1_id in (select w2.w2_id from co_s w2 where w2.w1_id=137)
and
w1.w2_id in (select w3.w2_id from co_s w3 where w3.w1_id=137);
Cypher:
START n=node:words(w_id = "137") MATCH n-[:CO_S]->m, n-[:CO_S]-> t,
m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq
The results are the same, but the Cypher Query is about 10 times slower
than the SQL pendant. I currently do not use any additional indices.
Just a map ("words") between my word ID and the neo4j node id.
Thx for your help! Great work! Rock on!
Martin (@kc1s)
___
Neo4j mailing list
User@lists.neo4j.org
https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
It is fairly well known that cypher queries are not (yet) optimised. If speed is a concern for you, try using traversal API, i can confirm it is much faster than cypher. cheers ___ Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
> Cypher:
>
> START n=node:words(w_id = "137") MATCH n-[:CO_S]->m, n-[:CO_S]-> t,
> m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq
>
>
> The results are the same, but the Cypher Query is about 10 times slower
> than the SQL pendant. I currently do not use any additional indices.
> Just a map ("words") between my word ID and the neo4j node id.
Doing index lookups is much slower than node lookups.
In the application I'm developing, we've removed most index lookups
and replaced them with node lookups. We keep a keyword to nodeId
mapping in a key/value store and lookup the nodeId before running any
Cypher queries.
In your case:
START n=node(1) MATCH n-[:CO_S]->m, n-[:CO_S]-> t,
m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq
(where 1 is the corresponding nodeId to w_id = "137")
If I understood your email correctly, you already have that map available
to you ("137" -> 1). I'd use that to see if it's any quicker.
Your mileage may vary, of course. In our application the speed improvements
were roughly 10x. See my post on the mailing list with subject:
Comparing Lucene index lookup performance to lookup by node id
-TPP
___
Neo4j mailing list
User@lists.neo4j.org
https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
@Tero @Krzysztof thx for your fast replies. @Krzysztof for me it was not "fairly well known". I will also check out the traverser api. @Tero I tried the same query using the internal id instead of my mapping index (lucene) orig: START n=node:words(w_id = '137') MATCH n-[:CO_S]->m, n-[:CO_S]-> t, m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq took: 662ms (average of 100 runs after 10 warmups) new: START n=node(119) MATCH n-[:CO_S]->m, n-[:CO_S]-> t, m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq took: 644ms (average of 100 runs after 10 warmups) So it doesn't seem to be much more faster not using the index for node lookup. I will check out your posts concerning Lucene Index. Greetings, Martin Am 30.11.2011 18:08, schrieb Krzysztof Raczyński: > It is fairly well known that cypher queries are not (yet) optimised. > If speed is a concern for you, try using traversal API, i can confirm > it is much faster than cypher. > > cheers > ___ > Neo4j mailing list > User@lists.neo4j.org > https://lists.neo4j.org/mailman/listinfo/user ___ Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
> START n=node(119) MATCH n-[:CO_S]->m, n-[:CO_S]-> t, m-[r:CO_S]-> t > return m.w_id, t.w_id, r.sig, r.freq > took: 644ms (average of 100 runs after 10 warmups) > Can you try using shortestPath cypher function for m->t ? ___ Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
Martin, would you be so kind as to test the current neo4j-1.6 snapshot with your query? We did some changes in cypher and would like to see how that affects your query. Thanks a lot Michael Am 30.11.2011 um 18:34 schrieb Martin Junghanns: > @Tero @Krzysztof > thx for your fast replies. > > @Krzysztof > for me it was not "fairly well known". I will also check out the > traverser api. > > @Tero > I tried the same query using the internal id instead of my mapping index > (lucene) > > orig: > START n=node:words(w_id = '137') MATCH n-[:CO_S]->m, n-[:CO_S]-> t, > m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq > took: 662ms (average of 100 runs after 10 warmups) > > new: > START n=node(119) MATCH n-[:CO_S]->m, n-[:CO_S]-> t, m-[r:CO_S]-> t > return m.w_id, t.w_id, r.sig, r.freq > took: 644ms (average of 100 runs after 10 warmups) > > So it doesn't seem to be much more faster not using the index for node > lookup. > > I will check out your posts concerning Lucene Index. > > Greetings, Martin > > Am 30.11.2011 18:08, schrieb Krzysztof Raczyński: >> It is fairly well known that cypher queries are not (yet) optimised. >> If speed is a concern for you, try using traversal API, i can confirm >> it is much faster than cypher. >> >> cheers >> ___ >> Neo4j mailing list >> User@lists.neo4j.org >> https://lists.neo4j.org/mailman/listinfo/user > > ___ > Neo4j mailing list > User@lists.neo4j.org > https://lists.neo4j.org/mailman/listinfo/user ___ Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
Re: [Neo4j] Cypher Query Optimizing
Am 01.12.2011 01:48, schrieb Michael Hunger: > Martin, Michael, :) > > would you be so kind as to test the current neo4j-1.6 snapshot with your > query? I used neo4j 1.6 M01 community edition for my tests. > > We did some changes in cypher and would like to see how that affects your > query. > > Thanks a lot > > Michael Greetings, Martin > > Am 30.11.2011 um 18:34 schrieb Martin Junghanns: > >> @Tero @Krzysztof >> thx for your fast replies. >> >> @Krzysztof >> for me it was not "fairly well known". I will also check out the >> traverser api. >> >> @Tero >> I tried the same query using the internal id instead of my mapping index >> (lucene) >> >> orig: >> START n=node:words(w_id = '137') MATCH n-[:CO_S]->m, n-[:CO_S]-> t, >> m-[r:CO_S]-> t return m.w_id, t.w_id, r.sig, r.freq >> took: 662ms (average of 100 runs after 10 warmups) >> >> new: >> START n=node(119) MATCH n-[:CO_S]->m, n-[:CO_S]-> t, m-[r:CO_S]-> t >> return m.w_id, t.w_id, r.sig, r.freq >> took: 644ms (average of 100 runs after 10 warmups) >> >> So it doesn't seem to be much more faster not using the index for node >> lookup. >> >> I will check out your posts concerning Lucene Index. >> >> Greetings, Martin >> >> Am 30.11.2011 18:08, schrieb Krzysztof Raczyński: >>> It is fairly well known that cypher queries are not (yet) optimised. >>> If speed is a concern for you, try using traversal API, i can confirm >>> it is much faster than cypher. >>> >>> cheers >>> ___ >>> Neo4j mailing list >>> User@lists.neo4j.org >>> https://lists.neo4j.org/mailman/listinfo/user >> ___ >> Neo4j mailing list >> User@lists.neo4j.org >> https://lists.neo4j.org/mailman/listinfo/user > ___ > Neo4j mailing list > User@lists.neo4j.org > https://lists.neo4j.org/mailman/listinfo/user ___ Neo4j mailing list User@lists.neo4j.org https://lists.neo4j.org/mailman/listinfo/user
