Index Rule : Equality > Sort > Range

[Kim_Hakseon]

While watching university, I became curious.

Assuming that there are documents as shown below,

db.collection.find()
{ a: 1, b: 1, c: 1, d: 1, e: 1, f: 1 }
{ a: 2, b: 2, c: 2, d: 2, e: 2, f: 2 }
{ a: 3, b: 3, c: 3, d: 3, e: 3, f: 3 }
. . .
{ a: n, b: n, c: n, d: n, e: n, f: n }

I want to use the following find command.

db.collection.find( { a: 1, b: 1, c: 1, d: { $gt: 3 } } ).sort( {e: 1, f: 1} )

Then, can we make it as below according to the Index Rule?

db.collection.createIndex( { a: 1, b: 1, c: 1, e: 1, f: 1, d: 1} )
================|==equality==|=sort=|=range

I asked you only theoretical information, not practical application.

[MaBeuLux88 (Maxime Beugnet)]

Hi @Kim_Hakseon,

Yes, your index would be the best one for this query according to the ESR rule. The trade off is that more index keys will need to be scanned as some documents will be picked up from the index (but sorted!) and then eliminated by the range query at the end.

The fact that you pick the index entries sorted and THEN remove some of them afterwards allow you to avoid the in-memory sort that would be required otherwise to solve this query.

Basically, with this index, you get the sort for free. You can confirm this by running an .explain(true) on it.

The index a,b,c,d,e,f would be more selective (less index entries would need to be scanned. But they aren’t sorted by e & f anymore before of the range query before them.

The best representation that I found for this is to imagine something like this. Let’s say my use case is find name = bob & age > 12 sorted by size.
I imagine I’m in a room. In this room there are big cupboards => one for each name (Alice, Bob, Maxime, …).
In them, I have large boxes. One for each age. And in each of these boxes, I have little folders, sorted by size.
If I take name = Bob, I go to the cupboard with Bob on it. Then if my index is (name, age, size), I need to take all the large boxes in it with age > 12… And to finally answer the query I need to take all the folders in all these boxes that aren’t sorted all together… So I need to manually sort them

If you change the index order to (name, size, age) you can just pick the docs from the folders and pile them up in order as they are already ordered by size if you pick the boxes in the right order ;-).

I hope this helps a bit.
Cheers,
Maxime.

[Kim_Hakseon]

Hi, @MaBeuLux88
Thanks to the really kind explanation, it was too easy for me to understand.
I think I really need to think a lot about the index and learn more by testing it a lot.

Thank you so much.