Which MongoDB indexes should be created for different sorting and filtering conditions to improve performance?

Question

I have MongoDB collection with ~100,000,000 records.

On the website, users search for these records with "Refinement search" functionality, where they can filter by multiple criteria:

• by country, state, region;
• by price range;
• by industry;

Also, they can review search results sorted:

• by title (asc/desc),
• by price (asc/desc),
• by bestMatch field.

I need to create indexes to avoid full scan for any of combination above (because users use most of the combinations). Following Equality-Sort-Range rule for creating indexes, I have to create a lot of indexes:

All filter combination × All sortings × All range filters, like the following:

country_title
state_title
region_title
title_price
industry_title
country_title_price
country_industry_title
state_industry_title
...
country_price
state_price
region_price
...
country_bestMatch
state_bestMatch
region_bestMatch
...

In reality, I have more criteria (including equality & range), and more sortings. For example, I have multiple price fields and users can sort by any of that prices, so I have to create all filtering indexes for each price field in case if the user will sort by that price.

We use MongoDB 4.0.9, only one server yet.

Until I had sorting, it was easier, at least I could have one compound index like country_state_region and always include country & state in the query when one searches for a region. But with sorting field at the end, I cannot do it anymore - I have to create all different indexes even for location (country/state/region) with all sorting combinations.

Also, not all products have a price, so I cannot just sort by price field. Instead, I have to create two indexes: {hasPrice: -1, price: 1}, and {hasPrice: -1, price: -1} (here, hasPrice is -1, to have records with hasPrice=true always first, no matter price sort direction).

Currently, I use the NodeJS code to generate indexes similar to the following (that's simplified example):

for (const filterFields of getAllCombinationsOf(['country', 'state', 'region', 'industry', 'price'])) {
    for (const sortingField of ['name', 'price', 'bestMatch']) {
        const index = {
            ...(_.fromPairs(filterFields.map(x => [x, 1]))),
            [sortingField]: 1
        };
        await collection.ensureIndex(index);
    }
}

So, the code above generates more than 90 indexes. And in my real task, this number is even more.

Is it possible somehow to decrease the number of indexes without reducing the query performance?

Thanks!

Answer 1

Firstly, in MongoDB (Refer: https://docs.mongodb.com/manual/reference/limits/), a single collection can have no more than 64 indexes. Also, you should never create 64 indexes unless there will be no writes or very minimal.

Is it possible somehow to decrease the number of indexes without reducing the query performance? Without sacrificing either of functionality and query performance, you can't.

Few things you can do: (assuming you are using pagination to show results)

1. Create a separate (not compound) index on each column and let MongoDB execution planner choose index based on meta-information (cardinality, number, etc) it has. Of course, there will be a performance hit.

2. Based on your judgment and some analytics create compound indexes only for combinations which will be used most frequently.

3. Most important - While creating compound indexes you can let off sort column. Say you are filtering based on industry and sorting based on price. If you have a compound index (industry, price) then everything will work fine. But if you have index only on the industry (assuming paginated results), then for first few pages query will be quite fast, but will keep degrading as you move on to next pages. Generally, users don't navigate after 5-6 pages. Also, you have to keep in mind for larger skip values, the query will start to fail because of the 32mb memory limit for sorting. This can be overcome with aggregation (instead of the query) with allowDiskUse enable.

4. Check for keyset pagination (also called seek method) if that can be used in your use-case.