Lesson 15: Performance Optimization

📚 Learning Objectives

By the end of this lesson, you will be able to: - Analyze query performance using EXPLAIN - Optimize database indexes for better performance - Write efficient SQL queries - Understand query execution plans - Implement database tuning strategies - Monitor and measure performance improvements

🎯 Prerequisites

• Completed Lessons 1-14
• Understanding of indexes and views
• Knowledge of stored procedures and triggers
• Basic understanding of database architecture

📖 Table of Contents

1. Introduction to Performance Optimization
2. Query Analysis with EXPLAIN
3. Index Optimization Strategies
4. Query Optimization Techniques
5. Database Configuration Tuning
6. Monitoring and Profiling
7. Caching Strategies
8. Best Practices
9. Common Performance Pitfalls

---

1. Introduction to Performance Optimization

Database performance optimization is the process of improving the speed and efficiency of database operations. It involves analyzing, tuning, and monitoring various aspects of database performance.

Key Performance Areas

• Query Performance: Optimizing individual SQL queries
• Index Strategy: Strategic use of indexes
• Database Configuration: Server-level tuning
• Hardware Optimization: Storage, memory, and CPU
• Application Design: Efficient data access patterns

Performance Metrics

• Response Time: How long queries take to execute
• Throughput: Number of queries processed per second
• Resource Usage: CPU, memory, and I/O utilization
• Scalability: Performance under increasing load

---

2. Query Analysis with EXPLAIN

Basic EXPLAIN Usage

-- Analyze a simple query
EXPLAIN SELECT 
    s.first_name,
    s.last_name,
    d.name AS department_name
FROM students s
JOIN departments d ON s.department_id = d.department_id
WHERE s.gpa >= 3.5;

-- More detailed analysis
EXPLAIN FORMAT=JSON SELECT 
    s.first_name,
    s.last_name,
    d.name AS department_name
FROM students s
JOIN departments d ON s.department_id = d.department_id
WHERE s.gpa >= 3.5;

Understanding EXPLAIN Output

-- Analyze complex query with multiple JOINs
EXPLAIN SELECT 
    s.first_name,
    s.last_name,
    c.course_code,
    c.name AS course_name,
    e.grade
FROM students s
JOIN departments d ON s.department_id = d.department_id
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.status = 'Active'
  AND e.grade >= 85
ORDER BY s.gpa DESC;

-- Key columns to analyze:
-- type: ALL (full table scan), index, range, ref, eq_ref
-- key: Index used
-- rows: Estimated rows examined
-- Extra: Additional information about execution

EXPLAIN ANALYZE (MySQL 8.0+)

-- Get actual execution statistics
EXPLAIN ANALYZE SELECT 
    s.first_name,
    s.last_name,
    AVG(e.grade) AS average_grade
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE s.department_id = 1
  AND e.grade IS NOT NULL
GROUP BY s.student_id, s.first_name, s.last_name
HAVING AVG(e.grade) >= 85;

---

3. Index Optimization Strategies

Identifying Missing Indexes

-- Find slow queries without indexes
SHOW PROCESSLIST;

-- Analyze slow query log (if enabled)
-- Look for queries with "Using filesort" or "Using temporary"

-- Check for full table scans
EXPLAIN SELECT * FROM students WHERE last_name = 'Smith';
-- If type = 'ALL', consider adding an index

-- Add appropriate index
CREATE INDEX idx_student_lastname ON students(last_name);

Composite Index Optimization

-- Analyze query patterns
EXPLAIN SELECT 
    first_name,
    last_name,
    gpa
FROM students
WHERE department_id = 1 
  AND gpa >= 3.5
ORDER BY gpa DESC;

-- Create optimal composite index
-- Most selective column first, then ORDER BY column
CREATE INDEX idx_dept_gpa_name ON students(department_id, gpa DESC, first_name, last_name);

Covering Indexes

-- Query that can be satisfied entirely by an index
SELECT 
    student_id,
    first_name,
    last_name,
    gpa
FROM students
WHERE department_id = 1 
  AND gpa >= 3.5;

-- Create covering index
CREATE INDEX idx_covering ON students(department_id, gpa, student_id, first_name, last_name);

-- Verify it's a covering index (Extra column should show "Using index")
EXPLAIN SELECT 
    student_id,
    first_name,
    last_name,
    gpa
FROM students
WHERE department_id = 1 
  AND gpa >= 3.5;

Partial Indexes

-- Index only active students
CREATE INDEX idx_active_students ON students(gpa) 
WHERE status = 'Active';

-- Index only passing grades
CREATE INDEX idx_passing_grades ON enrollments(grade) 
WHERE grade >= 85;

---

4. Query Optimization Techniques

Efficient JOIN Strategies

-- ❌ INEFFICIENT: Cartesian product
SELECT *
FROM students s, enrollments e, courses c
WHERE s.student_id = e.student_id
  AND e.course_id = c.course_id;

