Guidelines on optimising SQL Quires
SQL Workload reduced by
- Efficient structuring of the data (indexes, clusters, keys
- Efficient structuring of queries
- Providing more information to the DBMS query optimiser
- Avoid the use of *, count(*) (the system needs to work out at run time what needs to be substituted for the *)
- Avoid very long table names (they take longer to parse)
- Do use brief table aliases in joins
- Do specify table.columns in joins (avoids the optimiser having to work out which one is intended).
- Be Aware of the assumptions the optimiser in your system is making. Indexes etc. will give it more choice, check with and without processing times.
Summary
- JOIN generally faster than nested subquery
- Indexed columns generally faster than non-indexed columns for queries with a hit rate < 20%
- Unique index faster than non-unique
- != Does not use an index unlike other operators
- Non-compressed indexes faster than compressed indexes
From revision notes on an Advance Database System and Technology module at Queen Mary, University of London.
