How do I make my queries run in milliseconds

[idrismunir]

Hi

I currently need help on how to optimize this query match (c:client{client_id:$var3}) optional match (d:client) where (c.address =d.address or c.employer =d.employer) and c<>d and d.status ='WRITE_OFF' return count(distinct d) run in much faster in milliseconds. The number client node in my graph is roughly 800k.

[andrew_bowman]

You need indexes to support these lookups.

You'll need the following indexes:

:client(client_id)
:client(address)
:client(employer)

The only other issue now is that that the planner seems to want to do a label scan instead of an index seek to find d. We can provide an index hint for lookup, which will nudge the planner, and it will also utilize the other index without us having to explicitly specify both.

After you've created your indexes, do an EXPLAIN on this query to verify that only index seeks are used (and not label scans), then give the actual query a try.

match (c:client{client_id:$var3}) 
with c, c.address as address, c.employer as employer
optional match (d:client) 
using index d:client(address)
where (d.address = address or d.employer = employer) and c<>d and d.status ='WRITE_OFF'
return count(distinct d)

[idrismunir · Accepted solution]

Thanks Andrew,

Adding the indexes as suggested helped a lot.