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"