From f3e4cf3758596f09378fada6b1876b546bd0299b Mon Sep 17 00:00:00 2001 From: Andrew Morgan Date: Mon, 15 Feb 2021 17:36:43 +0000 Subject: Fix get_users_who_share_room_with_user SQL Three fixes here: * Using UNION only selects DISTINCT rows from each query. However, if we DISTINCT the rows during the query, and then use UNION ALL which doesn't attempt to select distinct rows, we get a 1/3rd speedup in query time! * We should select other_user_id instead of user_id from users_who_share_private_rooms, as user_id will always be the requesting user. * Added p1.user_id != p2.user_id to filter out the same entries between each table in the users_in_public_rooms query. --- synapse/storage/databases/main/roommember.py | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'synapse') diff --git a/synapse/storage/databases/main/roommember.py b/synapse/storage/databases/main/roommember.py index fbedc18191..55a56c6c74 100644 --- a/synapse/storage/databases/main/roommember.py +++ b/synapse/storage/databases/main/roommember.py @@ -496,9 +496,10 @@ class RoomMemberWorkerStore(EventsWorkerStore): FROM users_in_public_rooms as p1 INNER JOIN users_in_public_rooms as p2 ON p1.room_id = p2.room_id + AND p1.user_id != p2.user_id AND p1.user_id = ? - UNION - SELECT DISTINCT user_id + UNION ALL + SELECT DISTINCT other_user_id FROM users_who_share_private_rooms WHERE user_id = ? @@ -512,7 +513,7 @@ class RoomMemberWorkerStore(EventsWorkerStore): "get_users_who_share_room_with_user", _get_users_who_share_room_with_user ) - return {row["user_id"] for row in rows} + return {row.get("user_id") or row.get("other_user_id") for row in rows} async def get_joined_users_from_context( self, event: EventBase, context: EventContext -- cgit 1.4.1