Structured Query Language SQL is a multifaceted program. Each facet needs to be correctly balanced in order to optimise the performance so that results are obtained in a timely manner.
It is a bit like tuning a race engine; it takes experience and you will realize that optimisation is unlikely to be achieved immediately; you get close, then fine-tune in a series of subtle steps.
Put another way, the purpose of an SQL is to retrieve the requested information from a database. The component of an SQL system that attempts to determine the best way to retrieve the data is called a query optimizer. This sounds like it can take a poorly formulated query and reword it so that it executes at optimal performance. However this is not the case, query optimizers operate by finding short cuts for searching through the database. A poorly formulated query will be difficult to execute quickly, so the need to craft the query carefully remains.
In SQL, queries are specified using the select statement and there are many methods (or query execution plans) for processing a query. The goal of the optimizer is to quickly find which method will be fastest.
The query optimizer must resolve two interrelated issues: firstly how it will access each table referenced in the query; secondly in which order to approach each table. It operates in a series of steps, trying to optimize each step in turn. It also has other optimizing capabilities such as identifying and avoiding redundant steps.
An optimizer is not foolproof and not infinitely powerful, so does not operate well with poorly defines queries. However, if an optimizer is used well it will speed up operations and produce accurate results every time.
