Query optimization while using skip and limit in Atlas search

MongoDB Atlas Atlas Search
, ,
#1

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.

#3

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

#4

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
#6

Hello @Nanthakumar_DG ,

Can you share your storedSource configuration please?

I have exactly the same problem with pagination.

#7 
{
  "mappings": {
    "dynamic": false,
    "fields": {
      "lvl": {
        "type": "string"
      },
      "data": [
        {
          "dynamic": true,
          "type": "document"
        },
        {
          "type": "string"
        }
      ],
      "time": {
        "type": "date"
      }
    }
  },
  "storedSource": {
    "include": [
      "data",
      "lvl",
      "data"
    ]
  }
}
#8

Hey @Nanthakumar_DG , we are working on improving this for Atlas Search currently, what was your expected latency for this query you were hoping to see? Did stored source help?

#9

@Elle_Shwer Yes storedSource helps to improve the performance of the query timing but still, I need help in this search index the accuracy of the result is not as expected. I need a full-text search type indexing in my case can anyone help with that this index should also need to be sorted in descending, skip and limit.

#10

Hey @Nanthakumar_DG since we posted this we have a sort solution that may also help here: https://www.mongodb.com/docs/atlas/atlas-search/sort/

Pagination is still in progress.