Efficient indexing for filtering with multiple fields

Working with Data
1

Hi,

I have a collection with more than 2 million documents with multiple fields, sample document:

{
	"_id" : ObjectId("5ec2dbe1ad29e4000c272d16"),
	"Name" : "aaa",
	"Age" : "56",
	"Status" : "Draft",
	"Direction" : "forward,
     <Other Fields>
}

What I want to achieve is filtering out with a number of fields, the problem is the filtering fields can be in any combination or any number of fields, eg, it can be either of:

{name:"aaa", Age:56, Direction:"Forward"}
{name:"aaa", Status:"Draft", Direction:"Forward"}
{Age:56, Status:"Draft"}

and any other combinations

Since I do not have compound indexes for every combination, querying is extremely slow

How do I create compound indexes to make this querying fast?

1 Like
2

I’m facing the same scenario. Did you get the answer for this question

3

Currently i was achiving this by multiple compound index.
Consider, I’ve five fields(a, b, c, d, e). All the fields used for search filter. So, I will create compound index like below.

  1. create_index(a: 1, b:1, c:1, d:1, e:1) - Compound index
  2. create_index(b:1, c:1, d:1, e:1) - Compound index
  3. create_index(c:1, d:1, e:1) - Compound index
  4. create_index(d:1, e:1) - Compound index
  5. create_index(e:1) - Single index

Mongo will choose the compound index by first key of index. If your search will be like this (a, d, e). It will select (1) index for quering.

If anyone know, better solution. Please reply here.

4

i have got a simple collection with 50 fields and more than 1M docs.and we run queries with any filter combinations on 10 fields and then we sort it with on one field.

the question is, if i want to filter on a,b,c,d,e,f,g,h,i,j and then sort on k field, how many compound indexes should i create, since more than 60 indexes are not allowed as i know.

i know that i should follow the rule of “Equality - Sort - Range” in ordering the fields of an index

and i know if i have an index like {a,b,c,d,e} , it means that i have prefix indexes as well like {a} , {a,b} , {a,b,c} , {a,b,c,d} , {a,b,c,d}

correct me if i’m wrong, but i think if we have an index like {a,b,c,d,e} and if our query is based on a,c,e fields, it means that it will only use this index as {a} index, because we have not provided values for b and d in the query?

now, how should i create my indexes and how many should i create?

as @Yasir_Asarudheen suggests, we can create compound indexes starting with each of fields.but what about the combinations of each?for example for {b,c,d,e} index what if we neede {b,d} or {b,e} ?

1 Like
5

hi @Masoud_Naghizade did you find the answer? I’m facing the same issue