CODEWITHSUNDEEP
mongodbindexingmongodb-querynosql
Amanda
Amanda

Reputation: 2143

For an array of objects, should I be creating a multikey index?

For a document that resembles the following,

{
     "translations": [
             {
                 "source": "hello",
                 "lang": "en",
                 "target": "some target"
             },
             {
                "source": "hey",
                "lang": "en",
                "target": "target string"
             }
     ]
}

should I create a multikey index or a compound index? What I want is when the query happens for this collection on source or lang or target , it must return the results quickly.

Upvotes: 0

Views: 306

Answers (2)

fadil eldin
fadil eldin

Reputation: 143

Any answer you will get, will not be accurate because you need to provide a lot more information about your use case. such as:
How many documents you have?
How many array elements, in average, would be in each document?
Is your data static, read-only. or there are updates and deletes?
What are the most frequest queries you expect on the collection?
Note that your indexe/s on "source" and/or "target" must use the same "collation".
Queries that ensure selectivity: While "source" and "target" have high carinality, but "lang", in comparison, would naturally have a lower cardinality (fewer unique values). Test how your queries will benifit from indexing "lang" standlone vs compound with source or target.
Make sure the size of your indexs "db.collection.totalIndexSize()" fit entirely in the RAM in order to avoid disk reads.

If you have little information about the application, you can evelaute (explain, indexstat) the performance with or without (use hint if needed, to force use of a partiular index) various combinations of sigle-key or compound indexes.

Upvotes: 0

Gibbs
Gibbs

Reputation: 22956

it must return the results quickly.

It depends on multiple factors. One is amount of data. Another is resources you have such as ram, shards, nodes.

As you need to query more than field at a time from the nested documents, you can go for compound index. But I suggest you to try it out the below things

  1. Multi key index - examine your use cases - confirm that mongo uses index intersection by explaining the query

  2. Compound key index - ensure that compound key index is most of the time used for ur use cases

It would be quick in both the cases. You need to consider writes as well. Each write result in index update.

Upvotes: 1

Related Questions