If this tickles you fancy, you should check out this site http://use-the-index-luke.com or a book by the same author https://www.amazon.com/Performance-Explained-Everything-Deve...

He goes into great detail to explain how exactly SQL indices work and how to leverage this to write queries.