Query Tuning Optional Match

[francisco_casti]

Hi, I am doing my work title for the university and I have found one problem. I need optimize the following query:

// Q22. International dialog
PROFILE
MATCH
(country1:Country {name: 'Mexico'})<-[:IS_PART_OF]-(city1:City)<-[:IS_LOCATED_IN]-(person1:Person),
(country2:Country {name: 'Indonesia'})<-[:IS_PART_OF]-(city2:City)<-[:IS_LOCATED_IN]-(person2:Person)
WITH person1, person2, city1, 0 AS score
// subscore 1
OPTIONAL MATCH (person1)<-[:HAS_CREATOR]-(c:Comment)-[:REPLY_OF]->(:Message)-[:HAS_CREATOR]->(person2)
WITH DISTINCT person1, person2, city1, score + (CASE c WHEN null THEN 0 ELSE 4 END) AS score
// subscore 2
OPTIONAL MATCH (person1)<-[:HAS_CREATOR]-(m:Message)<-[:REPLY_OF]-(:Comment)-[:HAS_CREATOR]->(person2)
WITH DISTINCT person1, person2, city1, score + (CASE m WHEN null THEN 0 ELSE 1 END) AS score
// subscore 3
OPTIONAL MATCH (person1)-[k:KNOWS]-(person2)
WITH DISTINCT person1, person2, city1, score + (CASE k WHEN null THEN 0 ELSE 15 END) AS score
// subscore 4
OPTIONAL MATCH (person1)-[:LIKES]->(m:Message)-[:HAS_CREATOR]->(person2)
WITH DISTINCT person1, person2, city1, score + (CASE m WHEN null THEN 0 ELSE 10 END) AS score
// subscore 5
OPTIONAL MATCH (person1)<-[:HAS_CREATOR]-(m:Message)<-[:LIKES]-(person2)
WITH DISTINCT person1, person2, city1, score + (CASE m WHEN null THEN 0 ELSE 1 END) AS score
// preorder
ORDER BY
city1.name ASC,
score DESC,
person1.id ASC,
person2.id ASC
WITH
city1,
// using a list might be faster, but the browser query editor does not like it
collect({score: score, person1: person1, person2: person2})[0] AS top
RETURN
top.person1.id,
top.person2.id,
city1.name,
top.score
ORDER BY
top.score DESC,
top.person1.id ASC,
top.person2.id ASC

This query is attach to following data model:

I tried with the index, rewrite query and materialized road, but I couldn't solve plss help me !!!

[anthapu]

Can you please elaborate on how much time it is taking?

From what I can see you are starting from cartesian product of people who are living in 2 cities and doing various scores at each level, without getting filtered at all.

Cypher could be limited here as it needs to keep the whole cartesian product at five levels deep.

Another option is to process each query independently and add a relationship between patients with updated score at each level.

If you are OK with Java development, then you could write a Java store procedure, which can process this data more efficiently.

Also, I see you are trying to calculate score between 2 persons. Have you tried looking at Similarity graph algorithms to calculate that?