英文:
In Mongodb, should i use two indexes (with one a subset of the other) or a single one to have the best performance?
问题
假设我有一个MongoDB数据库，里面有数百万个文档。我有一个Python脚本，尝试获取这些文档，因为用户的输入，有时必须使用以下字段来筛选文档：
field1
field2
field3
文档字段，其他时候我只需要field1，field2和其他只需要field1。
我应该创建3个索引，一个包含这三个字段的复合索引，一个只包含field1和field2的子集，第三个只包含field1，还是只有第一个更好？
我尝试了一些实验，但结果相当不一致。
英文:
Let's say I have a mongodb database with a collection of milions of documents. I have a python script that tries to fetch those documents, because of the input of the user, sometimes i have to filter the documents using:
field1
field2
field3
document fields, other times i only need field1, field2 and others only field1.
Should I create 3 indexes, one containing compound all the three fields, one with the subset of field1 and field2 and a third with only field1, or is it better to have only the first one?
I tried doing some experiments but i'm having quite inconsistent results.
答案1
得分: 1
索引创建在{field1、field2、field3}上（按顺序）可用于三种类型的查询：
- db.doc.find(field1='a val', field2='a val', field3='a val')
- db.doc.find(field1='a val', field2='a val')
- db.doc.find(field1='a val')
但不能用于以下查询：
- db.doc.find(field2='a val')
所以，如果您要按照您提到的方式进行查询 => 只需按照这个顺序创建一个索引。
如果您要以不同的顺序进行查询 => 您将需要多个索引。
英文:
Index created on {field1, field2, field3} (IN that order) can be used for three types of queries:
db.doc.find( field1= 'a val' ,field2='a val', field3='a val')
db.doc.find( field1= 'a val' ,field2='a val')
db.doc.find( field1= 'a val')
But it can NOT be used for the following:
db.doc.find( field2= 'a val')
So if you going query for exactly they way you mentioned => Create only one Index in that order.
If you going to query in different orders => you will need multiple indexes
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论