Computer scienceFundamentalsSQL and DatabasesDBMSMySQLOptimization

Explain plan

Order plans by estimated execution time

Report a typo

Below you can see several execution plans retrieved with `EXPLAIN` operator. Assuming that estimations are correct, reorder the plans in ascending execution time, i.e. the first one is the fastest.

Note the cost/actual time in the first row of the "execution plans". It will help you solve the problem.

Put the items in the correct order

-> Inner hash join (no condition) (cost=8837083422.42 rows=88354590025)
  -> Table scan on e2 (cost=5.46 rows=297245)
  -> Hash
      -> Table scan on e1 (cost=30421.50 rows=297245)

-> Sort: department_employee_rel.department_id (actual time=195.232..195.233 rows=9 loops=1)
  -> Table scan on <temporary> (actual time=0.001..0.002 rows=9 loops=1)
      -> Aggregate using temporary table (actual time=195.214..195.215 rows=9 loops=1)
          -> Filter: (department_employee_rel.to_date is null) (cost=33431.35 rows=33007) (actual time=0.078..106.889 rows=240124 loops=1)
              -> Index scan on department_employee_rel using ak_department_employee_rel (cost=33431.35 rows=330066) (actual time=0.076..80.499 rows=331603 loops=1)

-> Nested loop inner join (cost=529744.00 rows=275672)
  -> Filter: (s.to_date is null) (cost=286779.95 rows=275672)
      -> Table scan on s (cost=286779.95 rows=2756719)
  -> Single-row index lookup on e using PRIMARY (id=s.employee_id) (cost=0.78 rows=1)

___

Create a free account to access the full topic