Computer scienceFundamentalsSQL and DatabasesDBMSMySQLOptimization

Explain plan

Theory

Restore plan from the query

Report a typo

Consider the following query:

SELECT *
FROM employee e
INNER JOIN salary s
ON e.id = s.employee_id
WHERE s.to_date is NULL
  AND e.id NOT IN (10001, 10002, 10003);

Change the order of the lines to make it a valid execution plan.

First of all we're looking up on employee. Remember that the execution starts from the bottom of the tree.
Put the items in the correct order
     -> Single-row index lookup on e using PRIMARY (id=s.employee_id) (cost=1.00 rows=1)
-> Nested loop inner join (cost=441738.72 rows=137836)
    -> Filter: ((s.to_date is null) and (s.employee_id not in (10001,10002,10003))) (cost=290118.90 rows=137836)
        -> Table scan on s (cost=290118.90 rows=2756719)
___

Create a free account to access the full topic