MySQL Performance Optimization for Laravel

The Database Is Usually the Bottleneck

Most performance issues trace back to the database. Proper MySQL configuration and query optimization have the biggest impact on application speed.

Key Configuration Settings

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# InnoDB Settings
innodb_buffer_pool_size = 4G  # 70-80% of available RAM
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2  # Trade durability for speed
innodb_flush_method = O_DIRECT

# Query Cache (MySQL 5.7)
query_cache_type = 0  # Disabled - often counterproductive

# Connections
max_connections = 200
wait_timeout = 28800
interactive_timeout = 28800

# Temp Tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Analyzing Slow Queries

# Enable slow query log temporarily
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

# Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow.log

# Or use EXPLAIN on specific queries
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

Index Optimization

// Add indexes for common query patterns
Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');
    $table->index(['status', 'created_at']);
    $table->index('total');
});

// Find missing indexes
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Connection Pooling

// Use persistent connections
// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'options' => [
        PDO::ATTR_PERSISTENT => true,
    ],
],

Monitoring

# Key metrics to watch
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

# Buffer pool hit ratio should be > 99%
# Ratio = read_requests / (read_requests + reads)

Conclusion

MySQL performance requires proper buffer pool sizing, query optimization, indexing strategy, and monitoring. Profile your actual workload and tune accordingly.