diff --git a/synapse/storage/databases/main/roommember.py b/synapse/storage/databases/main/roommember.py
index 8df760e8a6..db03729cfe 100644
--- a/synapse/storage/databases/main/roommember.py
+++ b/synapse/storage/databases/main/roommember.py
@@ -312,18 +312,10 @@ class RoomMemberWorkerStore(EventsWorkerStore, CacheInvalidationWorkerStore):
# We do this all in one transaction to keep the cache small.
# FIXME: get rid of this when we have room_stats
- # Note, rejected events will have a null membership field, so
- # we we manually filter them out.
- sql = """
- SELECT count(*), membership FROM current_state_events
- WHERE type = 'm.room.member' AND room_id = ?
- AND membership IS NOT NULL
- GROUP BY membership
- """
+ counts = self._get_member_counts_txn(txn, room_id)
- txn.execute(sql, (room_id,))
res: Dict[str, MemberSummary] = {}
- for count, membership in txn:
+ for membership, count in counts.items():
res.setdefault(membership, MemberSummary([], count))
# Order by membership (joins -> invites -> leave (former insiders) ->
@@ -370,6 +362,31 @@ class RoomMemberWorkerStore(EventsWorkerStore, CacheInvalidationWorkerStore):
)
@cached()
+ async def get_member_counts(self, room_id: str) -> Mapping[str, int]:
+ """Get a mapping of number of users by membership"""
+
+ return await self.db_pool.runInteraction(
+ "get_member_counts", self._get_member_counts_txn, room_id
+ )
+
+ def _get_member_counts_txn(
+ self, txn: LoggingTransaction, room_id: str
+ ) -> Dict[str, int]:
+ """Get a mapping of number of users by membership"""
+
+ # Note, rejected events will have a null membership field, so
+ # we we manually filter them out.
+ sql = """
+ SELECT count(*), membership FROM current_state_events
+ WHERE type = 'm.room.member' AND room_id = ?
+ AND membership IS NOT NULL
+ GROUP BY membership
+ """
+
+ txn.execute(sql, (room_id,))
+ return {membership: count for count, membership in txn}
+
+ @cached()
async def get_number_joined_users_in_room(self, room_id: str) -> int:
return await self.db_pool.simple_select_one_onecol(
table="current_state_events",
diff --git a/synapse/storage/databases/main/state.py b/synapse/storage/databases/main/state.py
index c5caaf56b0..ca31122ad3 100644
--- a/synapse/storage/databases/main/state.py
+++ b/synapse/storage/databases/main/state.py
@@ -736,6 +736,7 @@ class MainStateBackgroundUpdateStore(RoomMemberWorkerStore):
CURRENT_STATE_INDEX_UPDATE_NAME = "current_state_members_idx"
EVENT_STATE_GROUP_INDEX_UPDATE_NAME = "event_to_state_groups_sg_index"
DELETE_CURRENT_STATE_UPDATE_NAME = "delete_old_current_state_events"
+ MEMBERS_CURRENT_STATE_UPDATE_NAME = "current_state_events_members_room_index"
def __init__(
self,
@@ -764,6 +765,13 @@ class MainStateBackgroundUpdateStore(RoomMemberWorkerStore):
self.DELETE_CURRENT_STATE_UPDATE_NAME,
self._background_remove_left_rooms,
)
+ self.db_pool.updates.register_background_index_update(
+ self.MEMBERS_CURRENT_STATE_UPDATE_NAME,
+ index_name="current_state_events_members_room_index",
+ table="current_state_events",
+ columns=["room_id", "membership"],
+ where_clause="type='m.room.member'",
+ )
async def _background_remove_left_rooms(
self, progress: JsonDict, batch_size: int
|
