A point record of an index in MongoDB

A federated index is used in MongoDB, and some problems are found. Record it for later reference!

First of all, let's look at the description of the table:

Notice the highlighted index:

Prop.lis_sta_1_prop.is_recom_-1_prop.re_t_-1_prop.post_t_-1, these four fields are

Post status, recommendation status, recommendation time, posting time

After a test, the results are as follows

You can use the query indexed above:

Db.post.find ({'prop.lis_sta' {$in [5]}}) .sort ({' prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1}) .explain (); db.post.find ({'prop.lis_sta':5}) .sort ({' prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1}) .explain () Db.post.find ({'prop.lis_sta' {$in [0 prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1 5]}}) .sort ({' prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1}) .limit (20) .explain ()

You cannot use the query indexed above:

Db.post.find ({'prop.lis_sta' {$in [0 prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1 5]}}) .sort ({' prop.is_recom':-1,'prop.re_t':-1,'prop.post_t':-1}) .explain ()

So here are a few points:

1), try to avoid passing sorting rules in the form of flexible parameters, if there is more data, but there is no relevant index, it is easy to cause problems.

2), modified the collation, to check the index, through the explain to test.

3) it is important to filter out as much data as possible and re-sort using existing indexes, while trying to preserve limit restrictions.