0

I'm getting poor query performance from a Spring/Mongo application where I have a document that contains a list of subdocuments. I've tried adding compound indexes to help with specific queries, but they don't seem to help much when I'm searching inside the list of subdocuments. Is this just a bad idea in general (querying against subdocuments inside of lists), or is there something I could do to improve this? I tried upgrading from mmapv1 to wiredTiger storage engine, but found that wiredTiger was actually ~30% slower for the same query.

My understanding is that our number of index rows will end up being document rows * number of subdocuments in each list we try to index with the compound index. It seems suboptimal, but was wondering if there is some way we could optimize this and still have our subdocuments in a list.

Pseudo/simplified example of the Spring Mongo Document class:

@Document
@CompoundIndexes({
@CompoundIndex(name = "index1", def = "{ 'attr':1, 'things:attr1': 1, 'things:attr2': 1}", unique = false)
})
public class Foo {
  String attr
  List things
}

So, MongoDB document looks something like this:

{
attr: value,
things: [
    {
      attr1: value1,
      attr2: value2
    },
    {
      attr1: value1,
      attr2: value2
    }
  ]
}

Example of a query:

{ "$and" : [ {"attr": "value"}, { "things" : { "$elemMatch" : { "attr1" : "value2" , "attr2" : "value2"}}}]}

My next best solution is to put a field directly on the root of the document for each subdocument in the list, but this would entail a painful and costly refactor of our codebase, after which document would look something like this:

{
attr: value,
thing1:
    {
      attr1: value1,
      attr2: value2
    },
thing2:
    {
      attr1: value1,
      attr2: value2
    }
  ]
}
Neil Davis
  • 31
  • 4
  • "index1" should be enough for your query. Can you do a find().explain() to confirm that "index1" is used for your query? – anhlc Apr 24 '15 at 00:05
  • Right, the index is as good as we can do to the best of our knowledge, but the performance even with the index is not acceptable. – Neil Davis Apr 24 '15 at 20:18

0 Answers0