Indexing Flashcards
What is an index and what is its purpose?
An index is a B-tree of sorted field values that point to documents in the collection. Each node stores a field value that points to the actual document in the collection. Its purpose is to improve query performance
True or False
Without indexes, MongoDB needs to read all documents in a collection when data is queried
True
True or False
When indexes are used, MongoDB FETCHs the appropriate documents from the collection instead of performing an COLLSCAN
True
By default, what index is created when a collection is created?
Can it be deleted?
An _id index is created
No
What are the pros and cons of indexes?
Pros:
* Faster reads since the entire collection need not be traversed
* Faster reads since its values are sorted O(logn)
Cons:
* Slower writes since the values must also be added to the index (1 write for the actual document and 1 write for the index)
What are the 4 different index types?
- Single-field index
- Single-field multikey index
- Compound index
- Compound multikey index
What is a single-field index?
A single-field index is an index with one field
What is a single-field multikey index?
A single-field multikey index is an index with one field that is an array
What is a compound index?
A compound index is an index with multiple fields
What is a compound multikey index?
A compound multikey index is an index with multiple fields and one of those fields is an array
True or False
When using a multikey index, each element in the array gets an index key
True
Code an example of creating a single-field index where the field is unique in mongosh
db.collection.createIndex({field: 1 or -1}, {unique: true})Code an example of creating a single-field multikey index in mongosh
db.collection.createIndex({fieldArr: 1 or -1})Code an example of getting the indexes in a collection
db.collection.getIndexes()
Code an example analyzing whether an index is being used for a read operation
db.collection.explain().find(...)
Describe the differences between COLLSCAN, FETCH, and IXSCAN
- COLLSCAN implies a full collection scan (no indexes are used)
- FETCH implies retrieving documents (not a full collection scan)
- IXSCAN implies scanning index keys
True or False
Multikey indexes can have multiple field arrays
False. Multikey indexes can only have 1 field array
What are the queries that can be supported by the following compound index:
db.collection.createIndex({item: 1, location: 1, stock: 1})
find({item: value})
find({item: value, location: value})
find({item: value, location: value, stock: value})When creating indexes, what field order does MongoDB recommend?
- Equality fields
- Sort fields
- Range fields
What is “covering a query by an index”?
An index covers a query when MongoDB does not need to fetch the data from memory since all the required data is already returned by the index. In most cases, we can use projections to return only the required fields and cover the query. Make sure those fields in the projection are in the index
What is an index key?
An index key is the field names and their respective sorted values when creating an index. For example:
db.collection.createIndex({field1: 1, field2: 1})
Index key = {field1: 1, field2:1}
Code an example of hiding an index
db.collection.hideIndex("indexName" or indexKey)What is the purpose of hiding an index?
The purpose of hiding an index is to analyze how an index affects the performance of a query
True or False
When indexes are hidden, they will not be used for queries and they will not be updated for write operations
False. While hidden indexes are not used for queries, they are still updated for write operations
Code an example of deleting an index in mongosh
db.collection.dropIndex("indexName" or indexKey)Code an example of deleting multiple indexes
db.collection.dropIndexes(["indexName1", "indexName2"])
True or false
An index
covers
a query when all of the following apply:
all the fields in the query are part of an index, and
all the fields returned in the results are in the same index.
True
True or False
An index can cover a query on fields within embedded documents.
True. For example:
{ _id: 1, user: { login: "tester" } }
// index = { "user.login": 1 }
db.userdata.find( { "user.login": "tester" }, { "user.login": 1, _id: 0 } ) // covered
True or False
Because MongoDB can read indexes in both ascending and descending order, the direction of a single-key index does not matter
True
Can index names be changed after creation?
No. If you wish to change the name of an index, drop it and create a new one with the new name
Code an example of naming an index
db.collection.createIndex({field: value}, {name: "indexName"})Code an example of creating multiple indexes
db.collection.createIndexes([{field: value}, {field: value}])Create an index for the location subdocument in the following document:
{
"name": "Bob",
"gpa": 3.2,
"location": { city: "Albany", state: "New York" }
}
So that the following query uses the index:
db.students.find( { location: { city: "Albany", state: "New York" } } )
db.students.createIndex(location: 1)
True or False
When you create an index on an embedded document, only queries that specify the entire embedded document use the index. Queries on a specific field within the document do not use the index
True
Create an index for the field in the embedded document:
{
"location": { state: "New York" }
}
db.collection.createIndex({"location.state": 1})Code an example of the general structure of a compound index
db.collection.createIndex({field: value, field2: value2})How many fields can a compound index contain?
32
What is the purpose of a multikey index?
The purpose of a multikey index is to improve the query performance on field arrays
True or False
MongoDB can create multikey indexes over arrays that hold both scalar values (for example, strings and numbers) and embedded documents
True
True or False
After you create the compound multikey index, if you attempt to insert a document where both fields are arrays, the insert fails
True
True or False
Multikey idexes cannot cover queries on array fields
True
True or False
When you create an index on a field of a document inside an array, MongoDB stores that index as a multikey index
True
Code an example of unhiding an index
db.collection.unhideIndex("index name" or key)True or False
If you use the unique constraint on a compound index, then MongoDB will enforce uniqueness on the combination of the index key values
True
True or False
MongoDB cannot do an index sort on the results of a range filter
True
The following queries pertain to which part of the ESR rule?
db.cars.find( { price: { $gte: 15000} } )
db.cars.find( { age: { $lt: 10 } } )
db.cars.find( { priorAccidents: { $ne: null } } )
R = range
Write an optimal compound index for the following query:
db.cars.find(
{
manufacturer: 'Ford',
cost: { $gt: 15000 }
} ).sort( { model: 1 } )
db.cars.createIndex({manufacturer: 1, model: 1, cost: 1})True or False
For the fastest processing, ensure that your indexes fit entirely in RAM so that the system can avoid reading the index from disk
True
