CODEWITHSUNDEEP
neo4jcypher
Andrea Mucci
Andrea Mucci

Reputation: 837

Neo4J query with Huge database very slow

i have a database with 29M nodes and 43M relationships

i need to do a query to find a name of an Artist

MATCH (a:Artist) WHERE toLower(a.name) CONTAINS toLower("John") AND a.group=false
WITH a
OPTIONAL MATCH (a)-[b:IS_SUBSCRIBED]->(c:Company)
WITH a, c
OPTIONAL MATCH (a)<-[f:HAS_MEMBER]-(g:Artist)
WITH a, c, collect([g.name, g.aid]) as groups
RETURN a.name AS name, a.aid as id, c.number as aie_number, c.email as email, groups
ORDER BY a.name
LIMIT 200

So i have some artists that maybe are subscribed with a company and maybe are members of some groups. I need to catch that information and when i do the query this take long time, more that 6 seconds that for my point of view is a bit slow, i have attached the profile

enter image description here

Are there some tricks to improve the performance?

I have INDEX for the Artist name and an index of :Artist(name, group)

thanks in advance for your help

Upvotes: 1

Views: 205

Answers (1)

Dave Bennett
Dave Bennett

Reputation: 11216

The problem is that although you have an index because you are doing toLower on the name property before the comparison then the index is not being used. The first step of the profile shows NodeByLabelScan. It is doing a full table scan on the name property.

Create a new property on your :Artist nodes called name_lower, set it to be toLower(name), index that property and use it in your searches. This will vastly improve the performance of your query.

Upvotes: 1

Related Questions