I'm running into a problem that I suspect is down to my inability to craft an efficient CYPHER query, and general lack of neo4j experience.

**Background:**

I have a relatively large dataset that seems to be choking when finding mutual 'likes' between a user and their 2nd degree friends.

**Current stats:**

```
neo4j-sh (?)$ dbinfo -g "Primitive count"
{
  "NumberOfNodeIdsInUse": 9343080,
  "NumberOfPropertyIdsInUse": 25416540,
  "NumberOfRelationshipIdsInUse": 47270718,
  "NumberOfRelationshipTypeIdsInUse": 8
}

------

Numbers:
Users: ~ 2 million
Likes: ~ 7 million
Users Likes: ~ 22 million

```

**Indexes:**

```
neo4j-sh (?)$ schema
Indexes
  ON :Employer(origin_id)       ONLINE (for uniqueness constraint)
  ON :Group(origin_id)          ONLINE (for uniqueness constraint)
  ON :Like(category)            ONLINE
  ON :Like(origin_id)           ONLINE (for uniqueness constraint)
  ON :Location(country_code)    ONLINE
  ON :Location(country)         ONLINE
  ON :Location(origin_id)       ONLINE (for uniqueness constraint)
  ON :School(origin_id)         ONLINE (for uniqueness constraint)
  ON :User(registered)          ONLINE
  ON :User(relationship_status) ONLINE
  ON :User(interested_in)       ONLINE
  ON :User(gender)              ONLINE
  ON :User(age)                 ONLINE
  ON :User(origin_id)           ONLINE (for uniqueness constraint)
  ON :User(uid)                 ONLINE (for uniqueness constraint)

Constraints
  ON (user:User) ASSERT user.uid IS UNIQUE
  ON (school:School) ASSERT school.origin_id IS UNIQUE
  ON (user:User) ASSERT user.origin_id IS UNIQUE
  ON (group:Group) ASSERT group.origin_id IS UNIQUE
  ON (employer:Employer) ASSERT employer.origin_id IS UNIQUE
  ON (like:Like) ASSERT like.origin_id IS UNIQUE
  ON (location:Location) ASSERT location.origin_id IS UNIQUE

```

**Slow Queries:**
<http://pastebin.com/MPZ3aXCs>

**Issue:**

The first query executes in about 12 seconds for this user, returning 909 rows. Still very slow.

The second query executes in about 70 seconds for this user. The immediate issue for me is that trying to search through the mutual interests of a matched friend of friend (line 33) causes a dramatic increase in time. I also notice that adding this match seems to create a second EAGER 'branch' in the profile. During this time CPU is absolutely pinned.

If I take a step back and simply match the mutual interests between two users the query is executed in < 50ms.

```
neo4j-sh (?)$ PROFILE MATCH (u:User {origin_id:2043})-[:LIKES]->(l:Like)<-[:LIKES]-(u2:User {origin_id:1212817}) return l;
3 rows

ColumnFilter
  |
  +Filter
    |
    +TraversalMatcher

+------------------+------+--------+-------------+--------------------------------------+
|         Operator | Rows | DbHits | Identifiers |                                Other |
+------------------+------+--------+-------------+--------------------------------------+
|     ColumnFilter |    3 |      0 |             |                       keep columns l |
|           Filter |    3 |      0 |             |      NOT(  UNNAMED31 ==   UNNAMED50) |
| TraversalMatcher |    3 |   1114 |             | u2,   UNNAMED50, u2,   UNNAMED31, u2 |
+------------------+------+--------+-------------+--------------------------------------+

Total database accesses: 1114

```

We are currently looking to extend this query out to match the users 3rd degree friends which right now is seemingly impossible.

I should also note that I'm running this on a standalone AWS c3.xlarge (4 vCPU / 8GB RAM) which does nothing else except host neo4j. The server configs are more or less standard default. Happy to provide them if necessary.

**Ideally,** I'd want to have this information returned in a single query due to the way its processed afterwards.

Any help optimizing these queries would be greatly appreciated. If I've missed any key pieces of information here, please let me know.

**Edit:** Using Neo4J 2.1.6

**Edit 2:**

I've made some changes to the query which seem to reduce the number of dbhits almost in half. The time taken for the query has now reduced down to ~ 16 seconds.

The new query with profile is available here: <http://pastebin.com/UyFi89H7>

Are there more optimisations I can make, aside from using extra criteria to filter down the friends of friends?