How does MySQL skip ranges (nodes) in a loose index scan?
I think the Optimizer works something like this:


It can find the first row:  MIN(name) WHERE foo=123
It can find the last row:  MAX(name) WHERE foo=123


Those are done via a drill-down in the BTree, and assumed to be reasonably cheap.  If there are a lot of rows for foo=123, then this is likely to skip over some blocks.  Note: fetching blocks potentially means a disk hit, and disk hits are the most costly primitive operation.  Note also that much of the Optimizer was designed in the days of small RAM and before SSDs.

Once it gets to the first (or last) row, then the B+Tree shines in that it can walk forward (or backward) efficiently.  (But this is not relevant to your Question.)

If, as you imply in your question, there are not many rows where foo=123, then there is not much savings, perhaps no savings.

(What I don't know here is whether and when the Optimizer decides that "loose scan" is likely to be inefficient.  I do know that the Optimizer will deliberately choose to do a table scan instead of using an INDEX when it estimates that the back and forth between the index BTree and the data BTree is too costly.)

A Rule of Thumb:  There are 100 items in each node of the BTree.  (This of course really varies between 1 and several hundred, but 100 is a handy number for estimating things.)  So, a 3-level BTree like you diagrammed may be holding 1 million items and have 10,000 leaf nodes.  Now, depending on the specifics of the schema and the query, a table scan would cost 10,000 disk hits versus only a few disk hits for a loose scan.  (Yeah, the table is likely to be completely cached in the buffer_pool; I do know that the Optimizer does not yet take that into account in its 'cost model'.)

See also: https://dev.mysql.com/doc/refman/8.0/en/cost-model.html