The guidelines listed below summarize the ways you can improve query performance.
- If nested or correlated queries do not seem to process efficiently, restructure the query as a flat join to see if that method
improves processing speed.
- Use a correlated query instead of a nested query if the correlated column is indexed and the nested query returns a large
amount of data.
- Try to index the columns that have the lowest selectivity values (those with the largest number of different values), and
construct your queries to include predicates involving these columns.
- Try to avoid OR conditions on different columns. Use a union of queries containing AND conditions instead.
- When creating compound indexes, try to list columns in the same order they appear in the ORDER BY clauses of your queries
to allow the compound index to be used to speed up sorts.
- Avoid numeric conversions. Try to make comparisons between columns of the same type.
- Avoid the use of LIKE predicates beginning with % or __.
- Avoid arithmetic expressions in a predicate, especially those that evaluate to a constant. Perform the calculation beforehand,
if possible.
- Select only the columns you need. Avoid SELECT * FROM when possible, especially when retrieving data across a network. Unneeded
data creates unnecessary network traffic.
- Avoid using COUNT(*) to check for existence.
- Evaluate the benefits of single vs. compound indexes. Use compound index ordering if suitable. If possible, order the columns
in the compound index by highest selectivity (that is, order the columns with the most unique values first).
- Provide redundant information when joining tables, so that the system can select the lowest cost solution for you.
- If a table is frequently updated or if most searches on the table are sequential searches (every record is accessed), avoid
building any indexes on the table except for those used to enforce uniqueness.
- Use views only when absolutely necessary. Each view access requires additional I/O and processing costs.