Order of Clause Execution in SQL Query And Some Query Optimization Techniques

Easy Solution Web : MySQL Tutorials

An SQL Query made up of various Clause which gets executed in certain order like:

  1. From
  2. Where
  3. Group by
  4. Having
  5. Select
  6. Order by

From Clause

Executes first in the Query. Then Where Clause gets executed following From Clause.

These two clause helps use to shorten or result-set of a table where we need to perform the searching.

So, every time when you think about to optimize your query first focus on from clause then where clause

How to optimize From Clause?.

Using From clause for a single table, it is OK. But what about the case when we need to deal with multiple tables or joins.

There are common things we need to take care while forming our SQL Query using Joins.

  1. We must use ON clause as much as we can so that we can able to get limited rows while using Joins. So, that we need to perform a search with limited items.
  2. While using the inner join, A table having less number of rows should be put on the left. So that to form the combination of tables which ultimately becomes a resultant table, will need a limited searching operation in comparison to, when we call table vice-versa in joins.

Now let’s talk about Where Clause

Where Clause

We just use try to use predicates on indexed columns So that we can remove rows as much as we can with limited searching. If we don’t use indexed columns in predicates, searching will be done on the entire rows of the resultant table we get from the joins. We can optimize searching in where clause by using indexed columns and the correct type of index on the columns.

Note: We can’t use Unique index to make searching better where column type is text.

Sometimes we get confused why we are not able to use computed column which we have created during select clause in where clause. And the answer is where clause executed before the select clause.

This same law applied to the Group By and Having Clause

After Group by and Having Clause Now Select Clause execution will happen. To optimize select clause we must avoid unnecessary selection of columns in the select clause. So always select the required number of columns in SELECT CLAUSE.

After Select Clause, Order By clause gets executed. We must ensure that Order By is necessary for the query or not because it is one of costliest in term of optimization, in the SQL Query to be executed. So, try to avoid it.

And One more thing Columns we created in select clause can be used in Order by Clause