0

we have 3 cols {'x','y','z'} and there are millions of records

when i create one by one index

{x:1}
{y:1}
{z:1}

collection.find({'x':'foo','y':'bar','z':'test'})

works faster

but when i do multiple indexes as {x:1,y:1,z:1}

collection.find({'x':'foo','y':'bar','z':'test'})

works more faster

so my question is what will happen for below find;

collection.find({'x':'foo','y':'bar'})

for index {'x':1,:'y':1}

for index {'x':1,'y':1,'z':1}

will they in same speed?

The second question is below indexes are same?

{'x':1,'y':1,'z':1} 
{'y':1,'z':1,'x':1}
Onur Öztürk
  • 343
  • 1
  • 3
  • 12
  • 1
    I think this [official document](https://docs.mongodb.com/manual/tutorial/create-indexes-to-support-queries/#create-compound-indexes-to-support-several-different-queries) is the exact answer to your case, and they have example highly similar to your situation. – ray Sep 24 '21 at 08:57
  • thanks, so if i create {x:1,y:1,z:1} index i do not need to create additional {x:1} for collection find({x:'foo'}) – Onur Öztürk Sep 24 '21 at 09:05
  • yes from my understandings :) – ray Sep 24 '21 at 09:07
  • 1
    From the documentation, again, your second question has the answer that the two indexes are not he same. And, it also applies to your comment - refer the topic about Compound Index Prefix. – prasad_ Sep 24 '21 at 09:07
  • thanks @prasad_ so {x:1,y:1,z:1} index does supports collection.find({'x':'foo', 'z':'bar'}) ? – Onur Öztürk Sep 24 '21 at 09:16
  • 1
    Also, see this post on using compound indexes: https://stackoverflow.com/questions/62663992/mongodb-does-compound-index-on-multiple-fields-indexes-the-id-column-by-default/62669480#62669480 – prasad_ Sep 24 '21 at 09:19

0 Answers0