Help me creating indexes properly

Working with Data
, , , ,
1

I’m building a lead data website using the MERN stack. Users can filter data based on various criteria like country, city, job title, seniority, industries, department, email type, and more (over 10 filtering options in total). They can choose any field or a combination of fields to filter the data. I’ve set a limit of 25 records per request to keep the responses manageable.

Currently, I’ve indexed all fields (country, city, job title, etc.) individually, and when using a single filter, the data retrieval is fast and efficient. However, when users apply multiple filters, the performance drops significantly—either the data is not filtered correctly, or the results take a very long time to load.

I experimented with compound indexing, which improves performance for specific combinations of filters. However, since users can apply filters in any random combination, I’m struggling to determine the best approach to optimize query performance across all potential filter combinations.

My question is:

  • How can I optimize the filtering queries in MongoDB when users can apply any combination of filters?

  • Should I use compound indexes, or is there another approach to handle such flexible filtering more efficiently?

Any advice or best practices for indexing and optimizing query performance in this scenario would be greatly appreciated!

here is my code partially

 if (Array.isArray(filters.countries)) {
        addCondition(
          "_source.person_location_country",
          "$in",
          filters.countries
        );
      }

      if (Array.isArray(excludedFilters.countries)) {
        addExclusionCondition(
          "_source.person_location_country",
          "$nin",
          excludedFilters.countries
        );
      }

      // job title
      if (Array.isArray(filters.jobTitle)) {
        addCondition("_source.person_title", "$in", filters.jobTitle);
      }

      if (Array.isArray(excludedFilters.jobTitle)) {
        addExclusionCondition(
          "_source.person_title",
          "$nin",
          excludedFilters.jobTitle
        );
      }

      // seniority
      if (Array.isArray(filters.seniority)) {
        addCondition(
          "_source.person_seniority",
          "$in",
          filters.seniority.map((s) => s.toLowerCase())
        );
      }

      if (Array.isArray(excludedFilters.seniority)) {
        addExclusionCondition(
          "_source.person_seniority",
          "$nin",
          excludedFilters.seniority.map((s) => s.toLowerCase())
        );
      }

// Apply all conditions
      if (conditions.length > 0) {
        query.$and = conditions;
      }

      if (exclusionConditions.length > 0) {
        query.$and = [...(query.$and || []), ...exclusionConditions];
      }

results = await Contacts_V5.find({
          _id: { $in: savedItemsIds },
          ...query,
        })
          .skip((page - 1) * limit)
          .limit(limit)
          .exec();

res.status(200).json({
        results
      });
2

One solution worth exploring is the Attribute Pattern. It’s a flexible schema design pattern that works particularly well in scenarios where users need to query a dataset using arbitrary combinations of fields—exactly your usecase.

Shifting to the Attribute Pattern, you should see an improvement in both query performance and the ability to handle arbitrary combinations of filters.

2 Likes