Assignment 6-A

Optimize Performance

Take slow queries and make them fast! You'll analyze execution plans, create strategic indexes, and apply optimization techniques to dramatically improve database performance.

6-7 hours
Advanced
150 Points
What You'll Practice
  • Create strategic indexes
  • Analyze EXPLAIN plans
  • Identify index usage
  • Rewrite slow queries
  • Measure performance gains
Contents
01

Assignment Overview

In this assignment, you will optimize a Large E-Commerce Database with millions of records. You'll be given slow queries and must make them fast using indexes, query rewrites, and other optimization techniques.

Skills Applied: This assignment tests your understanding of Index Types (Topic 6.1), Execution Plans (Topic 6.2), and Performance Tuning (Topic 6.3) from Module 6.
Indexing

B-tree, composite, covering

Execution Plans

EXPLAIN, cost analysis

Optimization

Query rewriting, statistics

02

The Scenario

FastShop Performance Crisis

FastShop's database has grown to millions of rows and critical queries are running slowly. The DBA team needs you to identify and fix the performance bottlenecks.

"Our order lookup queries are taking 30+ seconds. Customer complaints are increasing. We need these queries optimized to under 1 second!"

03

Optimization Tasks

1
Index Creation (5 tasks)
  • Create single-column index for frequent lookups
  • Create composite index for multi-column WHERE
  • Create covering index to eliminate table access
  • Create unique index for constraint enforcement
  • Evaluate and drop redundant indexes
2
Execution Plan Analysis (5 tasks)
  • Analyze EXPLAIN output for table scan vs index scan
  • Identify missing index from execution plan
  • Compare costs before and after optimization
  • Identify join order optimization opportunities
  • Detect suboptimal index usage
3
Query Optimization (5 tasks)
  • Rewrite query to avoid function on indexed column
  • Optimize OR conditions with UNION
  • Replace correlated subquery with JOIN
  • Add appropriate hints for query optimizer
  • Document before/after performance metrics
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-6-optimization
  • Include indexes.sql with CREATE INDEX statements
  • Include before-after.md with EXPLAIN outputs
  • Include optimized-queries.sql with rewritten queries
  • Document performance improvements with timing data
05

Grading Rubric

Criteria Points
Index creation tasks (5 × 10 pts) 50
Execution plan analysis (5 × 10 pts) 50
Query optimization (5 × 10 pts) 50
Total 150