CODEWITHSUNDEEP
c#mongodbmodeling
Oren Ellenbogen
Oren Ellenbogen

Reputation: 154

MongoDB querying/modeling thougths

Model

I've got a collection of ChatRoom:

ObjectId Id
ObjectId GroupId 
ObjectId LastMessageId 
List<ObjectId> Members 
bool IsEveryone

I've got a collection of ChatMessage:

ObjectId GroupId
ObjectId RoomId
ObjectId Id
ObjectId UserId
string Text
DateTime Date

Background

Each user is part of multiple groups. Each group contains multiple rooms. I have all of the rooms the user is part of and array of the last messages id that the user saw so far.

The idea is to calculate delta for a given user: (1) bringing back all of the last messages ids from all rooms and (2) calculate how many chat messages the user missed (new messages).

The server gets the latest msg ids that the user saw so far (so I can pull the date of that messages and calculate how many new messages exist that the user didn't see).

Query

What I'm looking for is a way to run 2 fast queries for:

  1. Return the latest message id per room (by date, desc). Input: array of room-id. Output: array of [room-id]:[msg-id]

  2. Calculate how many new messages (integer) exist per room for the given user. Input: array of [user-id, room-id, last-message-date]. Output: array of [room-id]:[int]. Notice that I don't want to count messages that the user wrote.

Can you help with constructing the optimized queries? I'm using MongoDB C# 10gen SDK.

UPDATE:

Every time I now write a new chat message, I also run an atomic update on the ChatRoom to keep the LastMessageId. This solves the first query while keeping perf sanity (read rate > write rate).

Upvotes: 2

Views: 280

Answers (1)

Andrew Orsich
Andrew Orsich

Reputation: 53675

Going to answer your questions.:

1. To achieve best performance you need to create proper compound index:

db.ChatMessage.ensureIndex({GroupId: 1, RoomId:1, Date: -1})

Then query will be like this:

db.ChatMessage.find({GroupId: 2, RoomId:3}).sort({"Date": -1})

If you need return only {RoomId, MessageId} object you can specify these fields:

db.ChatMessage.find({GroupId: 2, RoomId:3}, //filter messages
             {RoomId:1, _id:1}) // specify set of fields thats query should return
             .sort({"Date": -1}) // sort by date desc
             .limit(10) // take a specific number of last messagies

To make sure that query use proper index you can use explain() method.

2. For your second question i suggest to prerecalculate number of new messagies using set operator rather then use map/reduce because it work slow for big sets of data. Or another good option can be incremental asynchronous map/reduce.

In depending on your needs in rooms collection you can have NumberOfNewMessagies, or nested array [{UserId, MessagiesCount}]. So when you will load room you will always have number of new messagies.

btw, let me know if you will have any troubles convert mongodb shell queries into c# code.

Hope this will be helpfull for you.

Upvotes: 2

Related Questions