Make cypher query more effective

48 views Asked by At

I have a goal of having this query return data in less than 10 ms, currently it's less than 50ms.

The relationships are

(l:list)<-[:IN_LIST]-(p:product)<-[:PRODUCT]-(a:a)

I have over 20m of :a in the db and this significantly slows down the query. But I need to order the :list by count of :a. Is there a fast way of getting the size/count?

GRAPH.profile g "MATCH (l:list{kind: 'Trending'})
          MATCH (l)<-[:IN_LIST]-(p:product)
          WITH p, size((p)<-[:PRODUCT]-()) as count, l ORDER BY count DESC
          WITH count(l) as total, collect(l{.id})[0..20] as lists
          RETURN *"
1) "Results | Records produced: 1, Execution time: 0.001034 ms"
2) "    Project | Records produced: 1, Execution time: 0.007407 ms"
3) "        Aggregate | Records produced: 1, Execution time: 0.331449 ms"
4) "            Sort | Records produced: 636, Execution time: 0.155567 ms"
5) "                Project | Records produced: 636, Execution time: 0.383724 ms"
6) "                    Apply | Records produced: 636, Execution time: 4.117716 ms"
7) "                        Conditional Traverse | (p:product)->(p:product) | Records produced: 636, Execution time: 0.409086 ms"
8) "                            Filter | Records produced: 39, Execution time: 0.050714 ms"
9) "                                Node By Label Scan | (p:list) | Records produced: 77, Execution time: 0.020350 ms"
10) "                        Aggregate | Records produced: 636, Execution time: 19.862072 ms"
11) "                            Conditional Traverse | (anon_0)-[anon_1:PRODUCT]->(anon_0) | Records produced: 46947, Execution time: 65.262939 ms"
12) "                                Argument | Records produced: 636, Execution time: 0.050361 ms"

UPDATE, this way it works ~30ms

GRAPH.profile g "MATCH (l:list{kind: 'Trending'})
          MATCH (l)<-[:IN_LIST]-(p:product)<-[:PRODUCT]-(a)
          WITH l ORDER BY count(a) ASC
          RETURN count(l) as total, collect(l{.id})[0..20] as lists"

1) "Results | Records produced: 1, Execution time: 0.001405 ms"
2) "    Aggregate | Records produced: 1, Execution time: 0.055607 ms"
3) "        Sort | Records produced: 20, Execution time: 0.007036 ms"
4) "            Aggregate | Records produced: 20, Execution time: 9.456037 ms"
5) "                Conditional Traverse | (a)->(a) | Records produced: 46947, Execution time: 19.134115 ms"
6) "                    Filter | Records produced: 39, Execution time: 0.057806 ms"
7) "                        Node By Label Scan | (l:list) | Records produced: 77, Execution time: 0.019137 ms"
0

There are 0 answers