Retrieve only the info that is needed | Caching

SQL Practices to be avoided

• Avoid using "Distinct" in comparison clauses (that is, where column a in (select distinct b from z)). The "Distinct" is nothing but an overhead.

• Avoid the use of functions or operators on indexed columns in a predicate. For example, the following query will not be able to take advantage of an index on the LASTNAME column:

  SELECT lastname, firstname FROM employee WHERE UPPER(LASTNAME) = `BROWN'

• Avoid using FOR UPDATE, as it can lock the rows for longer times and may cause waits and deadlocks.

• Avoid constructs that require extra table scanning, index scanning, or locking (for example, DISTINCT, ORDER BY, GROUP BY, and so on).

• Avoid scalar functions (for example, Count(*), max(), avg()).

• Avoid INNER and OUTER joins unless they are warranted. Too many times these are not required and result in the optimizer choosing the wrong path.