OrientDB中查找两顶点间边的最快方法及community_user边更新问题

Hey there! Let's break down how to speed up your community_user edge lookup and update in OrientDB. Your current query works, but we can make it much more efficient with a few targeted tweaks.

First: Lock in Proper Indexes #

The biggest bottleneck here is likely missing indexes on the _id fields of your community and user vertices. Since you're using _id to locate specific vertices, adding unique indexes on these fields will turn those vertex lookups from slow full scans into near-instant O(1) operations. Run these commands once to set them up:

CREATE INDEX community._id ON community (_id) UNIQUE
CREATE INDEX user._id ON user (_id) UNIQUE

Fastest Ways to Find the Edge Between Two Vertices #

1. Use the MATCH Statement (Top Recommendation) #

OrientDB's MATCH syntax is purpose-built for graph traversals, making it the fastest method to locate edges between known vertices. It leverages the database's graph structure and your new indexes directly to skip unnecessary subquery overhead:

MATCH
  {class: community, where: (_id = '5ab283c35b6b9435d4c9a958')} -[e:community_user]-> {class: user, where: (_id = 'x5mxEBwhMfiLSQHaK')}
RETURN e

This query directly traverses from the target community vertex to the target user vertex via the community_user edge, returning the edge e if it exists.

2. Optimize Your Original SELECT Query #

If you prefer sticking with SELECT, you can trim unnecessary overhead by replacing the IN operator (overkill here, since your unique indexes will return exactly one RID) with direct equality checks. This cuts out redundant collection processing:

SELECT FROM community_user
WHERE outV = (SELECT @rid FROM community WHERE _id = '5ab283c35b6b9435d4c9a958')
AND inV = (SELECT @rid FROM user WHERE _id = 'x5mxEBwhMfiLSQHaK')

Updating the Edge Directly #

To update the edge once found, extend either query with an UPDATE clause. For example, using MATCH:

MATCH
  {class: community, where: (_id = '5ab283c35b6b9435d4c9a958')} -[e:community_user]-> {class: user, where: (_id = 'x5mxEBwhMfiLSQHaK')}
UPDATE e SET your_field = 'new_value'
RETURN e

Or with the optimized SELECT:

UPDATE community_user
SET your_field = 'new_value'
WHERE outV = (SELECT @rid FROM community WHERE _id = '5ab283c35b6b9435d4c9a958')
AND inV = (SELECT @rid FROM user WHERE _id = 'x5mxEBwhMfiLSQHaK')

Why These Changes Boost Performance #

• Indexes eliminate full vertex scans: Unique indexes on _id ensure we find the target vertices instantly, no more scanning entire vertex classes.
• MATCH leverages graph optimization: OrientDB's query planner is tuned to handle graph traversals efficiently, avoiding redundant subquery execution.
• Equality checks replace IN: Since we're targeting a single vertex per subquery, using = instead of IN skips unnecessary set operations that slow down execution.

内容的提问来源于stack exchange，提问作者mitchken