Systems Analysis and Synthesis

6.4. Query Optimisation

The power of the SQL query sub-language is that there is always a simple procedure that can find the result:

1. Form the Cartesian product of a set of tables.

2. Select a subset of the resulting rows.

3. Project the result onto a subset of their attributes.

4. Sort the result into order.

We have four tools at our disposal: Joining is a process of combining information from two tables. Selection is the action of choosing rows that satisfy a certain condition. Projection is the act of ignoring all columns except those of interest. Finally, sorting is the means by which the order by clause is satisfied.

Query optimisation is the art of combining these operations most effectively. There are four ways this can be achieved:

1. By ...