Efficient way to transverse mongoDB's indexes with an iterator/pointer?

Question

We are developing a C++ application development tool that uses mongoDB as the underlying database. Say the user has developed a patient collection with fields _id (OID) & patient# with a unique ascending index on patient#. Say there are 10 patients with patient#s 1, 5, 7, 13, 14, 20, 21, 22, 23, 25. Patient# 20 is displayed fetched with limit(1). The user presses PageDown and patient# 21 is displayed -- easy with $gt with patient# = 20.

When the user presses PageUp, patient# 14 should be displayed. My (hopefully wrong) solution is to create a parallel descending index on patient# and $lt. That implies every collection a user creates requires both indexes on the primary key fields to get bidirectional movement. That would apply also to secondary indexes such as name.

Additionally, the user presses F5, is prompted "# of records to move", they enter 3, patient# 23 should be displayed. Or they enter -3, patient# 7 should be displayed. My idea: First use a covered query and return the following 3 patient#s from the index and then fetch the 3rd document. This isn't at all ideal when a less simplified application has hundreds of thousands of documents and the user wants to transverse by 10s of thousands of records. And, again, to achieve the backward movement, I believe I would need that second descending index.

Finally, I need a way to have the user navigate to the last index entry (patient #25). Going to the beginning is trivial. Again, second index?

My question: Is there a way to transverse the ascending index using (say) an iterator or pointer to the current index element and then use iterator/pointer arithmetic to achieve what I want? I.e., +1 will get me the "next" index element from which I could fetch the "next" document; -1 the "previous", +3 the third following, -3 the third previous; index size to the last. Or is there another solution without so much overhead (multiple indexes, large covered queries).

Answer 1

A more concrete description of what I am trying to do can be found here:

How to get the previous mongoDB document from a compound index

The answer is: It can't be done in the current version. The 2nd jira ticket is a possible future fix.

See: SERVER-9540

and

SERVER-9547

Answer 2

The way to achieve what you want is to have an index on the relevant fields and then do simple queries to get you the records you need when you need them.

It's important to not over-think the problem. Rather than trying to break down how the query optimizer would traverse the index and trying to "reduce" somehow the work it does, just use the queries you need to get the job done.

That means in your case querying for records you need and when the user wants to jump to a particular record querying for that record. If someone is looking at record 27 and they want to go to the next one you can query for smallest record greater than 27 via descending sort and limit(1) qualifier on your find.

I would encourage you to revisit your choice to have basically two primary keys - instead of separate patientID field which has a unique index, you can store patientId in the _id field and get to use the already existing unique index on _id that MongoDB requires in every collection.