Lab 2.2

In this lab you're going to examine several example queries and determine which compound index will best service them.
> db.people.find({
    "address.state": "Nebraska",
    "last_name": /^G/,
    "job": "Police officer"
  })

> db.people.find({
    "job": /^P/,
    "first_name": /^C/,
    "address.state": "Indiana"
  }).sort({ "last_name": 1 })

> db.people.find({
    "address.state": "Connecticut",
    "birthday": {
      "$gte": ISODate("2010-01-01T00:00:00.000Z"),
      "$lt": ISODate("2011-01-01T00:00:00.000Z")
    }
  })
If you had to build one index on the people collection, which of the following indexes would best sevice all 3 queries?

Choose the best answer.

Answer:
  • { "address.state": 1, "job": 1 } - No - As this can able to serve all 3 of the example queries, however a better index that can be used on the first query, and the second query has to do an in-memory sort.
  • { "address.state": 1, "job": 1, "first_name": 1 } - No - this index is better than the first, but doesn't help with the sort on the 2nd query.
  • { "address.state": 1, "last_name": 1, "job": 1 } - Yes - This is the best one. It index matches the first query, can be used for sorting on the second, and has an prefix for the 3rd query.
  • { "job": 1, "address.state": 1 } - No - It can only be used by the first two queries.
  • { "job": 1, "address.state": 1, "first_name": 1 } - No - It is better than the previous one, but cannot be used by the 3rd query at all.
  • { "job": 1, "address.state": 1, "last_name": 1 } - No - this index has the same issues as the index directly above it.


Comments

Post a Comment

Popular posts from this blog

M201 MongoDB Performance - Labs & Final Exam Q&A

M201 MongoDB Performance Chapter-1 Introduction to MongoDB Performance Lab 1.1: Install Course Dependencies Lab 1.2: Install MongoDB Compass Chapter – 2 MongoDB Indexes Lab 2.1: Using Indexes to Sort Lab 2.2: Optimizing Compound Indexes Chapter – 3 Index Operations Lab 3.1: Explain Output Chapter 4: CURD Optimization Lab 4.1 – Equality, Sort, Range Chapter 5: Performance on Clusters Final Exam Question 1 Question 2 Question 3 Question 4 Question 5 Question 6 Question 7
Read more

Question 1

Image
Which of these statements is/are true? Answer: You can index multiple array fields in a single document with a single compound index - False   Multikey indexes allow us to index on array fields, but they do not support indexes on multiple array fields on single documents. Covered queries can sometimes still require some of your documents to be examined - False A query is covered if and only if it can be satisfied using the keys of the index. Write concern has no impact on write latency - False Different write concerns can certainly impact your write latency. Write concerns that only need acknowledgment from a primary are generally faster than ones that need acknowledgment from a majority of replica set members. Creating an ascending index on a monotonically increasing value creates index keys on the right-hand side of the index tree - True A collection scan has a logarithmic search time - False No, collection scans have a  linear  search time.
Read more

Question 7

Image
Given the following indexes: { categories: 1, price: 1 } { in_stock: 1, price: 1, name: 1 } The following documents: { price: 2.99, name: "Soap", in_stock: true, categories: ['Beauty', 'Personal Care'] } { price: 7.99, name: "Knife", in_stock: false, categories: ['Outdoors'] } And the following queries: db.products.find({ in_stock: true, price: { $gt: 1, $lt: 5 }  }).sort({  name: 1 }) db.products.find({ in_stock: true }) db.products.find({ categories: 'Beauty'  }).sort({  price: 1 }) Whic h of  following is/are true? Answer: Index #1 would provide a sort to query #3. - True Index #2 properly uses the equality, sort, range rule for query #1. - False - If we were to build an index for query #1 using the equality, sort, range rule, then the index would be: { in_stock: 1, name: 1, price: 1 }. There would be a total of 4 index keys created across all of these documents and indexes. - False - As there would be 5 to
Read more