0

Let's say I have some records as below:

{
  id: 1,
  age: 22,
  name: 'A',
  class: 'Y'
},
{
  id: 2,
  age: 25,
  name: 'B',
  class: 'D'
},
{
  id: 3,
  age: 30,
  name: 'C',
  class: 'Y'
},
{
  id: 4,
  age: 40,
  name: 'D',
  class: 'B'
}

Now I need to get the last (closest) record which has an age less than 28. For this, I can use the following code:

const firstUsersYoungerThan28 = await Users.find({
  class: 'C',
  age: {
    $lt: 28
  }
})
  .sort({
    age: -1
  })
  .limit(1)
  .lean();

const firstUserYoungerThan28 = firstUsersYoungerThan28[0];

Let's say the collection have millions of records. My question is, is this the most efficient way? Is there a better way to do this?

My biggest concern is, does my app load the records to the memory in order to sort them in the first place?

THpubs
  • 7,804
  • 16
  • 68
  • 143

2 Answers2

3

See the documentation about cursor.sort():

Limit Results

You can use sort() in conjunction with limit() to return the first (in terms of the sort order) k documents, where k is the specified limit.

If MongoDB cannot obtain the sort order via an index scan, then MongoDB uses a top-k sort algorithm. This algorithm buffers the first k results (or last, depending on the sort order) seen so far by the underlying index or collection access. If at any point the memory footprint of these k results exceeds 32 megabytes, the query will fail.

Make sure that you have an index on age. Then when MongoDB does the sorting, it will only keep the first k (in your case, 1) results in memory.

MongoDB can handle millions of documents for such basic queries, don't worry. Just make sure that you do have the proper indexes specified.

str
  • 42,689
  • 17
  • 109
  • 127
0

You should create an index with the following properties:

  1. The index filters everyone with an age below 28
  2. The index sorts by currentMeterReading.

This type of index is a partial index. Refer to the documentation about partial indexes and using indexes for sorting for more information.

The following index should do the trick: db.users.createIndex( { age: -1, currentMeterReading: -1}, { partialFilterExpression: { age: {$lt: 28} } )

Without the index, a full scan would probably be in order which is very bad for performance.
With the index, only the columns you specified will be stored in memory (when possible) and searching them would be much faster.
Note that MongoDB may load the values of the index lazily instead of on index creation in some or all cases. This is an implementation choice.

As far as I know, there's no way to create an index with only the last record in it.

If you want to understand how databases work you have to understand how indexes work, specifically B-Trees. B-Trees are very common constructs of all databases.
Indexes do have their disadvantages so don't create one for each query. Always measure before creating an index since it might not be necessary.

the_drow
  • 18,571
  • 25
  • 126
  • 193
  • Sorry I updated the sample to better explain the need. But even after that using this index will make a permanent index with records which have age less than 28 right? In the search, the age value changes from time to time. – THpubs Mar 18 '18 at 01:35
  • If the age criteria changes you'd have to index the entire age column without the `partialFilterExpression` or create multiple indexes if there are only few choices in values. – the_drow Mar 18 '18 at 07:51
  • You can also create a temporary index with TTL in MongoDB but that's at the cost of rebuilding the index when it expires. – the_drow Mar 18 '18 at 07:52