1) The sql query becomes faster if you use the actual column names in SELECT statement instead of ‘*’.
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 purpose.
3) Sometimes you may have more than one subquery in you main query. Try to minimize the number of subquery block in the query.
4) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
5) Try to use UNION ALL in place of UNION.
6) User operator EXISTS, IN and table joins appropriately in your query.
- a) Usually IN has the slowest performance.
- b) IN is efficient when most of the filter criteria is in the subquery.
- c) EXISTS is efficient when most of the filter query is in the main query.