This is a little bit tricky. For query
select * from table order by B desc limit 100
what happens in our internal query execution is: (1) Since B is not matching the sort key, we have to do a full scan of the table. (2) Since your projection is “select *”, all columns of your table will be extracted. So internally in this case, we will have a priority queue storing the 100 rows with the max B values, and update the priority queue as we scan the whole table. Note that the 100 rows contains all the columns of the table and we also extract all columns when we scan, since your projection is “select *”.
select ... from t where B >= (select min(B) from t order by B desc limit 100) order by B desc limit 100
In this query, we execute the subselect first. In the subselect, only column B is used. The execution of the subselect is same as the previous case, except that now we only need to extract column B, as no other columns are involved, which would be a lot faster than extracting all columns.
After we get the threshold B value, only approximately 100 rows (assuming that there are not too many duplicates on the threshold B value) will pass the filter “B >= threshold_B”. We will extract the other columns of the ~100 rows that passed the filter using subsegment access, which is a very small cost compared with extracting the whole table.
In theory, for “select * from table order by B desc limit 100”, our optimizer could have reached a better execution plan by only extract column B to get the top 100 row IDs, and then fill in the other projection columns by subsegment access. But this is not what’s happening now (the primary reason is we don’t have the concept of rowID and subsegment-access before 7.0), so you would have to use @jack 's subselect workaround for now.