In addition to single field indexes, MongoDB gives us the ability to create compound indexes. Compound indexes contain references to multiple fields within a document and support queries that match on multiple fields. Let’s have a look at the syntax for creating a compound index:
db.<collection>.createIndex({ <field>: <type>, <field2>: <type>, … })
Similar to single field indexes, MongoDB will scan our index for matching values, then return the corresponding documents. With compound indexes, the order of fields is important. To understand why, let’s return to our example from the first exercise about the university president.
Imagine that as president we wanted to plan reunions not just for students who studied abroad during a particular year, but also for students who studied abroad in a particular country. We could create a compound index on two fields: study_abroad_nation
and year_abroad
, like so:
db.students.createIndex({ study_abroad_nation: 1, year_abroad: -1 });
This creates a single index that references two fields: study_abroad_nation
in ascending, or alphabetical order, and year_abroad
in descending, or reverse chronological order.
Because of how the fields are ordered, references within this index will be sorted first by the study_abroad_nation
field. Within each value of the study_abroad_nation
field, references will be sorted by the year_abroad
field. This is an important consideration in determining how well our indexes will be able to support sort operations in our queries.
Now that we’ve created this compound index, anytime we query on these two fields, MongoDB will automatically employ this index to support our search.
The below query would be a use case for our compound index:
db.students.find({ study_abroad_nation: "Brazil", year_abroad: 2012 });
Compound indexes can also support queries on any prefix, or a beginning subset of the indexed fields. For example, consider the following compound index:
db.students.createIndex({ study_abroad_nation: 1, year_abroad: -1, graduation_year: 1 });
In addition to supporting a query that matches on the study_abroad_nation
, year_abroad
and graduation_year
fields, this index would also be able to support queries on the following fields:
study_abroad_nation
study_abroad_nation
andyear_abroad
This index would not, however, be able to support queries on the following fields:
year_abroad
graduation_year
year_abroad
andgraduation_year
As each index must be updated as documents change, unnecessary indexes can affect the write speed to our database. Make sure to consider if a compound index would be more efficient than creating multiple distinct single-field indexes to support your queries.
Let’s practice creating and using compound indexes!
Instructions
We’ve realized that creating one compound index would be more efficient than two single field indexes. Using the same listingsAndReviews
collection, create an index based on the fields borough
and cuisine
so that the results would be in ascending order for borough
and descending order for cuisine
.
Using the new compound index, we want to query only Chinese restaurants with a restaurant_id
greater than "42000000"
. Create a query that satisfies these parameters.