Index use with regex for sorting

MongoDB University Certification Exams
1

In the developer certification practice exam I have trouble understanding why this query:

db.users.find( { "user.login": /^ir.*/ }, { "user":1, "_id":0 } ).sort( { "user.date":1 } )

with the index:

{"user.login":1, "user.date": -1}

on this document:

{
  "_id": ObjectId("5360c0a0a655a60674680bbe"),
  "user": {
    "login": "ir0n",
    "description": "Made of metal"
    "date": ISODate("2014-04-30T09:16:45.836Z"),
  }
}

It is using the index just for fetching data but not for sorting. I would expect the returned data to be sorted thanks to the index.

1 Like
2

I would try to implement the same on my local instance and see what happens :slight_smile:

Kanika

3

Assume documents:
{ u : { l : ir3 , d : 2015 } }
{ u : { l : ir3 , d : 2012 } }
{ u : { l : ir1 , d : 2014 } }
{ u : { l : ir2 , d : 2013 } }

The index order the documents in the following way:

l : ir1 , d : 2014
l : ir2 , d : 2013
l : ir3 , d : 2015
l : ir3 , d : 2012

but what you want them in the order:
l : ir3 , d : 2012
l : ir2 , d : 2013
l : ir1 , d : 2014
l : ir3 , d : 2015

As you see the index does not support the order you want. The key of the answer lies in index prefixes. I would recommend that you go over https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/

3 Likes
4

Here’s a quick reason why the index is not used for sorting.

ESR [Equality Sort Range]

{ “user.login”: /^ir.*/ } : This is a range

The only index is {“user.login”:1, “user.date”: -1}, since there is no Equality and you cannot use this index for Sort as the first index is user.login, you can only use this index for range.

So, if the index was {“user.date”: -1, “user.login”:1}, then yes this index will be utilize for the sort stage and then follow by the range ~

2 Likes