3

We have a social app where users can chat with each other and we’ve reached 350K messages!

We recently noticed that as the number of messages is growing, the find operations are getting slower! I believe the issue here is that the Message collection is not indexed.

That’s what I want to do now! I found this piece of code at the MongoDB docs:

db.comments.ensure_index(('discussion_id', 1))

This is my Message collection:

{
 chatRoom: <Pointer>,
 user: <Pointer>,
 text: <String>,
 isSeen: <Bool>
}

So I guess this is all I have to do:

db.Message.ensure_index(('chatRoom', 1))

Is that just it? Run this command and I’m all set? All existing and future messages will be indexed after that?

Sotiris Kaniras
  • 520
  • 1
  • 12
  • 30

1 Answers1

4

Your index actually should depend on what your query looks like. Suppose your message query looks like this: 

var query = new Parse.Query("Message");
query.equalTo("chatRoom", aChatRoom);
query.equalTo("user", someUser);
query.equalTo("isSeen", false);
query.descending("createdAt");
query.find().then(function(results){//whatever});

Then you would need to build an index on the Message collection specifically for this query. In this case:

db.Message.createIndex({_p_chatRoom:1, _p_user:1, isSeen: -1, _created_at: -1})

Alternatively, an index with just the chatroom will perform much better than no index at all

db.Message.createIndex({_p_chatRoom:1})

To really understand which indexes to build, you'll need to do some reading on the Mongo docs https://docs.mongodb.com/manual/reference/method/db.collection.createIndex/#db.collection.createIndex

I personally use MLab for my Parse MongoDB, because I'm not very knowledgeable about databases, and they actually have a slow query analyzer that recommends indexes based on common queries in your application, so if you don't want to learn the finer points of MongoDB indexing, then MLab is a great place to start

LulzCow
  • 1,199
  • 2
  • 9
  • 21
  • 1
    +1 for mLab being awesome. Their support team is amazing, too, very helpful in getting things set up properly. – Jake T. Aug 09 '18 at 22:11
  • Thanks for your answer!! So if I run that command, all existing and new to come messages will be indexed? – Sotiris Kaniras Aug 10 '18 at 12:19
  • @SotirisKaniras Yes, but you just have to be careful to build the index that matches your specific query. If it doesn't, then it will either be suboptimal or possibly even useless. You'll need to build different variations of the index for any common similar queries as well – LulzCow Aug 10 '18 at 18:29
  • Last thing... what's the `_p_` you added to the chatRoom & user fields? I suppose it has something to do with the fact that they're both pointers? – Sotiris Kaniras Aug 23 '18 at 17:16
  • Also why did you include the `_created_at ` in the index? – Sotiris Kaniras Aug 23 '18 at 17:39
  • @SotirisKaniras the `_p_` is just how Parse pointers are represented in your mongo database. So if you have a pointer to a User called `user` then if you actually look at your MongoDB collection, then instead of just `user`, it'll actually be called `_p_user`. And I added `_created_at` to the index to demonstrate that if you're applying a sort to the query, then that needs to be included in the index. – LulzCow Aug 24 '18 at 18:12
  • I recommend using the command line or some gui tool to look at how your Parse objects are represented in MongoDB. Otherwise it will be confusing when you're trying to build indexes. For example the `createdAt` parse field is actually `_created_at` in MongoDB, and you would only know that if you are able to look at your collections in MongoDB – LulzCow Aug 24 '18 at 18:15
  • I'm having same issue,where I need to place `db.Message.createIndex({_p_chatRoom:1})` – Kishor Patidar Nov 24 '19 at 05:16
  • @LulzCow This way to create index on pointer is not working for me. I tried this: ```db.Offer.createIndex( { "_p_artist": "text","_p_artist.firstname": "text", "_p_artist.lastname": "text", title: "text", title_de: "text", title_fr: "text", title_it: "text", title_ru: "text", drscription: "text", drscription_de: "text", drscription_fr: "text", drscription_it: "text", drscription_ru: "text", category: "text", medium: "text", movement: "text" }, {name:"OfferSearchIndex"}, { default_language:"none" } )``` but the fulltext is not working on firstname and lastname if artist. – Kishor Patidar Aug 17 '20 at 16:09
  • Here is an different question : https://stackoverflow.com/questions/63406448/parse-server-fulltext-search-is-not-working-on-pointer-column – Kishor Patidar Aug 17 '20 at 16:11
  • That’s because `_p_artist` is not an object, it’s just a string holding the ID to the actual artist object. You need to create an index also in the collection `Artist`, eg: `db.Artist.createIndex({ first name: ‘text’})` – Raschid JFR Jan 28 '21 at 06:09