Performance Optimization

Introduction

Optimizing MySQL performance ensures fast query execution, efficient resource usage, and better user experience. This guide covers essential optimization techniques for WAMP/XAMPP environments.

Query Optimization

1. Use EXPLAIN

-- Analyze query execution
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Extended information
EXPLAIN EXTENDED SELECT * FROM users WHERE email = 'test@example.com';
SHOW WARNINGS;

-- JSON format (MySQL 5.7+)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';

Key EXPLAIN Columns:

2. Avoid SELECT *

-- Bad: Retrieves unnecessary data
SELECT * FROM users WHERE user_id = 1;

-- Good: Select only needed columns
SELECT user_id, username, email FROM users WHERE user_id = 1;

3. Use Indexes Effectively

-- Create index on frequently queried columns
CREATE INDEX idx_email ON users(email);

-- Composite index for multi-column queries
CREATE INDEX idx_name ON users(last_name, first_name);

-- Use covering indexes
CREATE INDEX idx_user_info ON users(user_id, username, email);
SELECT user_id, username, email FROM users WHERE user_id = 1;
-- Query uses index only, no table access needed

4. Optimize WHERE Clauses

-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Good: Sargable query uses index
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Bad: Leading wildcard prevents index use
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Good: Can use index
SELECT * FROM users WHERE email LIKE 'john%@gmail.com';

5. Limit Result Sets

-- Always use LIMIT for large results
SELECT * FROM posts ORDER BY created_at DESC LIMIT 100;

-- Pagination
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

6. Use Proper JOIN Syntax

-- Explicit JOIN (clear and optimizable)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.user_id = p.author_id
WHERE u.is_active = TRUE;

-- Avoid implicit JOIN
SELECT u.username, p.title
FROM users u, posts p
WHERE u.user_id = p.author_id AND u.is_active = TRUE;

7. Optimize Subqueries

-- Slow: Subquery in SELECT
SELECT
    u.username,
    (SELECT COUNT(*) FROM posts WHERE author_id = u.user_id) AS post_count
FROM users u;

-- Faster: JOIN with GROUP BY
SELECT u.username, COUNT(p.post_id) AS post_count
FROM users u
LEFT JOIN posts p ON u.user_id = p.author_id
GROUP BY u.user_id;

-- Use EXISTS instead of IN for large subqueries
-- Slow
SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

-- Faster
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

Index Optimization

When to Create Indexes

Always index:

Consider indexing:

Don’t index:

Index Types

