-2

The logic of my query goes like this:

  1. Filter documents based on arbitrarily many and non-predetermined fields. The filter conditions can total to hundreds. But the worst case is that there are no filter conditions at all, hence 100% of documents will pass.
  2. Lookup / join all of the documents returned in 1. from another collection / table to further filter the above documents. For example, looking up blocked users to remove them. This lookup / join will be indexed.
  3. Compute derived numeric fields for all documents that pass.
  4. Sort the passing documents based on arbitrarily many fields, which may include the derived fields from 3.
  5. Limit and then return only 50 of these documents to the client. The sorting and limit must happen last because of 2. and 3.

Let's suppose that the collection / table has 10 million documents. In worst case scenario, all 10 million documents must be examined in order to return just 50 to the client. There is no way to avoid this.

  1. What are the solutions to optimize this query for the worst case?
  2. In particular, is sharding a viable solution? For example, if there are 100 shards, then each server will only have to examine 100,000 documents in worst case. If there are 1000 shards, then each server will have to examine 10,000 documents in worst case. This query will be a scatter and gather, which will be held back by the slowest server. However, the slowest server will only have to examine 10,000 documents maximum.

Schema of main user collection / table:

{
  _id: "ObjectId",
  age: "number",
  city: "string",
  school: "string",
  height: "number",
  // etc., potentially hundreds of other attributes describing the user
}

Schema of the lookup / join collection:

{
  blocker: "objectId from main user collection", 
  blocked: "objectId from main user collection"
}

Schema of output documents:

{
  age: "number",
  city: "string",
  school: "string",
  height: "number",
  derived_field: "number",
  // etc. potentially others depending on filter
}

Cost feasible? If my APP can get to 10 million users, then the 1000 servers should not be an issue?

Improve this question
7
  • 1
    "In worst case scenario, all 10 million documents must be examined in order to return just 50 to the client. There is no way to avoid this." - Why? With a properly architected query & the correct index, this can definitely be avoided. You've asked a series of misguided sounding questions on this recently. I think understanding what your query does, with some sample data, and expected results, would go a long way in helping us being able to better recommend how to improve your process. If you could provide the object structure with an example of the filtering that will occur, that would help.
    – J.D.
    Jul 27 at 19:52
  • @J.D. I Have edited my question to include the schemas of the collections / table and the output of the query.
    – Bear Bile Farming is Torture
    Jul 28 at 0:06
  • But as stated, "correct index" will not help because the assumption is that there may be no filter conditions to limit the number of passing documents.
    – Bear Bile Farming is Torture
    Jul 28 at 0:07
  • But in your previous comments on the other Posts, you mentioned that there would be filtering and that not all of the records would be returned. Otherwise if there isn't any filtering, then the 50 rows you do return will be arbitrary?...And if you do want 50 arbitrary rows, then there should be an operation that returns the first 50 rows scanned, without having to scan the entire catalog of data. This is an automatic operation that occurs in SQL databases, it should be not much different in MongoDB. Under no circumstances should you need to scan the entire dataset unless you want to.
    – J.D.
    Jul 28 at 3:56
  • The filter conditions are arbitrary, which means that there could be one, hundreds, or zero. So in worst case, all documents could pass through. And no, the 50 documents that are ultimately returned to the client are not arbitrary. For example, they could be the youngest 50. Or they could be the closest in distance or some derived field. So the query cannot just return the first 50 found. They must be manually sorted and only then can the top 50 be returned.
    – Bear Bile Farming is Torture
    Jul 28 at 5:03

1 Answer 1

Reset to default
1

Again (or still) you have a wrong picture in front of you.

Maybe for comparison and as an example, have a look at https://www.stayfriends.com/team/, it is a German social network, however with tools like http://www.deepl.com/ you can translate the page.

They have 20 Million users, so it would be similar number according to your dreams. They employ 80 people (30 out of them in IT) and they run 300 servers. Of course, only a small subset of these servers are hosting the actual application.

I doubt that you can afford 1000 servers (1000 server only for your actual application, which means maybe 5000-10000 servers in total) having only 10 Million users.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.