QUERY
OPTIMIZATION SQL
- The sql query becomes faster if you use the
actual columns names in SELECT statement instead of than '*'
- Select carefully where we have to use “where” and
“having”
·
Sometimes
you may have more than one sub queries in your main query. Try to minimize the
number of sub query block in your query.
For Example: Write
the query as
“SELECT
name
FROM employee
WHERE (salary, emp_age) = (SELECT MAX (salary), MAX (emp_age)
FROM employee_details)
AND dept = 'Electronics'; “
Instead of:
“SELECT
name
FROM employee
WHERE salary = (SELECT MAX (salary) FROM employee_details)
AND age = (SELECT MAX (emp_age) FROM employee_details)
AND emp_dept = 'Electronics';”
·
Usually
“IN” has the
slowest performance, “IN” is efficient
when most of the filter criteria is in the sub-query
·
“EXISTS” is efficient
when most of the filter criteria is in the main query
·
Use
“EXISTS”
instead
of “DISTINCT”
when
using joins which involves tables having one-to-many relationship.
For Example: Write
the query as
SELECT d.dept_id, d.dept_name
FROM dept d
WHERE EXISTS (SELECT 'X' FROM employee e WHERE e.dept_name = d.dept_name);
Instead of:
SELECT DISTINCT d.dept_id,
d.dept_name
FROM dept d,employee e
WHERE e.dept_name = e.dept_name;
·
Try to use
UNION ALL in place of UNION
·
Be careful
while using conditions in WHERE clause.
For Example: Write the
query as
SELECT id, name, age FROM
student WHERE age > 30;
Instead of:
SELECT id, name, age FROM
student WHERE age! = 50;
