Not able to use index to get distinct values

[animesh_sah_ece]

Hello Everyone

Match (n: Player) return (distinct(n.Country)) as country.
I have created an index on the Country attribute for the Player label. When i am using the above the cypher statement it scans all the Player.

I wanted to get the distinct Countries , Is there any good method to do it

[elaine_rosenber]

Hello Animesh & Welcome to the Community!

In order for the index to be used, you must specify a value for the query that uses the index:

MATCH (n:Player)
WHERE n.Country = "xxx"
RETURN n.Country

Elaine

[andrew_bowman]

You can narrow this a bit by saying that n.Country has to exist. Null entries are not indexed, so when you use your query, it must do a label scan since it can return null for all of the nodes that don't have that property.

This should work, since when we require that the Country property must exist, the planner knows it can use the index:

MATCH (n:Player)
WHERE exists(n.Country)
RETURN n.Country

That said this will only give you DISTINCT values if you have a unique constraint, otherwise you'll get duplicates for players that share the same country.

You might want to consider remodeling such that :Player nodes have a :LIVES_IN_COUNTRY relationship to a :Country node. In any case getting distinct values from :Country nodes is easy, since the values should already be distinct.