Improving Database Performance

1. Place the most restrictive condition last in the WHERE clause, which is the condition that is first ready by the optimizer.
EX:
SELECT COUNT(*)
FROM TEST
WHERE LAST_NAME = 'SMITH'
AND CITY = 'INDIANAPOLIS';

result: 1024 Time: 20 seconds

SELECT COUNT(*)
FROM TEST
WHERE CITY = 'INDIANAPOLIS'
AND LAST_NAME = 'SMITH';

result: 1024 Time: 10 seconds

2. Avoid Full Table Scans
A full table scan occurs when an index is either not userd or there is no index on the table(s) being used by the SQL statement.
A reminder of data that should be indexed
- Columns used as primary keys
- Columns used as foreign keys
- Columns frequently used to join tables
- Columns frequently used as conditions in a query
- Columns that have a high percentage of unique values

4. Using the LIKE operator and wildcards

5. Avoiding the or operator ( Using "in" to take place of "or" )

6. Avoiding the HAVING clause

7. Avoiding large sort operations

8. Using stored procedures