Query optimization while using skip and limit in Atlas search

HI
I am trying to $skip and $limit after $search in the aggregation. Each and every time when I try to increase my skip size the execution time gets longer
Example:

  • Skip 10 and limit 10 then execution time is 500ms
  • Skip 30 and limit 10 then execution time is 700ms
  • Skip 50 and limit 10 then execution time is 900ms
  • Skip 800 and limit 10 then execution time is 20Sec

My code:

db.collection.aggregate([
    {
        $search: {
            "index": 'search',
            "count": { "type": "total" },
            "compound": {
                "must": [{
                    "range": {
                        "path": "timestamp",
                        "gte": ISODate('2020-01-01'),
                        "lte": ISODate()
                    }
                },
                {
                    "text": {
                        "query": '(.*)info(.*)',
                        "path": ['field1', 'field2']
                    },
                },
                {
                    "near": {
                        "path": 'timestamp',
                        "origin": ISODate(),
                        "pivot": 7776000000
                    }
                }
                ],
            }
        }
    },
    { $skip: 10 },
    { $limit: 10 }
])

I need to know if is there any other way to optimise the query to get faster and if is there any way to specify ascending or descending order in the Atlas search index.

Hi @Nanthakumar_DG - Welcome to the community :wave:

MongoDB still has to iterate over documents to skip them which explains what you were experiencing in the above quote.

Just wanting to understand more of the use case details here - Is the question about pagination of Atlas Search results? Could you provide more details on the intended use case?

Regards,
Jason

YEP, That’s about pagination each and every time when I go to the next page in UI response time is increasing as I mentioned in the Example.
I found the solution for that and made a change in the Atlas search index and that works well.
Ref: storing-source-fields
Thanks for the reply @Jason_Tran

1 Like