02. How does the database create a query plan

 

How does the database create a query plan?

01. MySQL (and most RDBMS): Execution Plan Selection is Based on Cost Estimation

How it works:

1. The query is parsed and analyzed.
2. The query optimizer looks at all possible execution plans (e.g., index scan, full table scan, different join orders).
3. It does NOT run the plans — instead, it uses statistics (like row count, index selectivity, table size) to estimate the cost of each plan.
4. The lowest-cost plan is chosen and executed.

Example: SELECT * FROM orders WHERE customer_id = 123

MySQL checks:

• Is there an index on `customer_id`?
• How many rows match `customer_id = 123` (using index stats)?
• What is the estimated disk I/O and CPU cost?

It chooses the plan with the lowest estimated cost, not the proven fastest.

 

02. MongoDB: Execution Plan Selection is Based on Trial Execution

How it works:

1. MongoDB generates several candidate plans.
2. It actually runs each plan for a short time — like a "mini test drive" (Actually each plan is running for some documents).
3. It records: How many documents were read, How long it took, How efficient each plan was   
4. The plan that performs best in trial is used for the full query and cached for reuse. 

Example:  db.orders.find({ customer_id: 123 })

MongoDB considers:

• Collection scan
• Index scan on `customer_id`

It actually executes each plan for a few documents and Picks the fastest one in practice, not just in theory

Summary Table:

Feature	MySQL (RDBMS)	MongoDB
Plan Selection Method	Cost-based (theoretical estimate)	Trial-based (actual mini-execution)
Accuracy of Plan Choice	Depends on accuracy of statistics	Based on real performance
When Plans Are Evaluated	Before execution	During early phase of execution
Caching Plan for Reuse	Yes (from version 8.x onward)	Yes (automatically cached)