Explain Query Optimization

Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10M

Year: Dec 2015

1 Answer
  • Query optimization is one of the most important tasks of a relational DBMS.
  • The Query optimizer generates alternative plans and chooses the best plan with the least estimated cost.
  • The query optimizer module is used to find out an execution plan which is the execution strategy to retrieve the result of the query from the database files.
  • A query can have many possible execution strategies each with different performances the process of selecting a reasonably efficient execution plan is known s query optimization.

Goals of query optimization:

  • Eliminate all unwanted data

    Query Optimization tries to eliminate unwanted tuples, or rows from given relation.


    Select *

    From Employees

    If we require only employee names no need to select all the columns from the table by specifying * is select clause. So, Optimized query will be

    Select Emp_Name

    From Employees

  • Speed up Queries

    Query optimization try to find out query which gives results very fast.

  • Increase query performance

    Break up the single complex SQL statement into several simple SQL statements which will increase performance of execution of query.

  • Select best query plan out of alternative query plan

    Consider following query to “Find all employees having age above 30 and workig for department HR”

    Select e.Emp_Name

    From EMPLOYEE e



    ON e.did=d.did

    WHERE e.age>30

    AND d.name=’HR

    Above query we can have alternative query like

    πe.Emp_Name (σe.Age>25 AND d.name=’HR’(EMPLOYEE e ⋈e.did=d.didDepartment d))

    πe.Emp_Name ((σe.Age>25 (EMPLOYEE e)) ⋈e.did=d.did(σd.dame=’HR’(Department d)))

    Now, the query option is to be selected is identified by query optimizer.

Please log in to add an answer.