-- ✅ EFFICIENT: Explicit JOINs with proper conditions
SELECT 
    s.first_name,
    s.last_name,
    c.course_code,
    e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
WHERE s.status = 'Active'
  AND e.grade IS NOT NULL;

Subquery Optimization

-- ❌ INEFFICIENT: Correlated subquery
SELECT 
    s.first_name,
    s.last_name,
    s.gpa,
    (SELECT AVG(grade) 
     FROM enrollments e 
     WHERE e.student_id = s.student_id) AS avg_grade
FROM students s;

-- ✅ EFFICIENT: JOIN with aggregation
SELECT 
    s.first_name,
    s.last_name,
    s.gpa,
    AVG(e.grade) AS avg_grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name, s.gpa;

LIMIT Optimization

-- ❌ INEFFICIENT: ORDER BY before LIMIT without index
SELECT * FROM students
ORDER BY gpa DESC
LIMIT 10;

-- ✅ EFFICIENT: Use index for ORDER BY
CREATE INDEX idx_gpa_desc ON students(gpa DESC);

-- Or use window functions for better performance
SELECT 
    student_id,
    first_name,
    last_name,
    gpa
FROM (
    SELECT 
        student_id,
        first_name,
        last_name,
        gpa,
        ROW_NUMBER() OVER (ORDER BY gpa DESC) as rn
    FROM students
    WHERE gpa IS NOT NULL
) ranked
WHERE rn <= 10;

EXISTS vs IN Optimization

-- ❌ INEFFICIENT: IN with large subquery
SELECT * FROM students
WHERE student_id IN (
    SELECT student_id 
    FROM enrollments 
    WHERE grade >= 90
);

-- ✅ EFFICIENT: EXISTS for better performance
SELECT * FROM students s
WHERE EXISTS (
    SELECT 1 
    FROM enrollments e 
    WHERE e.student_id = s.student_id 
      AND e.grade >= 90
);

-- ✅ ALTERNATIVE: JOIN if you need enrollment data
SELECT DISTINCT s.*
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
WHERE e.grade >= 90;

---

5. Database Configuration Tuning

MySQL Configuration Parameters

-- Check current configuration
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'query_cache_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

-- Key parameters to tune:
-- innodb_buffer_pool_size: 70-80% of available RAM
-- query_cache_size: For read-heavy workloads
-- tmp_table_size: For temporary tables
-- max_heap_table_size: For MEMORY tables

Connection and Thread Optimization

-- Monitor connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Key settings:
-- max_connections: Maximum concurrent connections
-- thread_cache_size: Thread reuse for better performance
-- wait_timeout: Close idle connections

Storage Engine Optimization

-- Check table storage engines
SHOW TABLE STATUS;

-- InnoDB specific optimizations
-- innodb_flush_log_at_trx_commit: 0, 1, or 2
-- innodb_log_file_size: Larger for better performance
-- innodb_file_per_table: 1 for better maintenance

---

6. Monitoring and Profiling

Performance Schema Usage

-- Enable performance schema for monitoring
-- Check if it's enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Monitor top queries by execution time
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000000 AS avg_time_seconds,
    MAX_TIMER_WAIT/1000000000000 AS max_time_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Slow Query Analysis

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking > 1 second

-- Analyze slow queries
SELECT 
    sql_text,
    query_time,
    lock_time,
    rows_examined,
    rows_sent
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

Real-time Monitoring

-- Monitor current processes
SHOW PROCESSLIST;

-- Check table locks
SHOW STATUS LIKE 'Table_locks%';

-- Monitor InnoDB status
SHOW ENGINE INNODB STATUS;

-- Check index usage
SHOW STATUS LIKE 'Handler_read%';

---

7. Caching Strategies

Query Result Caching

-- Enable query cache (MySQL 5.7 and earlier)
-- Note: Query cache is deprecated in MySQL 8.0

-- Check cache hit ratio
SHOW STATUS LIKE 'Qcache%';

-- Monitor cache efficiency
SELECT 
    Qcache_hits / (Qcache_hits + Qcache_inserts) * 100 AS cache_hit_ratio
FROM (
    SELECT 
        VARIABLE_VALUE AS Qcache_hits
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Qcache_hits'
) hits,
(
    SELECT 
        VARIABLE_VALUE AS Qcache_inserts
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Qcache_inserts'
) inserts;

Application-Level Caching

-- Create materialized view for frequently accessed data
CREATE TABLE student_summary AS
SELECT 
    s.student_id,
    s.first_name,
    s.last_name,
    d.name AS department_name,
    s.gpa,
    COUNT(e.enrollment_id) AS total_courses,
    AVG(e.grade) AS average_grade
FROM students s
JOIN departments d ON s.department_id = d.department_id
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name, d.name, s.gpa;

-- Create index on materialized view
CREATE INDEX idx_student_summary_dept ON student_summary(department_name);

-- Refresh materialized view periodically
-- (In MySQL, you need to recreate the table)

