SQL is declarative. You say what rows you want, never how to find them. Somewhere between your query text and the rows coming back, the database chooses a method: which index, which join order, which algorithm. That choice is made by the query planner, and it's the reason the same query can take 2 milliseconds one day and 4 seconds the next.
In Module 2 you added an index and watched a query get fast. This lesson opens the box. You'll see how the planner decides, how to read its decision with EXPLAIN, and the usual reasons it ignores an index you were sure it would use. Examples are Postgres, with MySQL notes where it differs.
From text to rows
Every query goes through four steps:
For a simple query there are a few possible plans. For a five-table join there can be thousands: every join order, times every way to read each table, times every join algorithm. The planner can't run them all to see which is fastest. It estimates the cost of each and picks the cheapest guess.