Query Optimization

Queries that are executed repeatedly or on a large DataBase should be optimized.

Techniques:

• Indexing: Faster lookup and retrieval
• Normalization: Lowers redundancy and lightens database size
• Denormalization: Lowers query time by using redundant content, mostly used in frequent join queries.
• Query structure:
  • Place most selective filters first.
  • Optimize join queries.
• Hardware: Upgrade Storage, RAM, Or CPU based on performance monitoring

Strategies:

• Avoid overusing sub-queries.
• Don’t retrieve data that is not needed(both rows and columns)
• Read as little data as possible.
• Avoid N:N JOINs