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);
