0
db.col_name.createIndex({"A": 1, "B", 1})

db.col_name.find({A: {$in: [1, 2, 3]} B: 50})

Since three values are selected for the prefix index, would that invalid the compound index?

If not, how will it solve this problem?

Bear Bile Farming is Torture
  • 4,317
  • 4
  • 27
  • 61
  • You can check yourself by [explain-result](https://www.mongodb.com/docs/manual/reference/explain-results/), and also you can check the performance of the query in [compass](https://www.mongodb.com/docs/compass/current/query-plan/), refer the same question in [mongodb forum](https://www.mongodb.com/community/forums/t/compound-index-with-queries-that-filter-for-multiple-values-of-a-prefixed-field/195772). – turivishal Oct 26 '22 at 05:25
  • But isn't there a guiding principle to this? In theory, what would happen? – Bear Bile Farming is Torture Oct 26 '22 at 05:55
  • 1
    All the information is explained in detail with the example, if you take a look at the documentation, Your question is related to the compound index and multikey index, so I just found the same example you asked here in [multikey index documentation](https://www.mongodb.com/docs/manual/core/index-multikey/#compound-multikey-indexes). It does not make any sense to explain that details again in answer. – turivishal Oct 26 '22 at 06:08
  • I think @turivishal points toward some good resources here. But out of curiosity, what do you mean by 'invalidate' the compound index? And when you say 'solve the problem', are you asking how the database constructs a plan for that query shape which efficiently uses that index? – user20042973 Oct 26 '22 at 16:35
  • @user20042973 B is sorted based on A. So if you have just one value of A, then you can use the index on B because B is sorted on this one value of A. But if you have multiple values of A, then the values of B are no longer sorted. Thus the compound index is invalidated? – Bear Bile Farming is Torture Oct 26 '22 at 16:42
  • The query in your question doesn't have a sort applied to it? Is your question specifically about how MongoDB attempts to provide _sorted_ results using a compound index when there is an `$in` clause in the query predicate? – user20042973 Oct 26 '22 at 16:46
  • @user20042973 I meant sorting in that an index is sorted. The compound index first sorts on A, then sort B on the values of A. So for a given value of A, we have an working index of B. But for multiple given values of A, the index on B no longer works? – Bear Bile Farming is Torture Oct 26 '22 at 16:58

0 Answers0