Database Optimisation Techniques

🔍 Query Optimization

🧠 Make SQL run faster and more efficiently.

• 🧾 Use SELECT only for needed columns

  Avoid SELECT *; fetch only what's needed to reduce I/O.

• 🧮 Use proper WHERE clauses

  Filter rows early to reduce scanned data.

• 🔄 Use JOINs wisely

  Join on indexed columns and avoid unnecessary nested subqueries.

• 📊 EXPLAIN / EXPLAIN PLAN

  Use this to analyze query execution and identify bottlenecks.

📦 Index Optimization

📘 Speed up data retrieval using proper indexing.

• 🧷 Create indexes on frequently queried columns

  Especially on WHERE, JOIN, and ORDER BY fields.

• 🧹 Avoid too many indexes

  Each index adds overhead to INSERT, UPDATE, DELETE.

• 📚 Use composite indexes

  Index multiple columns for multi-condition queries.

• 🧮 Use covering indexes

  Let the index include all needed columns to avoid table lookups.

🏗️ Database Schema Design

📐 Design for performance and flexibility.

• 🗃️ Normalize to reduce redundancy

  Avoid duplicate data, especially in OLTP systems.

• 🧱 Denormalize for read-heavy workloads

  Especially helpful in OLAP or reporting databases.

• 🧬 Choose correct data types

  Smaller, precise data types save space and improve performance.

• 🪜 Use constraints smartly

  Primary keys, foreign keys, and checks help enforce data quality.

🧰 Maintenance Techniques

🧹 Keep the database clean, healthy, and optimized over time.

• 🔄 Regularly update statistics

  Keeps the query optimizer informed about data distribution.

• 🧱 Rebuild/reorganize indexes

  Fix fragmented indexes to maintain read/write speed.

• 🗑️ Purge or archive old data

  Reduces table size and improves query performance.

• 🧵 Monitor long-running queries

  Use logs and performance dashboards to identify slow queries.

🌐 Server-Level Tuning

⚙️ Improve the performance of the database engine itself.

• 💾 Tune memory settings

  Configure buffer pool, cache sizes, and sort areas.

• 🛠️ Optimize connection pooling

  Reduce overhead of frequent connection creation.

• 📡 Use load balancing and replication

  Spread out the workload and enhance availability.

• 🧯 Enable logging for slow queries

  Identify and fix problem areas before they scale.

📈 Monitoring & Profiling Tools

🧪 Track performance and uncover hidden issues.

• 📊 Use built-in profilers

  MySQL’s slow query log, PostgreSQL’s pg_stat_statements, etc.

• 🔍 Leverage third-party tools

  e.g., New Relic, Datadog, or Percona Monitoring.

• 🧠 Benchmark regularly

  Compare performance over time, especially after schema or query changes.

🧠 Summary Table