MongoDB (and Mongoose.js): Does the order of query conditions matter?

Question

When creating a simple MongoDB query, I have a question about the ordering of 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> });

..assuming a simple single-key indexing strategy:

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

..does the order of the conditions listed above matter?

IMPORTANT: I know the order DOES MATTER for compound indexes, but per above I am curious about single-key index queries. Also interested in cases of totally non-indexed queries since we're here. :)

ALTERNATE EXPLANATION: Put another way, assuming 100 Users (50 archived and 50 not), given two possible internal MongoDB searching strategies:

1. First filter out all 50 of the archived users, then search through the remaining 50 non-archived users with the first_name value of "Billy"
2. First search through ALL 100 User documents for the first_name value "Billy", and then filter the found objects by removing any Billys that are archived.

..I would assume #1 to be faster (potentially MUCH faster in large queries with more than two conditions). But regardless of which is faster and why, surely one of them is.

CORE QUESTION: Outside the vast and powerful world of compound indexes, does MongoDB know how to perform its most performant/quick searches/filters automatically, regardless of which fields and which ordering? Or do we need to tell the system what is best programmatically (via the order of conditions presented, etc)?

Answer 1

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

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

Now, let's assume we have two separate indexes, { first_name: 1 } and { archived: 1 }. In this case, MongoDB will do query optimization to determine which index is the most efficient to use. You can read more about the query optimization performed by MongoDB here.

The MongoDB query optimizer will thus likely use the same index for both of the multicondition 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 an index of your choosing. In general, this is probably not a good idea. You can also manually check which index is the most efficient for a specific query as detailed here.

You can see which index a query is using by using the .explain() functionality in the Mongo shell. (If no index is used, you'll see "cursor" : "BasicCursor" in the resulting document. On the other hand, if the compound index is being used, you'll see something like "cursor" : "BtreeCursor first_name_1_archived_1". If one of the single-field indexes was used, you might see "cursor" : "BtreeCursor archived_1".

Additionally, the search strategy for MongoDB works like this:

• first, traverse the index, using the index bounds to filter out as many documents as possible;
• next, if there are additional predicates that cannot be satisfied using the index,
  • fetch the document,
  • apply the predicate,
  • and include/exclude the document from the results appropriately.

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

tl;dr? The Matcher is smart enough to match equality predicates when they're presented in any order.

Does that make sense?