0

I have a collection X on which I have to apply a filter.

The filter is saved as a sepparate entity (collection filters) and the only data it holds are the field name and the conditions applied to that field name

Example of filter:

Name is Stephan and Age BETWEEN 10, 20

Basically what I have to improve is the fact that each field in my filter is an index added upon creation of the filter.

The only structure that matches is a compound index on the fields filtered.

In conclusion, the problem is that when I have a filter like:

Name is Stephan and Age BETWEEN 10,20

My compound index in MongoDb will be: {'Name':1,'Age':1}

But then, if I add another filter, let's say: Age is 10 and Name is Adrian and Height BETWEEN 170,180

compound index is: {'Age':1,'Name':1, 'Height':1}

{'Name':1,'Age':1} <> {'Age':1,'Name':1, 'Height':1}

What can I do to make the last index fit with the first and the other way around.

Please let me know if I haven't been to explicit.

Community
  • 1
  • 1
Gabriel
  • 772
  • 1
  • 13
  • 37

1 Answers1

1

The cleanest solution to this problem is index intersections, which is currently in development. That way, an index for each of the criteria would be sufficient.

In the mean time, I see two options:

  1. Use a separate search database that returns the relevant ids based on your criteria, then use $in in MongoDB to query the actual documents. There are a number of tools that use this approach, but it adds quite a bit of overhead because you need to code against and administer a second db, keep the data in sync, etc.
  2. Use a smart mix of compound indexes and 'infinite range queries'. For instance, you can argue that a query for age in the range of (0, 200) won't discard anybody from the result set, neither will a height query between 0 and 400.

That might not be the cleanest approach, and its efficiency depends very much on the details of the queries, so that might require some fine-tuning.

mnemosyn
  • 45,391
  • 6
  • 76
  • 82
  • How would an index intersection help me? Sorry but I'm a bit blurred on the topic. – Gabriel Oct 01 '13 at 11:52
  • Index intersectioning is a feature that allows the db to combine multiple indexes, so to speak. So you don't need a compound index for every possible combination of indexes, but only one index per criterion, e.g. one for age, name and height. That's three indexes instead of six, or `n` indexes instead of `n!` indexes for the general case. – mnemosyn Oct 01 '13 at 12:19
  • Can you please tell me something more accurate for the 'infinite range queries' option? – Gabriel Oct 01 '13 at 13:26
  • I'm no longer sure if that makes sense because that would be range queries. The idea was: according to the docs[http://docs.mongodb.org/manual/core/index-compound/], Mongo doesn't support querying for A and C if the compound index is A,B,C, but you could include some bogus range of B that basically includes all values for B. Not sure if that's true - you might want to run some experiments with `explain`. Also there's a nice article about MongoDB indexes at http://emptysqua.re/blog/optimizing-mongodb-compound-indexes/ – mnemosyn Oct 01 '13 at 13:37