MySQL Slow Query Performance

# MySQL Slow Query Performance

Symptoms

• Queries taking longer than expected
• High CPU usage on MySQL server
• Slow page load times
• Connection timeouts during peak traffic
• Application becomes unresponsive

Root Causes

1. 1.Missing indexes - Full table scans instead of index lookups
2. 2.Inefficient query design - Suboptimal JOINs or WHERE clauses
3. 3.Large result sets - Retrieving more data than needed
4. 4.Lock contention - Multiple queries competing for same resources
5. 5.Outdated statistics - Query optimizer making poor decisions
6. 6.Poor schema design - Normalization issues or wrong data types

Diagnosis Steps

Step 1: Enable Slow Query Log

```sql -- Check if slow query log is enabled SHOW VARIABLES LIKE 'slow_query_log%';

-- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; SET GLOBAL long_query_time = 2; -- Log queries over 2 seconds SET GLOBAL log_queries_not_using_indexes = 'ON'; ```

Or in configuration:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Step 2: Analyze Slow Queries

-- View slow queries from performance schema
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as executions,
    AVG_TIMER_WAIT/1000000000 as avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

Step 3: Use EXPLAIN to Analyze Query

```sql -- Basic EXPLAIN EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Extended EXPLAIN with additional info EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN ANALYZE (MySQL 8.0.18+) EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123; ```

Key EXPLAIN columns to check: - type: ALL (bad - full table scan), index, range, ref, const (best) - key: Which index is used (NULL means no index) - rows: Estimated rows examined - Extra: Using filesort, Using temporary (both are performance warnings)

Step 4: Check Table Statistics

```sql -- Show table status SHOW TABLE STATUS LIKE 'table_name';

-- Check index cardinality SHOW INDEX FROM table_name;

-- Check table size SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH / 1024 / 1024 AS data_size_mb, INDEX_LENGTH / 1024 / 1024 AS index_size_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name' ORDER BY DATA_LENGTH DESC; ```

Solutions

Solution 1: Add Appropriate Indexes

```sql -- Analyze query first EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- If type is ALL, add index -- Single column index CREATE INDEX idx_customer_id ON orders(customer_id);

-- Composite index (order matters!) CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- Covering index (includes all selected columns) CREATE INDEX idx_covering ON orders(customer_id, status, order_date, total); ```

Index Strategy Guidelines:

1. 1.Index columns in WHERE clause
2. 2.Index columns used in JOIN conditions
3. 3.Order composite indexes by selectivity (most selective first)
4. 4.Avoid over-indexing (impacts INSERT/UPDATE performance)

Solution 2: Optimize Query Structure

Before (Slow):

-- Using subquery
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

After (Fast):

-- Using JOIN
SELECT o.* 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'active';

Before (Slow):

-- SELECT * retrieves all columns
SELECT * FROM orders WHERE created_at > '2024-01-01';

After (Fast):

-- Select only needed columns
SELECT id, customer_id, total, status 
FROM orders 
WHERE created_at > '2024-01-01';

Solution 3: Optimize ORDER BY and GROUP BY

```sql -- Problem: filesort EXPLAIN SELECT * FROM orders ORDER BY created_at DESC;

-- Solution: Add index on ORDER BY column CREATE INDEX idx_created ON orders(created_at DESC);

-- Problem: Using temporary for GROUP BY EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;

-- Solution: Add index or use loose index scan CREATE INDEX idx_status ON orders(status); ```

Solution 4: Use Query Hints (When Necessary)

```sql -- Force index usage SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 123;

-- Ignore specific index SELECT * FROM orders IGNORE INDEX (idx_old) WHERE customer_id = 123;

-- Straight join (force join order) SELECT STRAIGHT_JOIN o.*, c.name FROM orders o INNER JOIN customers c ON o.customer_id = c.id; ```

Solution 5: Partition Large Tables

-- Partition by range
CREATE TABLE orders (
    id BIGINT,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Solution 6: Update Table Statistics

```sql -- Analyze table to update statistics ANALYZE TABLE orders;

-- Analyze multiple tables ANALYZE TABLE orders, customers, products;

-- Check when last analyzed SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database_name'; ```

Solution 7: Optimize Configuration

```ini # /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] # InnoDB buffer pool (70-80% of available RAM for dedicated server) innodb_buffer_pool_size = 4G

# Query cache (MySQL 5.7, removed in 8.0) query_cache_type = 1 query_cache_size = 256M

# Join buffer size join_buffer_size = 256K

# Sort buffer size sort_buffer_size = 256K

# InnoDB log file size innodb_log_file_size = 512M

# Enable slow query log slow_query_log = 1 long_query_time = 2 ```

Solution 8: Use Read Replicas

For read-heavy workloads:

```sql -- On master -- Write operations go here

-- On replica -- Read operations distributed here SET GLOBAL read_only = ON; ```

Application connection:

```php // PHP example $writeDb = new PDO('mysql:host=master.local;dbname=app', 'user', 'pass'); $readDb = new PDO('mysql:host=replica.local;dbname=app', 'user', 'pass');

// Write to master $writeDb->exec("INSERT INTO orders (...) VALUES (...)");

// Read from replica $stmt = $readDb->query("SELECT * FROM orders WHERE ..."); ```

Monitoring and Prevention

1. Regular Performance Analysis

```bash #!/bin/bash # Weekly performance report

# Top 10 slowest queries mysqldumpslow -ts 10 /var/log/mysql/mysql-slow.log

# Table sizes mysql -e "SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024,2) as 'Size(MB)' FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'database' ORDER BY DATA_LENGTH DESC LIMIT 10;" ```

2. Set Up Alerts

-- Create event to check query performance
CREATE EVENT monitor_slow_queries
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    -- Log if too many slow queries in past hour
    INSERT INTO admin_alerts (alert_type, message)
    SELECT 'slow_queries', CONCAT('High slow query count: ', COUNT(*))
    FROM performance_schema.events_statements_summary_by_digest
    WHERE AVG_TIMER_WAIT > 5000000000  -- 5 seconds
    AND COUNT_STAR > 10;
END;

3. Regular Maintenance

```sql -- Schedule monthly optimization -- For InnoDB tables ALTER TABLE large_table ENGINE=InnoDB;

-- Optimize tables OPTIMIZE TABLE orders, customers, products;

-- Update statistics ANALYZE TABLE orders, customers, products; ```

Related Errors

• [MySQL Too Many Connections](./fix-mysql-too-many-connections)
• [MySQL Disk Full](./fix-mysql-disk-full)