/database-tuning-optimization

A college project on database system tuning and query optimization.

Primary LanguageTeX

Database Tuning and Optimization

This is a college project on database system tuning and query optimization.

Database Filling

  • The SQL database filling scripts and NoSQL collections can be found in this GDrive folder.

  • Make sure to disable foreign key checks bfore running SQL scripts : SET FOREIGN_KEY_CHECKS=0;.

Optimization Techniques

  • Schema Optimization :

    • Normalization/Denormalization.
    • Data types optimization.

  • Memory and Cache Optimization :

    • Hardware Specs.
    • Stored Procedures.

  • Index Optimization :

    • Indexes Generation on Non-Primary Keys.
    • UNION is better than OR with Indexes.

  • Query Optimization :

    • Unnecessary Conditions Elimination.
    • UNION ALL instead of UNION.

Required Statistics

  • Non-optimized SQL vs. Optimized SQL (100,000).

  • Optimized SQL vs. NoSQL (100,000).

  • Different Sizes of Optimized SQL (10,000 / 100,000 / 1,000,000).

  • Different hardware on Optimized SQL.

Clearing Disk Cache

  • To clear disk cache for database : 
    chmod +x clear_cache.bash
./clear_cache.bash