Skip to content
BENIBIX Learn

Query Plan

Development🗃️ DatabasesRelational Databases

Also known as Execution Plan, EXPLAIN, Query Execution Plan

The steps a database picks to answer a query. EXPLAIN shows them.

Think of it like

Think of it like the route a sat nav picks before you drive. You can look at it and see if it is a good one.

Example

Jack's search takes 3 seconds. EXPLAIN shows a scan of all 2 million rows, so he adds an index and the plan switches to a search.

Why it matters

You cannot fix a slow query by guessing. The plan shows where the time goes.

Definition

A query plan is the set of steps a database picks to answer a query. It says which tables it reads first, whether it uses an index and how it joins them. You see it by putting EXPLAIN in front of the query. A plan that scans a whole big table is often the cause of a slow page. Reading plans is the first step in making a slow query fast.

Example code

sql
EXPLAIN QUERY PLAN SELECT * FROM customers WHERE city = 'London';
-- SCAN customers
In SQLite, SCAN means it reads every row to find the matches.

Common mistake

Testing the plan on a tiny copy of the data. With 50 rows a scan is fine; with 5 million it is not.

Key takeaways

  1. 1
    The chosen steps. How the database will answer.
  2. 2
    EXPLAIN shows it. Put it before the query.
  3. 3
    Look for scans. Full scans of big tables are slow.