CODEWITHSUNDEEP
mongodbperformanceindexing
Madbreaks
Madbreaks

Reputation: 19539

MongoDB Indexes and Views

I'm having trouble finding much information on MongoDB's use of indexes when querying views. The docs state:

Index Use and Sort Operations

Views use the indexes of the underlying collection.

As the indexes are on the underlying collection, you cannot create, drop or re-build indexes on the view directly nor get a list of indexes on the view.

You cannot specify a $natural sort on a view.

aaaand that's about it. I'm trying to figure out how to properly index my underlying collections (based on my experience with SQL DBs) to maximize performance of my app's queries on views.

For example, let's say I have a collection holding Product entities in a products collection and which share deleted and categoryId attributes. On top of that I might create a view productsActive defined as { deleted: false }, and as such on an index on the products collection: { deleted: 1 }. Now let's say that I commonly do the following query:

db.productsActive.find({categoryId: 'turntable'});

Intuitively I want to use a compound index here on the products table:

db.products.createIndex({deleted: 1, categoryId: 1});

Am I doing it right? I suppose ultimately I'm confused by the doc's statement:

Views use the indexes of the underlying collection.

Given that, can mongo use the compound index to initially build the view and then to subsequently query it? Or is simply deriving the view one step (tied to 1 index) and then the subsequent query on the view a second, distinct query with its own index(es)?

Upvotes: 1

Views: 5036

Answers (1)

mgmonteleone
mgmonteleone

Reputation: 410

Yes, creating a compound index as described would do the trick. (By the way, you should probably use the background param when building the index.)

Given that, can mongo use the compound index to initially build the view and then to subsequently query it?

There is really no "building of the view" here, views are created on demand. The index exists on the collection and is used by the view and other queries which match the index definition.

Hope this helps.

Upvotes: 3

Related Questions