Can I write an index for an $or query in mongodb?

user_1078829 0
Viewed 29

I have a few different but similar mongodb queries that I need to write an index for. Is this the correct way to do it?

First Query:

{
    $or: [
        {_id: "..."}, 
        {linkedListingID: "..."}
    ]
}

Second Query:

{
    $or: [
        {_id: "..."}, 
        {linkedListingID: {$in: ["...", "..."]}
    ]
}

Third Query:

$or: [
    {
        _id: {$in: ["...", "..."]},
        linkedListingID: {
            $exists: false,
        },
    },
    {
        linkedListingID: {$in: ["...", "..."]},
    },
];

Index:

Listing.index(
    {
        _id: 1,
        linkedListingID: 1
    },
    {name: "index_name"}
);
mongodb
2 Answers

As per $or documentation,

When using indexes with $or queries, each clause of an $or can use its own index.

That means it will not use compound index!

To support your query, rather than a compound index, you would create one index on _id and another index on linkedListingID:

Listing.index({ _id: 1 });
Listing.index({ linkedListingID: 1 });

For more details try explain() with your query, and check executionStats > executionStages > inputStage

Explain state with compound index:

Listing.index({ _id: 1, linkedListingID: 1 }, {name: "index_name"});

Result: This will scan full collection "stage": "COLLSCAN"!

    "winningPlan": {
      "inputStage": {
        "direction": "forward",
        "filter": {
          "$or": [
            {
              "_id": {
                "$eq": "abc"
              }
            },
            {
              "linkedListingID": {
                "$eq": "bca"
              }
            }
          ]
        },
        "stage": "COLLSCAN"
      },
      "stage": "SUBPLAN"
    }

Playground

Explain state with single index: Here _id not needed index because by default _id having unique index,

Listing.index({ linkedListingID: 1 });

Result: this will at least use index individually "stage": "IXSCAN"!

    "winningPlan": {
      "inputStage": {
        "inputStage": {
          "inputStages": [
            {
              "direction": "forward",
              "indexBounds": {
                "_id": [
                  "[\"abc\", \"abc\"]"
                ]
              },
              "indexName": "_id_",
              "indexVersion": 2,
              "isMultiKey": false,
              "isPartial": false,
              "isSparse": false,
              "isUnique": true,
              "keyPattern": {
                "_id": 1
              },
              "multiKeyPaths": {
                "_id": []
              },
              "stage": "IXSCAN"
            },
            {
              "direction": "forward",
              "indexBounds": {
                "linkedListingID": [
                  "[\"bca\", \"bca\"]"
                ]
              },
              "indexName": "_linkedListingID",
              "indexVersion": 2,
              "isMultiKey": false,
              "isPartial": false,
              "isSparse": false,
              "isUnique": false,
              "keyPattern": {
                "linkedListingID": 1
              },
              "multiKeyPaths": {
                "linkedListingID": []
              },
              "stage": "IXSCAN"
            }
          ],
          "stage": "OR"
        },
        "stage": "FETCH"
      },
      "stage": "SUBPLAN"
    }

Playground

user_89871280

By default, MongoDB creates a unique index on the _id field during the creation of a collection.

You query could be as simple as:

{_id: "..."}

MongoDB ensures that the indexed fields do not store duplicate values; for that reason you query on the _id will alway retrieve an unique document.

user_49801410
Related