What is Query Optimization in DBMS? How to optimize a query?

A simple definition of Query Optimization can be efficient execution of the query such that it results in minimum cost.

Can somebody explain this in detail with example?

How can we achieve query optimization in large databases?


  • brajeshkumar
    AS my knowledge there is some proccess
    1.inner querry
    2.join query
    3.using specific keyword(like max ,min)
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    A few techniques of Query Optimization are:

    1. Faster SELECT query, when you use the actual column names instead of '*'.
    Original SQL Code:
    SELECT id, first_name, last_name, age FROM member_details;
    Optimized SQL Code:
    SELECT * FROM member_details;
    2. HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
    Original SQL Code:
    SELECT subject, count(subject) 
    FROM student_details 
    GROUP BY subject 
    HAVING subject!= 'Vancouver' AND subject!= 'Toronto';
    Optimized SQL Code:
    SELECT subject, count(subject) 
    FROM student_details 
    WHERE subject != 'Science' 
    AND subject != 'Maths' 
    GROUP BY subject;
    3. Sometimes you may have more than one sub-queries in your main query. Try to minimize the number of subquery block in your query.
    Original SQL Code:
    SELECT name 
    FROM employee
    WHERE salary = (SELECT MAX(salary) FROM employee_details) 
    AND age = (SELECT MAX(age) FROM employee_details) 
    AND emp_dept = 'Electronics';
    Optimized SQL Code:
    SELECT name 
    FROM employee 
    WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) 
    FROM employee_details) 
    AND dept = 'Electronics';
    4. Use operator EXISTS, IN and table joins appropriately in your query.
    Original SQL Code:
    Select * from product p 
    where product_id IN 
    (select product_id from order_items)
    Optimized SQL Code:
    Select * from product p 
    where EXISTS (select * from order_items o 
    where o.product_id = p.product_id)
    5. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
    Original SQL Code:
    SELECT DISTINCT d.dept_id, d.dept 
    FROM dept d,employee e 
    WHERE e.dept = e.dept;
    Optimized SQL Code:
    SELECT d.dept_id, d.dept 
    FROM dept d 
    WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
  • Manish Goyal
    Manish Goyal
    Adding a index to a column is also a good way to optimize your search query

    for eg if you have database having table say A

    having attributes such as city, state, address , then you can add index to these to make your search fast

    2:- Always try to avoid unwanted parenthesis in your query

You are reading an archived discussion.

Related Posts

Let's use this thread to understand the concepts of atomicity and aggregation in DBMS. Try to explain in simple words with examples.
Let the creative juices flow.. Come up with a script of your own, direct your friends to act and produce a film with your mobile camera on site- the DSK...
CEans, We're in the final phases of CE Grand Quiz. The following one shouldn't bother your heads for long time. Go! Crack It & Win 20 CE Coins 😀 Image...
Ng Beng Kiat's Min7.1 Blazes Through 2011 All Japan Micromouse Competition - and following video shows how you will have hard time believing your own eyes. [video=youtube;CLwICJKV4dw]https://www.youtube.com/watch?feature=player_embedded&v=CLwICJKV4dw[/video]
Here comes the conquering King! Super K Nothing less. ​Super K | Movie Feature - Yahoo! Movies India