Indexing

Question 1

What is an index and what is its purpose?

Answer 1

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

Question 2

True or False

Without indexes, MongoDB needs to read all documents in a collection when data is queried

Answer 2

True

Question 3

True or False

When indexes are used, MongoDB FETCHs the appropriate documents from the collection instead of performing an COLLSCAN

Answer 3

True

Question 4

By default, what index is created when a collection is created?

Can it be deleted?

Answer 4

An _id index is created
No

Question 5

What are the pros and cons of indexes?

Answer 5

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)

Question 6

What are the 4 different index types?

Answer 6

1. Single-field index
2. Single-field multikey index
3. Compound index
4. Compound multikey index

Question 7

What is a single-field index?

Answer 7

A single-field index is an index with one field

Question 8

What is a single-field multikey index?

Answer 8

A single-field multikey index is an index with one field that is an array

Question 9

What is a compound index?

Answer 9

A compound index is an index with multiple fields

Question 10

What is a compound multikey index?

Answer 10

A compound multikey index is an index with multiple fields and one of those fields is an array

Question 11

True or False

When using a multikey index, each element in the array gets an index key

Answer 11

True

Question 12

Code an example of creating a single-field index where the field is unique in mongosh

Answer 12

db.collection.createIndex({field: 1 or -1}, {unique: true})

Question 13

Code an example of creating a single-field multikey index in mongosh

Answer 13

db.collection.createIndex({fieldArr: 1 or -1})

Question 14

Code an example of getting the indexes in a collection

Answer 14

db.collection.getIndexes()

Question 15

Code an example analyzing whether an index is being used for a read operation

Answer 15

db.collection.explain().find(...)

Question 16

Describe the differences between COLLSCAN, FETCH, and IXSCAN

Answer 16

• COLLSCAN implies a full collection scan (no indexes are used)
• FETCH implies retrieving documents (not a full collection scan)
• IXSCAN implies scanning index keys

Question 17

True or False

Multikey indexes can have multiple field arrays

Answer 17

False. Multikey indexes can only have 1 field array

Question 18

What are the queries that can be supported by the following compound index:

db.collection.createIndex({item: 1, location: 1, stock: 1})

Answer 18

find({item: value})
find({item: value, location: value})
find({item: value, location: value, stock: value})

Question 19

When creating indexes, what field order does MongoDB recommend?

Answer 19

1. Equality fields
2. Sort fields
3. Range fields

Question 20

What is “covering a query by an index”?

Answer 20

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

Question 21

What is an index key?

Answer 21

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}

Question 22

Code an example of hiding an index

Answer 22

db.collection.hideIndex("indexName" or indexKey)

Question 23

What is the purpose of hiding an index?

Answer 23

The purpose of hiding an index is to analyze how an index affects the performance of a query

Question 24

True or False

When indexes are hidden, they will not be used for queries and they will not be updated for write operations

Answer 24

False. While hidden indexes are not used for queries, they are still updated for write operations

Question 25

Code an example of deleting an index in mongosh

Answer 25

``` db.collection.dropIndex("indexName" or indexKey) ```

Question 26

Code an example of deleting multiple indexes

Answer 26

``` db.collection.dropIndexes(["indexName1", "indexName2"]) ```

Question 27

# 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.

Answer 27

True

Question 28

# True or False An index can cover a query on fields within embedded documents.

Answer 28

True. For example: ``` { _id: 1, user: { login: "tester" } } // index = { "user.login": 1 } db.userdata.find( { "user.login": "tester" }, { "user.login": 1, _id: 0 } ) // covered ```

Question 29

# True or False Because MongoDB can read indexes in both ascending and descending order, the direction of a single-key index does not matter

Answer 29

True

Question 30

Can index names be changed after creation?

Answer 30

No. If you wish to change the name of an index, drop it and create a new one with the new name

Question 31

Code an example of naming an index

Answer 31

``` db.collection.createIndex({field: value}, {name: "indexName"}) ```

Question 32

Code an example of creating multiple indexes

Answer 32

``` db.collection.createIndexes([{field: value}, {field: value}]) ```

Question 33

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" } } ) ```

Answer 33

``` db.students.createIndex(location: 1) ```

Question 34

# 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

Answer 34

True

Question 35

Create an index for the field in the embedded document: ``` { "location": { state: "New York" } } ```

Answer 35

``` db.collection.createIndex({"location.state": 1}) ```

Question 36

Code an example of the general structure of a compound index

Answer 36

``` db.collection.createIndex({field: value, field2: value2}) ```

Question 37

How many fields can a compound index contain?

Answer 37

32

Question 38

What is the purpose of a multikey index?

Answer 38

The purpose of a multikey index is to improve the query performance on field arrays

Question 39

# True or False MongoDB can create multikey indexes over arrays that hold both scalar values (for example, strings and numbers) and embedded documents

Answer 39

True

Question 40

# 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

Answer 40

True

Question 41

# True or False Multikey idexes cannot cover queries on array fields

Answer 41

True

Question 42

# 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

Answer 42

True

Question 43

Code an example of unhiding an index

Answer 43

``` db.collection.unhideIndex("index name" or key) ```

Question 44

# 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

Answer 44

True

Question 45

# True or False MongoDB cannot do an index sort on the results of a range filter

Answer 45

True

Question 46

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 } } ) ```

Answer 46

R = range

Question 47

Write an optimal compound index for the following query: ``` db.cars.find( { manufacturer: 'Ford', cost: { $gt: 15000 } } ).sort( { model: 1 } ) ```

Answer 47

``` db.cars.createIndex({manufacturer: 1, model: 1, cost: 1}) ```

Question 48

# 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

Answer 48

True