Neo4j poor order by query performance

Question

I have a complex cypher, When I don't use "order by" I get a pretty fast response but when I use "order by" it is incredibly slow. I have an b tree index on my order attribute(score of the movie which is PageRank algorithm score). I added the cypher.

MATCH (m:Movie)
WHERE m.release > '0' AND m.imdbVoteAverage > 0 AND
CASE WHEN NOT [] = [] THEN any(title in [] WHERE toLower(m.title) CONTAINS title OR toLower(m.originalTitle) CONTAINS title) ELSE TRUE END
WITH m AS m
MATCH (m)-[:HAS_GENRE]->(genre: Genre)
WHERE CASE WHEN NOT ['komedi'] = [] THEN any(genreName in ['komedi'] WHERE toLower(genre.name) CONTAINS genreName) ELSE TRUE END
MATCH (m)<-[acted:ACTED_IN]-(actor: Person)
WHERE CASE WHEN NOT [] = [] THEN any(actorName in [] WHERE toLower(actor.name) CONTAINS actorName) ELSE TRUE END AND
CASE WHEN NOT [] = [] THEN any(characterName in [] WHERE any(cname in acted.characterNames WHERE toLower(cname) CONTAINS characterName)) ELSE TRUE END
MATCH (m) -[:HAS_KEYWORDS]->(keyword: Keyword)
WHERE CASE WHEN NOT [] = [] THEN any(keywordName in [] WHERE toLower(keyword.name) CONTAINS keywordName) ELSE TRUE END
MATCH (m)<-[:PRODUCED]-(producer: Person)
WHERE CASE WHEN NOT [] = [] THEN any(producerName in [] WHERE toLower(producer.name) CONTAINS producerName) ELSE TRUE END
MATCH (m)<-[:DIRECTED]-(director: Person)
WHERE CASE WHEN NOT [] = [] THEN any(directorName in [] WHERE toLower(director.name) CONTAINS directorName) ELSE TRUE END
MATCH (m)<-[:WRITTEN]-(writer: Person)
WHERE CASE WHEN NOT [] = [] THEN any(writerName in [] WHERE toLower(writer.name) CONTAINS writerName) ELSE TRUE END
MATCH (m)<-[:PRODUCED_COMPANY]-(productionCompany: ProductionCompany)
WHERE CASE WHEN NOT [] = [] THEN any(producedCompanyName in [] WHERE toLower(productionCompany.name) CONTAINS producedCompanyName) ELSE TRUE END
RETURN DISTINCT m ORDER BY m.score DESC LIMIT 10

Also If I add more field such as genre, title, directorName the query works much faster.

score 4 · Accepted Answer · answered May 10 '21 at 13:01

You need to indicate to the planner that your m.score field is numeric, so pulls that from the index. I.e. where m.score > 0

You should see it in your query plans.

Your query looks also really convoluted, and generated. But actually not taking into account that always "false" expressions can just be left out from the query parts e.g. WHERE NOT [] = []

Neo4j poor order by query performance

1 Answers1