5

Is there an efficient way to do a range-based query across multiple collections, sorted by an index on timestamps? I basically need to pull in the latest 30 documents from 3 collections and the obvious way would be to query each of the collections for the latest 30 docs and then filter and merge the result. However that's somewhat inefficient.

Even if I were to select only for the timestamp field in the query then do a second batch of queries for the latest 30 docs, I'm not sure that be a better approach. That would be 90 documents (whole or single field) per pagination request.

Essentially the client can be subscribed to articles and each category of article differs by 0 - 2 fields. I just picked 3 since that is the average number of articles that users are subscribed to so far in the beta. Because of the possible field differences, I didn't think it would be very consistent to put all of the articles of different types in a single collection.

paulkon
  • 1,755
  • 2
  • 20
  • 34

3 Answers3

2

MongoDB operations operate on one and only one collection at a time. Thus you need to structure your schema with collections that match your query needs.

Option A: Get Ids from supporting collection, load full docs, sort in memory

So you need to either have a collection that combines the ids, main collection names, and timestamps of the 3 collections into a single collection, and query that to get your 30 ID/collection pairs, and then load the corresponding full documents with 3 additional queries (1 to each main collection), and of course remember those won't come back in correct combined order, so you need to sort that page of results manually in memory before returning it to your client.

{
  _id: ObjectId,
  updated: Date,
  type: String
}

This way allows mongo to do the pagination for you.

Option B: 3 Queries, Union, Sort, Limit

Or as you said load 30 documents from each collection, sort the union set in memory, drop the extra 60, and return the combined result. This avoids the extra collection overhead and synchronization maintenance.

So I would think your current approach (Option B as I call it) is the lesser of those 2 not-so-great options.

Community
  • 1
  • 1
Peter Lyons
  • 142,938
  • 30
  • 279
  • 274
  • I edited my answer above and with the context added, do you think there would be a better way to do this if I change the schemas or just use a single collection and disregard the field differences among docs? – paulkon Aug 28 '13 at 04:05
  • NoSQL schema design is highly dependent on the specifics of the application. Based on this one query, storing them all in one collection might well be your best solution, but your app may have other use cases that suffer as a result of that. Hard to say from my standpoint. Just try them both, build a proof of concept or A/B test them and see what makes the best overall sense for your app. – Peter Lyons Aug 28 '13 at 05:02
2

If your query is really to get the most recent articles based on a selection of categories, then I'd suggest you:

A) Store all of the documents in a single collection so they can utilize a a single query for fetching a combine paged result. Unless you have a very consistent date range across collections, you'll need to track date ranges and counts so that you can reasonably fetch a set of documents that can be merged. 30 from one collection may be older than all from another. You can add an index for timestamp and category and then limit the results.

B) Cache everything aggressively so that you rarely need to do the merges

WiredPrairie
  • 58,954
  • 17
  • 116
  • 143
0

You could use the same idea I explained here, although this post is related to MongoDB text search it applies to any kind of query

MongoDB Index optimization when using text-search in the aggregation framework

The idea is to query all your collections ordering them by date and id, then sort/mix the results in order to return the first page. Subsequent pages are retrieved by using last document's date and id from the previous page.

Community
  • 1
  • 1
Jordi
  • 93
  • 5