50 suggestions for MongoDB developers Tip25

This series of articles translated from "50 Tips and Tricks for MongoDB Developers", temporarily did not find the Chinese version, anyway, I have recently been in-depth study of mongodb, so just take it to translate. On the one hand, strengthen the effect of your own learning, on the other hand, let everyone experience what we mongodb users need to pay attention to.

First of all, I declare that my English level is not too high. In addition, some English translations into Chinese cannot find suitable words to express them. Therefore, English original words may appear in the article, or translation in some places will be somewhat rigid, that is to say, literal translations will appear. The main purpose of translating this book is to study and discuss it for everyone. If there is any inaccurate translation, or if there is a more accurate translation, please point out that I will correct it in time. Thank you in advance.

Tip#25.Use compound indexes to make multiple queries fast

Using composite indexes to speed up queries

If possible, create composite indexes that can be used by multiple queries. It's not always possible, but it's necessary if your queries have similar conditions.

Any query that primarily matches the prefix of an index can use that index. Therefore, the index you build should contain the maximum number of conditions shared by multiple queries.

Suppose your application executes the following query.

collection.find({"x":criteria,"y":criteria,"z":criteria})collection.find({"z":criteria,"y":criteria,"w":criteria})collection.find({"y":criteria,"w":criteria})

As you can see, y is the only field used by all queries, so the y field is a good candidate. The z field appears in the first two queries and the w field appears in the second two queries, either of which will be the next candidate.

We want to hit the index as hard as possible. If it can be determined that the first query is important or more frequent than the other two, we build an index that favors the first query. Assuming that the first query executes thousands of times more than the other two, we can build the following index.

collection.ensureIndex({"y":1,"z":1,"x":1})

The first query was highly optimized, and partial queries for the other two could use the index.

If all queries are run equally, the following indexes can be created.

collection.ensureIndex({"y":1,"w":1,"z":1})

Because the condition contains y, so three queries can use this index, the latter two can use w, the middle can be completely even this index.

You can view index usage for a query by issuing the following command:

collection.find(criteria).explain()