I agree with all the responses with good information as to the theory of what happens. However, a good starting point from a practical standpoint is to look at the query plans of the queries you run. Sql server allows you to do this from SQL Server management Studio (SSMS), and most other databases have an EXPLAIN keyword.
This will show you what is going on with your queries, and what parts of the query are slowest.
Combining this level of knowledge (e.g. a index seek is faster than an index scan) with the low level theory (e.g. how a b-tree works) allows you to truly understand whats happening (A seek goes through the tree, and a scan reads the linked list of leaves)