Optimize MySQL query performance with indexes and query profiling for high-traffic applications

Enable MySQL performance monitoring

First, enable the Performance Schema and slow query logging to capture detailed metrics about query execution times and resource usage.

sudo mysql -u root -p

-- Enable Performance Schema (if not already enabled)
SET GLOBAL performance_schema = ON;

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Set slow query log file location
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- Enable general query log for detailed analysis
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

EXIT;

Configure persistent MySQL performance settings

Make these settings permanent by adding them to your MySQL configuration file.

# Performance monitoring settings
[mysqld]
performance_schema = ON
slow_query_log = 1
long_query_time = 1.0
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/slow-query.log

InnoDB optimization settings
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000

Query cache settings (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

Connection and table settings
max_connections = 500
table_open_cache = 4000
tmp_table_size = 256M
max_heap_table_size = 256M

# Performance monitoring settings
[mysqld]
performance_schema = ON
slow_query_log = 1
long_query_time = 1.0
log_queries_not_using_indexes = 1
slow_query_log_file = /var/log/mysql/slow-query.log

InnoDB optimization settings
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000

Query cache settings (MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

Connection and table settings
max_connections = 500
table_open_cache = 4000
tmp_table_size = 256M
max_heap_table_size = 256M

Create log directory and restart MySQL

Ensure the log directory exists with proper permissions and restart MySQL to apply the configuration changes.

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 755 /var/log/mysql

sudo systemctl restart mysql
sudo systemctl status mysql

sudo systemctl restart mysqld
sudo systemctl status mysqld

Install and configure MySQL performance analysis tools

Install mysqldumpslow for analyzing slow query logs and pt-query-digest from Percona Toolkit for advanced query analysis.

sudo apt update
sudo apt install -y percona-toolkit mysql-client

Verify installation
pt-query-digest --version
mysqldumpslow --help

sudo dnf install -y percona-toolkit mysql

Verify installation
pt-query-digest --version
mysqldumpslow --help

Create sample database and tables for testing

Set up a test environment with sample data to demonstrate query optimization techniques.

mysql -u root -p

-- Create test database
CREATE DATABASE performance_test;
USE performance_test;

-- Create users table (without indexes initially)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'suspended'),
    last_login DATETIME
);

-- Create orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- Insert sample data
INSERT INTO users (username, email, status, last_login)
SELECT 
    CONCAT('user', num) as username,
    CONCAT('user', num, '@example.com') as email,
    CASE WHEN RAND() > 0.8 THEN 'inactive' ELSE 'active' END as status,
    NOW() - INTERVAL FLOOR(RAND() * 365) DAY as last_login
FROM (
    SELECT a.N + b.N  10 + c.N  100 + d.N * 1000 + 1 AS num
    FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
) numbers WHERE num <= 10000;

-- Insert orders data
INSERT INTO orders (user_id, order_date, total_amount, status)
SELECT 
    FLOOR(1 + RAND() * 10000) as user_id,
    NOW() - INTERVAL FLOOR(RAND() * 180) DAY as order_date,
    ROUND(RAND() * 1000, 2) as total_amount,
    CASE WHEN RAND() > 0.9 THEN 'cancelled' WHEN RAND() > 0.8 THEN 'pending' ELSE 'completed' END as status
FROM (
    SELECT a.N + b.N  10 + c.N  100 + d.N * 1000 + 1 AS num
    FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
    CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
) numbers WHERE num <= 50000;

Analyze query performance with EXPLAIN

Use the EXPLAIN statement to understand how MySQL executes queries and identify optimization opportunities.

-- Test slow query without indexes
EXPLAIN SELECT * FROM users WHERE email = 'user1000@example.com';

-- Test join query performance
EXPLAIN SELECT u.username, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.status = 'active' 
GROUP BY u.id, u.username;

-- Test range query
EXPLAIN SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' 
AND total_amount > 500;

-- Use EXPLAIN ANALYZE for detailed execution statistics (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%user1%';

Create optimized indexes based on query patterns

Add strategic indexes to improve query performance based on the EXPLAIN analysis results.

-- Index for email lookups (exact matches)
CREATE INDEX idx_users_email ON users (email);

-- Index for status filtering
CREATE INDEX idx_users_status ON users (status);

-- Composite index for complex queries
CREATE INDEX idx_users_status_created ON users (status, created_at);

-- Index for foreign key relationships
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Index for date range queries
CREATE INDEX idx_orders_date ON orders (order_date);

-- Composite index for multi-column WHERE clauses
CREATE INDEX idx_orders_date_amount ON orders (order_date, total_amount);

-- Index for ORDER BY optimization
CREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);

-- Show index usage
SHOW INDEXES FROM users;
SHOW INDEXES FROM orders;

