Great article by Alexander Rubin that goes through a brief intro of B-trees (the basis of many databases indexes) and gets into the details of understanding and optimizing MySQL queries:
In this article we will talk about query optimization with the focus on the
queries with GROUP BY and ORDER BY. We will start with the basic concepts
(Indexes, Explain plan in MySQL, etc) and then will talk about the advanced
query optimization techniques. We will cover “loose index scan” and “tight
index scan” optimizations and show the benchmark results.
Original title and link: Level up your MySQL query tuning