0

I am looking to create an index on MongoDB, am relatively new to this type of DBMS, have looked in the MongoDB log and noticed that its doing a COLLSCAN which from reading online can indicate that an index would be more efficient.

This is the query:

{ find: "PaitemCode", filter: { trackRef: /^aut$/i, time: "1250", item.9.silk: /20210930aut1250/ }, limit: 1 }

We have the fields trackref, time, item but what would be the best way of putting an index in that would be efficient to this query?

{
    "trackRef" : 1,
    "time" : 1,
    "item" : 1
}

Any help is much appreciated. Not sure of the implication of adding indexes in. I am using Robo3T to view the MongoDB DB via a GUI.

MongoDB Version is v3.4.24

Improve this question
4
  • You can create compound index (index with multiple fields), which can be used for a fast search (as in your query). See MongoDB Compound Indexes. You can verify the index usage by generating a query plan (use the explain method).
    – prasad_
    Oct 2, 2021 at 6:11
  • @prasad_ would this compound index cover values within those fileds or would it be specific to the values in the fields in the above query?
    – rdbmsNoob
    Oct 4, 2021 at 15:28
  • @rdbmsNoob did you try to create any similar index on your collection db.collection.createIndex( { "trackRef" : 1, "time" : 1, "item": 1 } ) and share the explain plan
    – Jerry
    Oct 18, 2021 at 5:42
  • Hi @Jerry I did try to add the index in however got errors to say the index itself was too large, also speaking with the development team there was a pre-existing bug which was causing these queries and did not need to implement anything at the moment.
    – rdbmsNoob
    Oct 18, 2021 at 9:24

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.