Set up automated slow query analysis

Create a script to automatically analyze slow queries and generate performance reports.

#!/bin/bash

MySQL Performance Analysis Script
LOG_FILE="/var/log/mysql/slow-query.log"
REPORT_FILE="/var/log/mysql/performance-report-$(date +%Y%m%d-%H%M).txt"
EMAIL_RECIPIENT="admin@example.com"

echo "MySQL Performance Report - $(date)" > "$REPORT_FILE"
echo "==========================================" >> "$REPORT_FILE"
echo "" >> "$REPORT_FILE"

Check if slow query log exists and has content
if [[ -f "$LOG_FILE" && -s "$LOG_FILE" ]]; then
    echo "Top 10 Slowest Queries:" >> "$REPORT_FILE"
    echo "------------------------" >> "$REPORT_FILE"
    mysqldumpslow -s t -t 10 "$LOG_FILE" >> "$REPORT_FILE"
    
    echo "" >> "$REPORT_FILE"
    echo "Queries Not Using Indexes:" >> "$REPORT_FILE"
    echo "-------------------------" >> "$REPORT_FILE"
    mysqldumpslow -s c -t 5 "$LOG_FILE" | grep -A 10 "not using indexes" >> "$REPORT_FILE"
    
    # Advanced analysis with pt-query-digest if available
    if command -v pt-query-digest &> /dev/null; then
        echo "" >> "$REPORT_FILE"
        echo "Detailed Query Analysis:" >> "$REPORT_FILE"
        echo "------------------------" >> "$REPORT_FILE"
        pt-query-digest --limit 5 "$LOG_FILE" >> "$REPORT_FILE"
    fi
else
    echo "No slow queries found in the log file." >> "$REPORT_FILE"
fi

Check InnoDB status
echo "" >> "$REPORT_FILE"
echo "InnoDB Buffer Pool Status:" >> "$REPORT_FILE"
echo "---------------------------" >> "$REPORT_FILE"
mysql -e "SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_total', 'Innodb_buffer_pool_pages_free');" >> "$REPORT_FILE"

Check connection statistics
echo "" >> "$REPORT_FILE"
echo "Connection Statistics:" >> "$REPORT_FILE"
echo "----------------------" >> "$REPORT_FILE"
mysql -e "SHOW STATUS LIKE 'Connections'; SHOW STATUS LIKE 'Max_used_connections'; SHOW STATUS LIKE 'Threads_connected';" >> "$REPORT_FILE"

echo "Report generated: $REPORT_FILE"

Rotate slow query log to prevent it from growing too large
if [[ -f "$LOG_FILE" ]]; then
    mysql -e "FLUSH LOGS;"
    gzip "${LOG_FILE}.1" 2>/dev/null
fi

sudo chmod +x /usr/local/bin/mysql-performance-check.sh
sudo chown root:root /usr/local/bin/mysql-performance-check.sh

Schedule automated performance monitoring

Set up a systemd timer to run performance analysis automatically and catch issues early.

[Unit]
Description=MySQL Performance Analysis
After=mysql.service
Requires=mysql.service

[Service]
Type=oneshot
User=root
ExecStart=/usr/local/bin/mysql-performance-check.sh
StandardOutput=journal
StandardError=journal

[Unit]
Description=Run MySQL Performance Analysis Daily
Requires=mysql-performance.service

[Timer]
OnCalendar=daily
RandomizedDelaySec=1h
Persistent=true

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable mysql-performance.timer
sudo systemctl start mysql-performance.timer
sudo systemctl status mysql-performance.timer

Configure query cache optimization

Fine-tune query cache settings for optimal performance with frequently executed queries.

-- Check current query cache status
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

-- Monitor query cache efficiency
SELECT 
    ROUND((Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100, 2) as cache_hit_ratio,
    Qcache_hits,
    Qcache_inserts,
    Qcache_lowmem_prunes as memory_prunes,
    Qcache_free_memory as free_memory,
    Qcache_total_blocks as total_blocks
FROM 
    (SELECT VARIABLE_VALUE as Qcache_hits FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_hits') h,
    (SELECT VARIABLE_VALUE as Qcache_inserts FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_inserts') i,
    (SELECT VARIABLE_VALUE as Qcache_lowmem_prunes FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_lowmem_prunes') p,
    (SELECT VARIABLE_VALUE as Qcache_free_memory FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_free_memory') f,
    (SELECT VARIABLE_VALUE as Qcache_total_blocks FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Qcache_total_blocks') b;

-- Test queries that should be cached
SELECT SQL_CACHE COUNT(*) FROM users WHERE status = 'active';
SELECT SQL_CACHE AVG(total_amount) FROM orders WHERE status = 'completed';