MongoDB (and Mongoose.js): does the order of the request conditions have an order?

Question

When creating a simple MongoDB query, I have a question about ordering the conditions in the query - for example (Mongoose.js syntax):

conditions = { archived: false, first_name: "Billy" }; 

vs.

 conditions = { first_name: "Billy", archived: false }; 

.. in a simple find () function:

 User.find(conditions, function(err, users) { <some logic> }); 

.. provided a simple one-key indexing strategy:

 UserSchema.index( { first_name: 1, archived: 1} ); 

.. is there an order of the above conditions?

IMPORTANT: I know that order is related to composite indices, but higher. I am wondering what are the key queries with one key. They are also interested in cases of completely non-indexed queries, since we are here. :)

ALTERNATIVE EXPLANATION: In other words, suppose 100 User (50 archival and 50 not), given the two possible internal MongoDB search strategies:

• Filter out all 50 users of archived , then search for the remaining 50 unarchived users with first_name "Billy"
• First, search through all 100 User documents for the first_name "Billy" value, and then filter the found objects by deleting any archived Billys.

.. I would suggest that # 1 will be faster (potentially MUCH faster in large queries with more than two conditions). But no matter what happens faster and why, of course, one of them.

CORE QUESTION: Outside the vast and powerful world of complex indexes, MongoDB knows how to perform the most efficient / fastest searches / filters automatically, no matter which fields and which order? Or do we need to tell the system what is best programmatically (through the order of the conditions presented, etc.)?

Answer 1

I'm a little confused by your question, simply because the index you specified ( { first_name: 1, archived: 1 } ) is a composite index. All of the following queries will use this composite index:

 conditions = { archived: false, first_name: "Billy" }; conditions = { first_name: "Billy", archived: false }; conditions = { first_name: "Billy" }; 

Now suppose we have two separate indexes: { first_name: 1 } and { archived: 1 } . In this case, MongoDB will perform query optimization to determine which index is most efficient to use. You can learn more about MongoDB's query optimization here.

The MongoDB query optimizer, therefore, is likely to use the same index for both of the multi-connector queries you provided:

 conditions = { archived: false, first_name: "Billy" }; conditions = { first_name: "Billy", archived: false }; 

Alternatively, you can use hint to force MongoDB to use the index of your choice. All in all, this is probably not a good idea . You can also manually check which index is most effective for a particular query as described here .

You can see which index is used in the query using the .explain() function in the Mongo shell. (If the index is not used, you will see "cursor" : "BasicCursor" in the resulting document. On the other hand, if you use a compound index, you will see something like "cursor" : "BtreeCursor first_name_1_archived_1" . If one of the indices is one field, you can see "cursor" : "BtreeCursor archived_1" .

In addition, the search strategy for MongoDB works as follows:

• first, cross the index using the boundaries of the index to filter out as many documents as possible;
• next, if there are additional predicates that cannot be executed using the index,
  • document extraction
  • apply predicate
  • and include / exclude the document from the results.

The query optimizer runs all possible query plans in parallel and selects the "best", however, all query plans follow the strategy above. (The BasicCursor is a degenerate case: it goes through all the documents and applies the predicate to each of them.)

tl; dr? Matches are smart enough to match equality predicates when they are presented in any order.

It makes sense?