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

Replies

• 

  brajeshkumar

  AS my knowledge there is some proccess
  1.inner querry
  2.join query
  3.using specific keyword(like max ,min)
• 

  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

  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