select *
from table
where A >= 'A' and A <= 'K'
and B >= "C" and B <= 'P'
and C >= 'H' and C <= 'Z'
order by B,D,C
will be performed thus:
If no useful index:
1. scan the entire table
2. write the rows that match the WHERE clause to a "temporary table"
3. sort that temp table by B,D,C
4. Deliver the results.
If you had a composite INDEX(B,D,C), with the columns in _that_ order, then, the optimizer would _probably_ do
1. scan the entire index
2. deliver rows that match the WHERE clause.
If you had INDEX(A,B,C), in _any_ order, then (probably):
1. Reach into the index at the point where A='A' and B='C' and C='H'
2. Scan forward until it hits K+P+Z
3. for each row (in the index), reach into the data for all the columns ("*")
4. deliver each such row.
If you had index(es) on some subset of A,B,C, then other things could be happening.
Etc.
In your particular example, and if you had both (B,D,C) and (A,B,C), the optimizer would probably pick one, but it would not have enough information to _necessarily_ pick the better one.
If you have a hundred rows, that query will be fast regardless of what indexes you have. If you have a million rows, indexing is very important.
If most rows match ( A >= 'A' and A <= 'K' ), then some of the above algorithms are better than others. If very few match, the different algorithms are better.
See my cookbook:
http://mysql.rjweb.org/doc.php/index_cookbook_mysql
Sorry for the TMI.
For MyISAM, reads and writes block each other. For InnoDB, a select should proceed regardless of writes.