Index prefix question

Hi

I’m just preparing for the exam and have a question about index prefixes.

I have the compound index last_name, first_name, address.city But the filter I’ve used I wouldn’t expect the index to be used as the optimizer can’t create a partial index here? Unless it just uses last_name. But I did check that by removing the address city and it looked at a larger number of index keys. So this suggests to me that it is using the index as a compound one, and I’m confused by this, as I thought it wouldn’t be able to?

Would someone be able to explain?

It used the index on last_name and didn’t on city because it wasn’t the consecutive index prefix

Ok, I thought this :slightly_smiling_face: thanks.
Is there a way to see in the query plan which key of the compound was used? I looked but couldn’t see it

Within the executionStats section, the indexBounds should give you an indication.

As a suggestion for prepping for the exam, I’d suggest reading the explain plan from the Mongo Shell and running it in the allPlansExecution mode. What you see on Compass is not full. You’d need to know about all 3 modes, and have some understanding of how to read it. You may get asked about specific fields in the plan.

1 Like

Just coming back to this, the executionStats to me suggested that both were used in the index.

"indexBounds" : {
    "last_name" : [
        "[\"Smith\", \"Smith\"]"
],
"first_name" : [
	"[MinKey, MaxKey]"
],
"address.city" : [
	"[\"Ellenview\", \"Ellenview\"]"
]
},
1 Like

Ah yes! That’s because city is part of the compound index but it’s not the consecutive index prefix after last_name, as a result it’s using the index very inefficiently on city. You’d typically want a fairly even distribution between the nReturned, totalKeysExamined, and totalDocsExamined fields:
image
… and in this case it’s examined more keys than was necessary. So, if city was replaced with say “state”, then it definitely wouldn’t use the index on state.

By the way, a question around these three Explain Plan fields may come up in the exam.

Yes, I’d imagine something similar. I expected that the index was used just on last_name. So looking at the output, confused me. So are we saying that it is used on last_name as the index prefix, however it is also able to leverage the address.city too, but that is in addition and not part of the prefix. I’m assuming this as you can’t make an index prefix from this combination? Apologies for the replies, this threw me a little

We are saying that:

  1. The index was used on last_name because it’s the beginning of the index prefix
  2. The index was poorly/inefficiently used on address.city:
    • “Used” because it’s part of the same index as `last_name.
    • “Poorly” because it’s not the next index prefix after last_namefirst_name is.
    • And “poorly” because it’s most likely not sorted alphabetically in the index and as a result needing to examine more index keys than usual.

I’ll try and find you the documentation re point 2.

1 Like

@Jonathon_93506, I’d almost forgotten because you didn’t send a chaser. 4th paragraph in this documentation on index prefixes.

1 Like

Thanks @007_jb ver much. What a subtle distinction, this isn’t overly clear apart from that sentence. Though I certainly am pleased that I feel this is now resolved. I’ll keep going through lessons/exams and the console playing around.

Thanks again :smile:

Very little emphasis indeed. Enjoy!

1 Like