Is $IN operator able to use Index?

Working with Data
1

I have an index on the AsnLpn field but for some reason when I use an $IN statement it seems to bypass my index for a COLLSCAN

image
image1437×887 37.3 KB

2

It should.

Please provide the output of:

db.ConveyorReceipt.getIndexes()

> c.getIndexes()
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  { v: 2, key: { a: 1 }, name: 'a_1', background: false }
]
> c.find( { a : { '$in' : [ 2 , 3 ]}}).explain().queryPlanner.winningPlan
{
  stage: 'FETCH',
  inputStage: {
    stage: 'IXSCAN',
    keyPattern: { a: 1 },
    indexName: 'a_1',
    isMultiKey: false,
    multiKeyPaths: { a: [] },
    isUnique: false,
    isSparse: false,
    isPartial: false,
    indexVersion: 2,
    direction: 'forward',
    indexBounds: { a: [Array] }
  }
}
3

Thanks Steeve, I actually posted the wrong query. Here’s the correct query, showing its not using index

image
image376×810 73.3 KB

and here are the indexes
image
image1266×732 123 KB

image
image816×675 17.8 KB

4

This is an $or query. That means all documents that match first condition, union with all documents that match the other condition. If you don’t have a separate index for each of the conditions it’ll need to do a collection scan anyway.

3 Likes
5

Thank you Asya, so id have to have individual indexes on each of those field CaseId and AsnLpn?