Simplified translation for https://www.youtube.com/watch?v=bo5j9xgiF48&t=840s
NOTE: The whole presentation deserves translation.
From 14:00:
lets talk about "concatenate indices"; they're not on 1 column but on many columns
this kind of query can take advantage of that index
this is what the index look like. You have 2 columns instead of 1 column like in the previous examples. The 1st column is ordered by customer_id; the creation_date column is ordered by customer_id AND creation_date.
that means creation_date are not ordrered in the table.
The ordering is made 1st by customer_id THEN by 2nd column creation_date
15:17
if we try to exec the request, the tree traversal is like in previous examples. if first scan all the customer_id to find candidates then scan all creation_date.
its very effective and the same principle as single indices except we check values on 2 columns instead of a single one.
Another intersting thing with this index is that it also index the customer_id alone efficiently.
BUT it will NOT work if you query by creation_date. Thats because the creation_date are not ordered globally in the index.
17:20
You need to know the order in which you define your columns on your indexes is important because it impacts which queries can be indexed or not. keep that in mind.
If you have a 2-col index, you can query by (1st) column, (1st+2nd) columns but NOT (2nd).
If you have a 3-col index, you can query by (1st) column, (1st+2nd) columns, (1st+2nd+3rd) columns but NOT (2nd) or (3rd) or (2nd+3rd) columns. you have to respect the order of the index definition