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

  • 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


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.