Is it possible to limit the number of scanned documents, similar to MySQL subselect?

user_11813677 0
Viewed 161

When querying on MongoDB collection with millions of documents and filtering or sorting on non-indexed fields, queries run too slow because mongo needs to scan the whole collection. On Mysql this is possible by performing a subselect only filtering the last 40k rows, for example:

select c.name, c.age, c.address //another fields
  from (select * from myTable order by id desc limit 40000) as c
 where c.name = 'My name' //more and more filters
 order by c.date_req desc
 limit 25

In this SQL, I get the last 40k rows and then apply the filtering and sorting logic, and it runs quickly even if the table has millions of rows.

On MongoDB, I only get good performance when filtering or sorting indexed fields, otherwise, it runs too slow. I presume I can't create an index in every field, so what can I do in this case? There is something similar to this on MongoDB?

mongodb-query mongodb mysql
1 Answers

You can do this by using an aggregation pipeline that performs the operations in the order you require:

db.coll.aggregate([
    // Get the last 40k docs
    {$sort: {_id: -1}},
    {$limit: 40000},
    // Filter and sort those docs
    {$match: {name: 'My name'}},
    {$sort: {date_req: -1}},
    // Take the first 25 of those
    {$limit: 25}
])
user_12595100
Related