On 5:39 when we filter document using compound index, but not using the fields in the ‘right’ order, the lecturer says: “when we look at explain, you can see that even though we only had to look at ONE document, we still had to look at SIX keys” - why exactly does that happen? I heard him say this before in one of the previous lectures, but I didn’t hear (or have I misheard?) an explanation for that.
The query in question is on (job, employer, first_name).
The index in question is on (job, employer, last_name, first_name).
Therefore, it could only use index prefix containing job and employer.
A previous query in the video showed that there were 5 entries that matched job and employer, so the index prefix scan would return 5 keys. Since the index had the first_name field, (my guess) it used the first_name information in the 5 keys to find the one that satisfied the query. Lastly it used this one key to retrieve the single document, thus there are 5+1 index keys examined, but only 1 document examined and returned.
I hope this is the valid explanation. There is quite a bit to understand/guess how the query plan optimization works.