0

I have collection with 60000000 Record. When running this query it take 5 min.

 collection.Find(f => f.EVN_EVT_PRS_FIRST_NAME.EndsWith("yasmine"))
   .SortBy(o=>o.EVN_EVT_PRS_FIRST_NAME).Skip(1600000).Limit(20).ToList();

Produces:

[ 
    { $match: { $text: { $search: \"sharma\" } } },
    { $skip: 160000 },
    { $limit: 20 },
    { $sort : { EVN_EVT_PRS_FIRST_NAME : -1 } }
]

this query take 6 min

Is there any way to fetch the data faster.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Sanjay Prakash
  • 11
  • 1
  • 1
    Do you have indices on 'EVN_EVT_PRS_FIRST_NAME'? – Danielle Summers Jun 14 '18 at 12:32
  • IF you can actually do so, then the only thing that can really save you here is a "range based" approach to paging instead of the `skip`, because that's really slow. The only other case you have is this probably should be a sharded collection at this size, and with a shard key which would reasonably distribute the load. For @DanielleSummers. it's a `$text` search, so there **must** be an index otherwise the query will simply produce an error. – Neil Lunn Jun 14 '18 at 12:35
  • And always good to see that people are really observant and "upvote" the comment that did not notice that it's a "text search" where you have no choice but to have an index. Kind of speaks volumes on the quality of votes here. The comment is irrelevant as I just noted already. – Neil Lunn Jun 14 '18 at 12:37
  • @NeilLunn, I couldn't find anything in documentation that explicitly states that there must be an index for $text searches, but I'll take your word for it. We honestly don't perform text searches, so I was in part shooting in the dark with my comment. – Danielle Summers Jun 14 '18 at 12:54

0 Answers0