Q:

How to optimize Cypher-Query with multiple OPTIONAL MATCH

I have following cypher query with multiple optional matches which can't be run on my machine anymore (Cartesian Product):
match (document:Document)-[*..2]-(relateddocument:Document)
optional match (document)-[:HAS_CATEGORY]->(c:Category)<-[:HAS_CATEGORY]-(relateddocument)
optional match (document)-[:HAS_KEYWORD]->(k:Keyword)<-[:HAS_KEYWORD]-(relateddocument)
optional match (document)-[:HAS_AUTHOR]->(a:Author)<-[:HAS_AUTHOR]-(relateddocument)
with document, relateddocument, collect(c)+collect(k)+collect(a) as similarity
where id(document) = 85182 return relateddocument, similarity order by similarity desc limit 5

Could you please give me a hint how I could optimize this query?

A:

As the other answers indicate, you need to put the WHERE clause as close as possible to the corresponding MATCH, to minimize the number of rows generated by the MATCH.
In addition, you can eliminate the cartesian products caused by all the back-to-back OPTIONAL MATCH clauses by using COLLECT to convert the N rows from each MATCH to 1 row. (The last WITH would be right before the RETURN, and so could be "merged" into the RETURN.)
Also, your ORDER BY similarity DESC clause does not make any sense (and will probably cause an error), since similarity is a collection. You probably meant to use SIZE(similarity) instead of similarity there.
This should be faster:
MATCH (document:Document)-[:HAS_CATEGORY|:HAS_KEYWORD|:HAS_AUTHOR*..2]-(relateddocument:Document)
WHERE ID(document) = 85182
OPTIONAL MATCH (document)-[:HAS_CATEGORY]->(c:Category)<-[:HAS_CATEGORY]-(relateddocument)
WITH document, relateddocument, COLLECT(c) AS cs
OPTIONAL MATCH (document)-[:HAS_KEYWORD]->(k:Keyword)<-[:HAS_KEYWORD]-(relateddocument)
WITH document, relateddocument, cs, COLLECT(k) AS ks
OPTIONAL MATCH (document)-[:HAS_AUTHOR]->(a:Author)<-[:HAS_AUTHOR]-(relateddocument)
RETURN relateddocument, cs+ks+collect(a) as similarity
ORDER BY SIZE(similarity) DESC
LIMIT 5;

Notice that the first MATCH also uses [:HAS_CATEGORY|:HAS_KEYWORD|:HAS_AUTHOR*..2] to filter the relationship types, in case your documents have a lot of relationships with other types. That could further reduce the number of rows generated by the first MATCH, which would reduce the amount of work done by the entire query.

