Oninit
Talk to an expert →

User Optimisation of a Query

As intimated at the beginning of the last section, there are a number of ways a user can optimise a query. These methods will either simplify matters for the DBMS's optimisation techniques or produce optimisations that are beyond those that can be achieved by the DBMS:

Order the Conditions in a Query for Optimal Acceptance or Rejection

If a number of conditions are ANDed together, a row will be rejected as soon as a condition is encountered that fails. Therefore, place the condition that is likely to fail most often first and order the rest accordingly. Conversely, if the conditions are ORed together, place the condition that is likely to succeed most often first.

Use UNIONs to Avoid Sequential Access.

The use of both ANDed and Ored conditions in a WHERE clause may preclude the use if indexes. For example, consider the following example:

SELECT *
FROM t0003rental
WHERE (c0001id = 34 AND c0002id > 69)
OR c0002id = 42;

In the example, two sets of rows are retrieved, one for the condition (c0001id = 34 AND c0002id > 69) and the other for c0002id = 42. Although both custid and filmid are indexed, the combination of conditions precludes their use, forcing the rental table to be searched sequentially. However, if a UNION is used, as follows, both I indexes will be used:

SELECT *
FROM t0003rental
WHERE c0001id = 34 AND c0002id > 69
UNION
SELECT * FROM t0003rental WHERE c0002id = 42;

Avoid Difficult Usages of Wildcards.

With both LIKE and MATCHES conditions, the use of wildcards causes the DBMS extreme difficulty

Avoid wildcards in the initial position of a string

For example

SELECT *
FROM t0001customer
WHERE c0001surname MATCHES "*th";

The DBMS cannot use any index that exists on the surname column to speed processing. If the table is the second or later one in a join, the query is slow. If such a test is essential, process the table separately, storing its output in a temporary table; then use the temporary table to join to the others.

Even if the wildcards occur in the middle or end of a string, allowing the use of any indexes on the column or columns involved, the query is still slow. Depending on the data involved, convert the wildcard test to a range test. For example, the latter of the following two tests is more efficient:

SELECT *
FROM t0001customer
WHERE c0001tel MATCHES "0208*";

SELECT *
FROM t0001customer
WHERE c0001tel BETWEEN "0208 0000000" AND "0208 9999999";

Avoid Noninitial Substrings.

Very much like using wildcards at the beginning of a string, using substring that does not include the start of a string - eg surname[6,12] will preclude the use of any indexes on the column or columns involved.

Similarly, use of initial substrings - eg c0001tel[1,4] = "0208" maybe replaced with range tests.

Use Sub-queries for Tables that are required for Cross-Reference but Not for Output

To reduce the number of Cartesian Joins required in a query, use sub-queries where possible. However, avoid the use of Correlated Sub-queries. These occur when a column label appears in both the select list of the main and the WHERE clause of the sub-query. This forces the sub-query to re-executed for each row in the main query.

Avoid Joining Tables to Views that are themselves Joins.

If a table is joined to a view that contains table joins itself, the resulting query that is executed may contain a combination of joins and filter conditions that severely slow execution. Analyse the resulting query see if it could be written in a much simpler format.

The Use of Indexes.

Although the optimiser will use indexes, whenever possible, they should be created for specific purposes:

  • Primary Keys.
  • Foreign Keys, but only if they are used because a primary key index cannot (eg in OUTER joins).
  • To avoid autoindexing
  • To eliminate sequential access to large tables.
  • Sorts.

Primary key indexes aid the uniqueness of the column or column set that form the key. Also, they are used in most joins to other tables.

Consider the use of Composite indexes. If a composite index were to be created on columns a, b and c in the order stated, the index would be used to:

  • Evaluate filter expressions on column a.
  • Join column a to another table.
  • Implement ORDER BY and GROUP BY on columns a, ab or abc.

Use CLUSTER indexes on tables that are not going to be changed on a regular basis. Use the columns that are going to be sorted by part of the index. This will remove the need for an ORDER BY clause on simple queries and
speed the sorting of more complex queries.

Finally, as stated in earlier sections, keep the number of indexes on a table to the bare minimum required for normal use. If an index is required to aid the speed of execution of a query, but not for normal use, it is better to create the index before executing the query, rather than make the index permanent.

Temporary Tables

If there are to be a number of queries based around a subset of data from a large table or set of tables, it is better to copy the data subset to a temporary table and direct the queries to the temporary table, rather than requery the original table or set of tables each time.