0

Using mongodb 3.6 Using latest C# 7 Mongodb driver.

Goal

I need to get all documents with a certain status in a collection by paging of 1k documents per page.

Example

Assuming each document has the following fields: Id, Status, IGNORE, CreateDate.

I want to fetch all document with status=2. For that I have an index of course, that includes Id:1,Status:1, IGNORE:1, CreateDate:1 which serves me for other queries.

Observation

For ~20k documents, the FIND query (id, status) takes 21ms. Adding the sort (createDate:1) makes it 166ms.

In both scenarios the same index is chosen.

Issues

  1. The jump here seems a little too high, am I missing something?
  2. Can I assume that the sort(createDate:1) is redundant because the documents are already placed like this? (I think not, because Mongodb will probably try to make my queries more efficient).
    (This is answered by the answers to this question).
  3. Is using the index where the 3rd field is unused hurts the operation so much?

Additionals Information

  1. I'm running the paging requests in parallel, let's say 10 requests each time with different page Ix but same amount.
  2. The results were taken by adding .explain("executionStats") in the end of both queries.
SQB
  • 3,926
  • 2
  • 28
  • 49
Ori Refael
  • 2,888
  • 3
  • 37
  • 68
  • 1
    Its not at all duplicate, it only answers 1 of the issues. there are 3 here. – Ori Refael Jun 14 '18 at 07:46
  • You can "play" with a database without specific indexes or sorts, but "real world" usage **always** needs one or the other to be explicitly present. Results are simply unpredictable without it. And that's the cause of ALL your issues, though you don't seem to yet understand why. I suggest more reading. – Neil Lunn Jun 14 '18 at 07:46
  • https://docs.mongodb.com/manual/core/index-compound/#prefixes should answer 1 and 3. – Alex Blex Jun 14 '18 at 08:22
  • @AlexBlex post an answer – Ori Refael Jun 14 '18 at 09:15
  • It's been answered few times already, e.g. https://stackoverflow.com/questions/17735279/mongodb-compound-index-usage. – Alex Blex Jun 14 '18 at 09:45

0 Answers0