Possible error on practice exam (Developer)

MongoDB University Certification Exams
,
1

The practice exam has the following question

Which of the following queries will require that you load every document into RAM in order to fulfill the query? Assume that no data is being written during the query. Check all that apply.

Indexes

[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "test.things"
	},
	{
		"v" : 1,
		"key" : {
			"a" : 1
		},
		"name" : "a_1",
		"ns" : "test.things"
	},
	{
		"v" : 1,
		"key" : {
			"c" : 1,
			"b" : 1,
			"a" : 1
		},
		"name" : "c_1_b_1_a_1",
		"ns" : "test.things"
	}
]
  • db.things.find( { b : 1 } ).sort( { c : 1, a : 1 } )
  • db.things.find( { c : 1 } ).sort( { a : 1, b : 1 } )
  • db.things.find( { a : 1 } ).sort( { b : 1, c : 1 } )

According to the exam the third is the correct answer but when running these queries on a local machine all of the following queries have to do an in memory sort.

2

you need 2 things to do your local test correctly:

  • make indexes as given; on “a” and on “c,b,a” as you see on the “key” and “name” properties.
  • insert some documents with “a/b/c” keys in them.

now, when you then use “explain” you will see counters of how many documents are used to find a result, along with how many are returned. if you have 10 out of 10 used that means all documents are used. if you see less than 10, that means the query used an index. if 0 documents are used, that means a whole index is used to return and no reads made other than returning final documents.

besides this, when you find yourself doubting, take a little break and stretch some muscles and relax your mind. a stressful day or moving too quickly between lectures of the course will lead miss important details; as in your case “load every document” statement.

Good Luck

3

I actually added the following index’s to my local before running the test just forgot to include that step in my post. I’ll take a little break and circle back. Thanks !

4

I figured it out they all do an in memory sort but only one does a collection scan

5

Doing a memory sort was not part of the question:

And indeed

but it cannot be

The third one should perform an IXSCAN using the index named a_1.

6

I have checked your question again and the order of your queries and your statement of “the third” seems not following each other.

Did you write them as they were given to you, or as part of the answer you see?

if you use one of the following ones:

db.things.find( ... ).sort( { ... } ).explain({verbosity: "queryPlanner"})
db.things.find( ... ).sort( { ... }).explain({verbosity: "executionStats"})

you will see 2 of them have executionStats.inputStage.stage to be IXSCAN along with executionStats.totalDocsExamined as 0 showing they use indexes. The other has COLLSCAN and the executionStats.totalDocsExamined is as many docs as you have to show no index usage and all documents loaded into memory.

the problem with your question is that the “third” item in the list, db.things.find( { a : 1 } ).sort( { b : 1, c : 1 } ), is one of those to have IXSCAN. that means either your list in the post is incorrect or the question in the exam is.

so please check your question carefully first, and check the question/answer from the exam if they both do match.

2 Likes
7

I will check now since I’m back at my computer I can check the results of my last exam.

8

I am being cryptic about the right answer so that we don’t want to ruin others’ experiences by giving a direct spoiler.

I have found the question we discussed so far, and found out the only mistake is your “third” statement being wrong in your first post. and from my explaining posts, you will see which of the other two is the correct answer and why.

also note that the answer was as easy as answering “does it use an index?” question, or comparing “how much may index X filter compared to filter Y” if needed. sorting part of the question is to add a little spice to test your knowledge.

Getting good takes time, so do not forget to have relaxation intervals to prevent attention loss to important details.

Good luck

2 Likes
9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.