-- B-Tree (default, best for most cases)
CREATE INDEX idx_name ON users(last_name);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_location ON users(country, city, zip_code);
-- Good for: WHERE country = 'USA' AND city = 'NYC'
-- Good for: WHERE country = 'USA'
-- Bad for: WHERE city = 'NYC' (doesn't use index)

-- Full-text index
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- Prefix index (for long strings)
CREATE INDEX idx_url ON pages(url(50));  -- First 50 characters

Analyzing Indexes

-- Show indexes on table
SHOW INDEX FROM users;

-- Analyze index usage
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = 'mydb' AND table_name = 'users';

-- Find unused indexes (MySQL 8.0+)
SELECT * FROM sys.schema_unused_indexes;

-- Drop unused indexes
DROP INDEX idx_unused ON users;

Configuration Optimization

Key MySQL Configuration Variables

Edit my.ini (Windows) or my.cnf (Linux/Mac):

[mysqld]
# Buffer Pool (most important for InnoDB)
# Set to 70-80% of available RAM for dedicated server
# Or 256MB-1GB for development
innodb_buffer_pool_size = 1G

# Log file size
innodb_log_file_size = 256M

# Query cache (deprecated in MySQL 8.0)
# MySQL 5.7 and earlier
query_cache_type = 1
query_cache_size = 64M

# Thread cache
thread_cache_size = 8

# Table cache
table_open_cache = 2000

# Max connections
max_connections = 150

# Sort and join buffers
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Connection timeout
wait_timeout = 600
interactive_timeout = 600

View Current Configuration

-- Show all variables
SHOW VARIABLES;

-- Show specific variable
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Show status
SHOW STATUS;

-- Show processlist
SHOW FULL PROCESSLIST;

Calculate Optimal Settings

-- Check current usage
SELECT
    FORMAT(SUM(data_length + index_length), 0) AS size_bytes,
    FORMAT(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'mydb';

-- Monitor buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

Table Optimization

1. Choose Right Storage Engine

-- InnoDB (default, recommended)
-- - ACID compliant
-- - Foreign keys
-- - Row-level locking
CREATE TABLE users (...) ENGINE=InnoDB;

-- MyISAM (legacy)
-- - No foreign keys
-- - Table-level locking
-- - Full-text search (before MySQL 5.6)
CREATE TABLE logs (...) ENGINE=MyISAM;

2. Optimize Table Structure

-- Analyze table
ANALYZE TABLE users;

-- Optimize table (defragment)
OPTIMIZE TABLE users;

-- Check table
CHECK TABLE users;

-- Repair table (if corrupted)
REPAIR TABLE users;

3. Use Appropriate Data Types

-- Good: Right-sized types
CREATE TABLE products (
    product_id INT UNSIGNED,
    price DECIMAL(10,2),
    stock SMALLINT UNSIGNED,
    is_active BOOLEAN
);

-- Bad: Oversized types
CREATE TABLE products (
    product_id BIGINT,
    price DOUBLE,
    stock INT,
    is_active VARCHAR(10)
);

4. Normalize Appropriately

-- Balance normalization with performance
-- Sometimes denormalization improves read performance

-- Instead of JOIN every time:
SELECT u.username, COUNT(p.post_id)
FROM users u
LEFT JOIN posts p ON u.user_id = p.author_id
GROUP BY u.user_id;

-- Can denormalize:
ALTER TABLE users ADD COLUMN post_count INT DEFAULT 0;

-- Update via trigger or application
UPDATE users SET post_count = post_count + 1
WHERE user_id = ?;

Caching Strategies

1. Application-Level Caching

// Use Redis or Memcached
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

// Check cache first
$cacheKey = 'user:' . $userId;
$user = $redis->get($cacheKey);

if (!$user) {
    // Query database
    $user = $db->query("SELECT * FROM users WHERE user_id = ?", [$userId]);
    // Store in cache (expire in 1 hour)
    $redis->setex($cacheKey, 3600, json_encode($user));
}

2. Query Cache (MySQL 5.7 and earlier)

-- Enable query cache
SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 67108864; -- 64MB

-- Check query cache stats
SHOW STATUS LIKE 'Qcache%';

-- Force cache
SELECT SQL_CACHE * FROM users WHERE user_id = 1;

-- Bypass cache
SELECT SQL_NO_CACHE * FROM users WHERE user_id = 1;

Note: Query cache removed in MySQL 8.0

3. Result Set Caching

// Cache expensive query results
$cacheKey = 'dashboard:stats';
if (!$cache->has($cacheKey)) {
    $stats = $db->query("/* expensive query */");
    $cache->set($cacheKey, $stats, 300); // 5 minutes
}

Connection Pooling

PHP PDO Connection

// Reuse connections
class Database {
    private static $instance = null;

    public static function getInstance() {
        if (self::$instance == null) {
            self::$instance = new PDO(
                'mysql:host=localhost;dbname=mydb',
                'user',
                'pass',
                [
                    PDO::ATTR_PERSISTENT => true,  // Persistent connection
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                ]
            );
        }
        return self::$instance;
    }
}

Connection Limits

-- Set max connections
SET GLOBAL max_connections = 200;

-- View current connections
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST;

-- Kill idle connections
KILL CONNECTION_ID;

Monitoring and Analysis

1. Slow Query Log

# Enable slow query log in my.ini/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /path/to/slow-query.log
long_query_time = 2  # Queries longer than 2 seconds
log_queries_not_using_indexes = 1

-- Enable dynamically
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

2. Analyze Slow Queries

# Use mysqldumpslow (included with MySQL)
mysqldumpslow -t 10 /path/to/slow-query.log
# Shows top 10 slow queries

# Options:
# -s: Sort (at=average time, t=total time, c=count)
# -t: Top N queries
# -g: Filter pattern

3. Performance Schema (MySQL 5.5+)

-- Enable performance schema
SET GLOBAL performance_schema = ON;

-- Find slow statements
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS executions,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 2) AS avg_seconds,
    ROUND(MAX_TIMER_WAIT / 1000000000000, 2) AS max_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Find queries using temporary tables
SELECT * FROM sys.statements_with_temp_tables;

-- Find full table scans
SELECT * FROM sys.statements_with_full_table_scans;

4. Information Schema

-- Table sizes
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

-- Column information
SELECT * FROM information_schema.COLUMNS
WHERE table_schema = 'mydb' AND table_name = 'users';

Partitioning

Range Partitioning

-- Partition by date range
CREATE TABLE logs (
    log_id INT,
    log_date DATE,
    message TEXT
) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Query specific partition
SELECT * FROM logs PARTITION (p2024);

List Partitioning

-- Partition by list of values
CREATE TABLE users (
    user_id INT,
    country VARCHAR(2)
) PARTITION BY LIST COLUMNS(country) (
    PARTITION p_us VALUES IN ('US'),
    PARTITION p_eu VALUES IN ('UK', 'FR', 'DE'),
    PARTITION p_asia VALUES IN ('JP', 'CN', 'IN'),
    PARTITION p_other VALUES IN (DEFAULT)
);

Best Practices Checklist

Query Optimization

Index Management

Configuration

Application Level

Monitoring

Common Performance Issues

1. N+1 Query Problem

// Bad: N+1 queries
$users = $db->query("SELECT * FROM users");
foreach ($users as $user) {
    $posts = $db->query(
        "SELECT * FROM posts WHERE author_id = ?",
        [$user['user_id']]
    );
}

// Good: Single query with JOIN
$result = $db->query("
    SELECT u.*, p.*
    FROM users u
    LEFT JOIN posts p ON u.user_id = p.author_id
");

2. Missing Indexes

-- Add missing indexes
CREATE INDEX idx_author ON posts(author_id);
CREATE INDEX idx_created ON posts(created_at);

3. Large Result Sets

-- Use pagination
SELECT * FROM logs
ORDER BY created_at DESC
LIMIT 100 OFFSET 0;

-- Or keyset pagination (more efficient)
SELECT * FROM logs
WHERE log_id < ?
ORDER BY log_id DESC
LIMIT 100;

4. Inefficient Queries

-- Rewrite inefficient queries
-- Use EXPLAIN to identify issues
-- Add appropriate indexes
-- Simplify complex JOINs

Tools and Resources

MySQL Tools

Monitoring Tools

External Tools

Next Steps

Additional Resources