There are some questions 1, 2 talk about the MongoDB warning Query Targeting: Scanned Objects / Returned has gone above 1000, however, my question is another case.

The schema of our document is

{
    "_id" : ObjectId("abc"),
    "key" : "key_1",
    "val" : "1",
    "created_at" : ISODate("2021-09-25T07:38:04.985Z"),
    "a_has_sent" : false,
    "b_has_sent" : false,
    "updated_at" : ISODate("2021-09-25T07:38:04.985Z")
}

The indexes of this collections are

{
    "key" : {
        "updated_at" : 1
    },
    "name" : "updated_at_1",
    "expireAfterSeconds" : 5184000,
    "background" : true
},
{
    "key" : {
        "updated_at" : 1,
        "a_has_sent" : 1,
        "b_has_sent" : 1
    },
    "name" : "updated_at_1_a_has_sent_1_b_has_sent_1",
    "background" : true
}

The total number of documents after 2021-09-24 is over 600000, and the distinct value of key is 5.

The above waning caused by the query db.collectionname.find({ "updated_at": { "$gte": ISODate("2021-09-24")}, "$or": [{ "a_has_sent": false }, {"b_has_sent": false}], "key": "key_1"})

Our server sends one document to a and b simutinously with batch size 2000. After sending to a successfully, mark a_has_sent to true. The same logic to b. As sending process goes on, the number of documents with a_has_sent: false reduce. And the above warning comes up.

After checking the explain result of this query, the index named updated_at_1 is used rather than updated_at_1_a_has_sent_1_b_has_sent_1.

What we had tried.

1. We add another new index {"updated_at": 1, "key": 1}, and expect this query could use the new index to reduce the number of scanned documents. Unfortunately, we failed. The index named updated_at_1 is still used.
2. We try to replace find with aggregate aggregate([{"$match": { "updated_at": { "$gte": ISODate("2021-09-24") }, "$or": [{ "a_has_sent": false }, { "b_has_sent": false}], "key": "key_1"}}]). Unfortunately, The index named updated_at_1 is still used.

We want to know how to eliminate this warning Scanned Objects / Returned has gone above 1000?

Mongo 4.0 is used in our case.

CodePudding user response：

MongoDB cannot use a single index to process an $or that looks at different field values.

The index on

{
        "updated_at" : 1,
        "a_has_sent" : 1,
        "b_has_sent" : 1
}

can be used with the $or expression to match either a_has_sent or b_has_sent.

To minimize the number of documents examined, create 2 indexes, one for each branch of the $or, combined with the enclosing $and (the filter implicitly combines the top-level query predicates with and). Such as:

{
        "updated_at" : 1,
        "a_has_sent" : 1
}

and

{
        "updated_at" : 1,
        "b_has_sent" : 1
}

Also note that the alert for Query Targeting: Scanned Objects / Returned has gone above 1000 does not refer to a single query.

The MongoDB server keeps a counter (64-bit?) that tracks the number of documents examined since the server was start, and another counter for the number of documents returned.

That scanned per returned ration is derive by simply dividing the examined counter by the returned counter.

This means that if you have something like a count query that requires examining documents, you may have hundreds or thousands of documents examined, but only 1 returned. It won't take many of these kinds of queries to push the ratio over the 1000 alert limit

CodePudding user response：

Follow the ESR rule

For compound indexes, this rule of thumb is helpful in deciding the order of fields in the index:

• First, add those fields against which Equality queries are run.
• The next fields to be indexed should reflect the Sort order of the query.
• The last fields represent the Range of data to be accessed.

We create the index {"action_key" : 1,"adjust_sent" : 1,"facebook_sent" : 1,"updated_at" : 1}, this index could be used by the query now