Order list without scanning every node

Question

When using LIMIT with ORDER BY, every node with the selected label still gets scanned (even with index).

For example, let's say I have the following:

MERGE (:Test {name:'b'})
MERGE (:Test {name:'c'})
MERGE (:Test {name:'a'}) 
MERGE (:Test {name:'d'})

Running the following gets us :Test {name: 'a'}, however using PROFILE we can see the entire list get scanned, which obviously will not scale well.

MATCH (n:Node)
RETURN n
ORDER BY n.name
LIMIT 1

I have a few sorting options available for this label. the order of nodes within these sorts should not change often, however, I can't cache these lists because each list is personalized for a user, i.e. a user may have hidden :Test {name:'b'}

Is there a golden rule for something like this? Would creating pointers from node to node for each sort option be a good option here? Something like

(n {name:'a'})-[:ABC_NEXT]->(n {name:'b'})-[:ABC_NEXT]->(n {name:'c'})-...

Would I be able to have multiple sort pointers? Would that be overkill?

Ref:

lifwanian lifwanian · Accepted Answer · 2016-06-26T21:19:30

Here's what I ended up doing for anyone interested:

// connect nodes

MATCH (n:Test)
WITH n
ORDER BY n.name
WITH COLLECT(n) AS nodes
FOREACH(i in RANGE(0, length(nodes)-2) | 
  FOREACH(node1 in [nodes[i]] | 
    FOREACH(node2 in [nodes[i+1]] | 
      CREATE UNIQUE (node1)-[:IN_ORDER_NAME]->(node2))))

// create list, point first item to list

CREATE (l:List { name: 'name' })
WITH l
MATCH (n:Test) WHERE NOT (m)<-[:IN_ORDER_NAME]-()
MERGE (l)-[:IN_ORDER_NAME]->(n)

// getting 10 nodes sorted alphabetically

MATCH (:List { name: 'name' })-[:IN_ORDER_NAME*]->(n)
RETURN n
LIMIT 10

Order list without scanning every node

1 Answers