MongoDB: Creating index very slow

Why is indexOnly: false. Isn't it supposed to be a covered index query? (see the explain later)

I believe this is a result of the isMultiKey : true field in the explain results. Basically, currently indexOnly is never true when isMultiKey is true.

This is a known problem in general with multi key indexes. You can find the relevant bug here:

https://jira.mongodb.org/browse/SERVER-3173

As well as some decent explanation in the linked/dupe bug here:

https://jira.mongodb.org/browse/SERVER-7595

I think you have done some manual munging of the fields here for some reason, but I would guess that search.keywords is the problem here. Try an index without that as the final field and see if that performs better.

I need to retrieve some additional fields from the collection (the id and the profile_picture url). Should I add them to the index to avoid hitting the collection, even if I'll never have to query them?

I'd recommend a separate index for those queries rather than massive single index. If you end up with too many fields in the index you are going to lose most of the benefit by simply having to scan through a massive index instead of a collection. An index that big will also likely have performance issues for updates/writes.

The lines mean that each [getMore][1] (essentially each iteration on the cursor) is returning 16 documents and that each one is 4MB (the default max batch size limit). Essentially it is taking a little over 100ms to fetch the documents and return them, and they seem to be around 250KB each.

Note that the queries are yielding the lock, so they are likely not in memory and are being paged off disk (this is not the only reason that it might yield but it is the most likely).

Here's a quick breakdown of the interesting fields:

• nreturned:16 = number of documents returned by the getmore
• numYields: 4 = number of times that getmore yielded the lock
• locks(micros) r:240837 = the time in microseconds that the read lock was held in total (r), which can be greater than the total execution time
• reslen:4265572 = the size of the result in bytes (~4MB)
• 135ms = the time to complete the getmore operation (100ms is the default threshold for logging slow queries)

If I were to hazard a guess I would think that the { $gte: 5, $lte: 10 } matches an awful lot more data than the { $gte: 10, $lte: 1000000 } criteria, and that all of that data is not fitting into memory, but it is hard to be sure with such a limited snapshot of the activity.