---

8. Best Practices

Query Design Best Practices

-- 1. Use specific column lists instead of SELECT *
SELECT student_id, first_name, last_name FROM students;

-- 2. Use appropriate data types
-- Use INT instead of VARCHAR for numeric IDs
-- Use DATE instead of VARCHAR for dates

-- 3. Use LIMIT for large result sets
SELECT * FROM students ORDER BY enrollment_date DESC LIMIT 100;

-- 4. Use prepared statements for repeated queries
PREPARE stmt FROM 'SELECT * FROM students WHERE department_id = ?';
SET @dept_id = 1;
EXECUTE stmt USING @dept_id;

-- 5. Use batch operations for multiple inserts
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES 
    (1, 1, CURDATE()),
    (2, 1, CURDATE()),
    (3, 1, CURDATE());

Index Best Practices

-- 1. Create indexes on frequently queried columns
CREATE INDEX idx_student_email ON students(email);

-- 2. Use composite indexes for multi-column queries
CREATE INDEX idx_student_dept_gpa ON students(department_id, gpa);

-- 3. Consider index cardinality
-- High cardinality = good for indexes
-- Low cardinality = may not benefit from indexes

-- 4. Monitor index usage
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'school_db'
ORDER BY CARDINALITY DESC;

-- 5. Remove unused indexes
-- Use pt-index-usage tool or monitor with Performance Schema

Database Design Best Practices

-- 1. Normalize tables to reduce redundancy
-- 2. Use appropriate primary keys
-- 3. Use foreign key constraints
-- 4. Choose optimal data types
-- 5. Plan for growth and scalability

-- Example: Well-designed table structure
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT NOT NULL,
    gpa DECIMAL(3,2) CHECK (gpa >= 0 AND gpa <= 4.0),
    status ENUM('Active', 'Inactive', 'Graduated') DEFAULT 'Active',
    enrollment_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_department (department_id),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_gpa (gpa),

    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

---

9. Common Performance Pitfalls

Pitfall 1: N+1 Query Problem

-- ❌ INEFFICIENT: N+1 queries
-- Application code:
-- 1. SELECT * FROM students WHERE department_id = 1;
-- 2. For each student: SELECT * FROM enrollments WHERE student_id = ?

-- ✅ EFFICIENT: Single query with JOIN
SELECT 
    s.first_name,
    s.last_name,
    c.course_code,
    e.grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id
WHERE s.department_id = 1;

Pitfall 2: Missing WHERE Clauses

-- ❌ INEFFICIENT: No WHERE clause
SELECT * FROM students ORDER BY gpa DESC;

-- ✅ EFFICIENT: Use WHERE to limit results
SELECT * FROM students 
WHERE gpa >= 3.5 
ORDER BY gpa DESC;

Pitfall 3: Inefficient LIKE Patterns

-- ❌ INEFFICIENT: Leading wildcard
SELECT * FROM students WHERE last_name LIKE '%son';

-- ✅ EFFICIENT: Use full-text search or trailing wildcard
SELECT * FROM students WHERE last_name LIKE 'John%';

-- ✅ BETTER: Use full-text index for complex searches
ALTER TABLE students ADD FULLTEXT(first_name, last_name);
SELECT * FROM students WHERE MATCH(first_name, last_name) AGAINST('John Smith');

Pitfall 4: Unnecessary DISTINCT

-- ❌ INEFFICIENT: Unnecessary DISTINCT
SELECT DISTINCT department_id FROM students;

-- ✅ EFFICIENT: Use GROUP BY or check if DISTINCT is needed
SELECT department_id FROM students GROUP BY department_id;
-- Or simply: SELECT department_id FROM students; (if no duplicates expected)

---

🎯 Summary

Performance optimization is a critical aspect of database management:

Key Areas

• Query Analysis: Use EXPLAIN to understand execution plans
• Index Strategy: Strategic indexing for optimal performance
• Query Optimization: Write efficient SQL queries
• Configuration Tuning: Optimize database server settings
• Monitoring: Continuous performance monitoring

Optimization Process

1. Identify performance bottlenecks
2. Analyze using EXPLAIN and monitoring tools
3. Optimize queries, indexes, and configuration
4. Measure performance improvements
5. Monitor ongoing performance

Best Practices

• Profile before optimizing - measure actual performance
• Index strategically - not every column needs an index
• Write efficient queries - avoid common anti-patterns
• Monitor continuously - performance can degrade over time
• Test thoroughly - optimization changes can have unintended consequences

🔄 Next Steps

• Practice analyzing queries with EXPLAIN
• Implement strategic indexing in your database
• Set up performance monitoring
• Move on to Lesson 16: SQLAlchemy ORM Basics

📚 Additional Resources

• MySQL Documentation: Optimization
• MySQL Documentation: EXPLAIN
• MySQL Documentation: Performance Schema
• MySQL Documentation: Indexes