MongoDB Indexes to Optimize Dynamic Query

Working with Data
,
1

Hello Folks,

I’m just getting started with MongoDB and I am currently exploring the concept of indexes.

I’m working on a music application, where users can search songs based on different tags (such as genre, mood, artist, bpm, etc.), totaling around ten. What makes it a bit more complex is that the users have the flexibility to choose the different search filters / fields dynamically.

To illustrate, here are two examples queries a user may request:

Example 1

db.songs.find( { genres: { $in: [rock, pop] } }, { moods: happy } )

Example 2

db.songs.find( { voice: female }, { moods: { $in: [happy, sad] } }, { bpm: { $gt: 90 } } )

I’d like to optimize these queries using indexes, but given the dynamic nature of the queries, I am unsure about the best approach to create the necessary index or indexes.

Is it a viable strategy to create a separate index for each searchable field?

Alternatively, should I create a single index including all the searchable fields? I fear this may be problematic because I have several fields that are arrays (for instance, a song can be tagged with multiple moods / genres). As far as I know, MongoDB can accommodate only one multikey (array) index. Therefore, a single index featuring all the fields wouldn’t be viable from my understanding.

What would you recommend as a solution for my scenario?

I appreciate any help and guidance!

Thanks a lot.

Best,
Valerio

2

Its hard to give a best answer here.

But you can take a look at this mysql - Create sql indexes for complex filtering - Stack Overflow

It has some good ideas on what are needed and what are not. Principle is only create indexes when necessary

Anyways the correct solution depends on your application needs.

3

Looks like the Attribute Pattern.

4

Many thanks @Kobe_W :slight_smile: The resource you shared is really helpful.

5

Didn’t know about the Attribute Pattern. Thank you for sharing it @steevej !