4.1 Optimizing your CRUD operations. Why no memory sort?


It is not entirely clear why on the third column there is memory sort, and on the last one there isn’t.

Can someone please clarify?

1 Like

This is using the Equality, Sort, Range rule.

I don’t think I explained that well.

The 3rd query is using the index for the cuisine and zipcode query predicates, however, it can’t use the index on the sort because zipcode is not an equality condition.

The 4th query is using the Equality, Sort, Range rule:

  • Equality: cuisine = "sushi"
  • Sort: according to the index, stars is the next field after cuisine so it can utilise the index on sorting
  • Range: zipcode is a range query predicate

Hi! Thank you for your answer! Can I ask you another thing to clarify?

From previous chapters I understood that compound indexes when filtering & sorting must be of the following order: (F, …, F, S,…,S), where F is Filter and S is Sorting. But the best query from 4.1 turns out to be (F, S, F)

Does this mean that indexes are not necessarily to be of order of (F, …, F, S,…,S) format? For example if my query implies sorting and range, canthe index theoretically be (S, F). I understand that may be this is not the best example, but I am trying to understand all the rules of index order and filtering & sorting fields

Hi @cepbuch,

Just want to confirm that you’re talking about the same lab 4.1?

These are indexes, not queries. Perhaps you can clarify (without mentioning the answer :slight_smile: )

Oh, you are right! Of course index! I wanted to say that the most efficient index from the lesson (not from the lab) uses fields not by the rule “Firstly all fields from the find, and then all fields from the sort”, but they are mixed as “Find (cuisine), Sort (stars), Find (zipcode)”.

The order of the fields in a compound index is one the most important things. This “FSF” that you’re seeing is handled internally by the query optimiser. So as long as you know the ESR rule, you will know how to:

  1. Create an optimal index with the correct order
  2. Write queries that utilise the index optimally
1 Like