Where Execution Happens?

SELECT * FROM sakila.film_actor WHERE film_id = 1                                                                         

1. Apply the conditions to the index lookup operation to eliminate non-matching rows. This happens at the storage engine layer.

2. Use a covering index (“Using index” in the Extra column) to avoid row accesses, and filter out non-matching rows after retrieving each result from the index. This happens at the server layer, but it doesn’t require reading rows from the table.

3. Retrieve rows from the table, then filter non-matching rows (“Using where” in theExtra column). This happens at the server layer and requires the server to read rows from the table before it can filter them.

Query Execution Steps

1. The client sends the SQL statement to the server.

2 .The server checks the query cache. If there’s a hit, it returns the stored result from the cache; otherwise, it passes the SQL statement to the next step.

3. The server parses, preprocesses, and optimizes the SQL into a query execution plan.

4. The query execution engine executes the plan by making calls to the storage engine API.

5. The server sends the result to the client.