How to index when the fields used in the query is unpredictable?

Question

In collection A, each document contains up to 100 fields. The database, mongoDB, only allows up to 64 indexes on a single collection.

Indexes are needed for a query on this collection. The filter, or $match, stage of this query may be done using any combination of the 100 fields.

Even if 64 indexes are created, it will at most only be able to cover 64 fields.

Problem:

This means that if the query is filtered based on any of the 36 fields that are not indexed, a collection scan must be done to complete the query.

Question:

Are there alternative ways to optimize this query, preventing a collection scan in all possible combinations of this query?

Answer 1

I am repeating myself: Review your database design, it is really bad - just believe it!

Anyway, you say "Indexes are needed for a query on this collection." Yes, that's true but it does not mean you need an index on every field which appears (or may appear) in the $match stage.

Create indexes on fields which are most commonly used or most expected. If a single index value returns 100 documents (out of 10 million) then this is still very fast. MongoDB can scan these in a few milliseconds.

A field with low cardinality does not need any index. The query performance will not change if you put an index on such field or not. A typical low cardinality would be for example a gender field, it has only male and female (and maybe others). An index on such field is a waste of disk space, even if it is part of every query. An arbitrary combination of 100 fields gives a huge amount of possible conditions, you will never be able to cover all of them. Focus on the Top-5 only!

Answer 2

How to index when the fields used in the query is unpredictable?

How do you shop for groceries when the meal being cooked is unpredictable? - You don't. Or you spend a ton of money buying every grocery item proactively.

For a thin table (only a few columns), it may be possible to index every realistic combination of fields to be searched on. But most times for most tables it's not reasonably possible. And almost always it's not necessary.

Indexes are needed for a query on this collection. The filter, or $match, stage of this query may be done using any combination of the 100 fields.

This is an unusual request. Even large scale companies like Facebook (to relate an example to your other posts) don't search across so many fields at a time. When you enter a search term in their search box, it's only searching a few fixed fields such as FirstName, LastName, Description, Tag, etc. It's not searching things like Birthday, Age, Gender, etc, etc.

Even if 64 indexes are created, it will at most only be able to cover 64 fields.

Not exactly. A single index can cover multiple combinations of fields at a time. E.g. a single index on the fields (FirstName, LastName, Tag) would cover predicates on only FirstName or on FirstName and LastName or FirstName, LastName, and Tag. So depending on the realistic uses, one index can cover multiple use cases.

---

I know it sounds like you're getting a lot of redundant answers that don't seem to help you out on each of your recent questions, but that's because optimization for a particular situation is very fact specific. Unfortunately only this generalized information can be provided with the generic details you've provided so far.

If you wanted to provide the exact use cases you have, including what is the system you're working on, what are the objects involved, how are they structured, some example data, and how you're trying to search against them, then maybe a more specific set of optimization approaches can be provided, which will likely be oriented around design implementation.