0

I have a following situation:

  • I have a collection with documents like
{a: 1, b: 'some string', c: 5, d: 7 }
{a: 2, b: 'some another string', c: 5, d: 8 }
{a: 3, b: 'yet another string', c: 5, d: 9 }
  • I allow users to search using custom queries, so sometimes I can search for:
{a: 2, c:5}
{c:5, d:8}
  • Some of the used fields are mandatory (eg. c)
  • I thought about seting up indexes for c so queries would work faster, and when I do:

db.my_collection.find({c:5})

it works great, but when I launch:

db.my_collection.find({c:5,d:8})

it consumes the same ommount of time when withought an index :(

So my question is: Is it possible to set up some kind of partial index so queries would in first place search inside keys that have indexes, and than inside those withought them?

bsiamionau
  • 8,099
  • 4
  • 46
  • 73
Witek Adamus
  • 3
  • 1

1 Answers1

0

My original answer was inaccurate, I didn't read properly.

So my question is: Is it possible to set up some kind of partial index so queries would in first place search inside keys that have indexes, and than inside those withought them?

MongoDB will already do this but you might have a problem in the selectivity, format and size of d and the document as a whole.

Even when MongoDB has filtered c through the index it will need to ScanObjects the actual data, paging the document in.

The selectivity of d could be such that you are returning a lot of documents from the c clause and causing a huge scan for d.

So these factors together could be making your query slower.

The best way to slove this normally is to not use a partial index here and instead just make a full compound index.

Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • Hi I was inaccurate - the index in my example is only on key: c – Witek Adamus Feb 26 '13 at 12:22
  • @WitekAdamus Ah I think I initially misread actually, I just assumed something and didn't fully read it. I see now; hmm yes it could be true that an index on `c` and a query on `c` and `d` would be slow, it depends on what `d` is and the selectivity of `c`, a typical way of solving this is to use a compound index on them – Sammaye Feb 26 '13 at 12:26
  • @WitekAdamus Ok I added an answer that actually answers the question – Sammaye Feb 26 '13 at 12:47