MySQL Performance Tuning for Complex PHP Applications

MySQL Configuration Optimization

Memory Configuration

Proper memory allocation is crucial for MySQL performance:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# InnoDB Buffer Pool (most important setting)
innodb_buffer_pool_size = 16G  # 70-80% of available RAM
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
# Query cache (disabled in MySQL 8.0+)
query_cache_type = 0
query_cache_size = 0
# Table cache
table_open_cache = 4000
table_definition_cache = 2000
# Connection settings
max_connections = 200
max_user_connections = 180
thread_cache_size = 16
# 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

InnoDB Optimization

# InnoDB specific settings
innodb_flush_log_at_trx_commit = 2  # Better performance, slight durability trade-off
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
# Deadlock detection
innodb_deadlock_detect = 1
innodb_print_all_deadlocks = 1
# Parallel threads
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4

Query Optimization Strategies

Index Design

Proper indexing is fundamental to query performance:

-- Compound indexes for complex WHERE clauses
CREATE INDEX idx_user_orders ON orders (user_id, status, created_at);
-- Covering indexes to avoid table lookups
CREATE INDEX idx_product_details ON products (category_id, status, price, name);
-- Partial indexes for filtered queries
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';
-- Functional indexes for computed columns
CREATE INDEX idx_user_full_name ON users ((CONCAT(first_name, ' ', last_name)));
-- JSON indexes for JSON column queries
CREATE INDEX idx_user_preferences ON users ((JSON_EXTRACT(preferences, '$.language')));

Query Rewriting

Transform slow queries into efficient ones:

-- Slow: Using OR conditions
SELECT * FROM products
WHERE category_id = 1 OR category_id = 2 OR category_id = 3;
-- Fast: Using IN clause
SELECT * FROM products
WHERE category_id IN (1, 2, 3);
-- Slow: Using NOT IN with NULL values
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM banned_users);
-- Fast: Using LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN banned_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;
-- Slow: Using OFFSET for pagination
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;
-- Fast: Using cursor-based pagination
SELECT * FROM products
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

PHP Database Optimization

Connection Optimization

<?php
declare(strict_types=1);
namespace AppDatabaseOptimization;
use AppValueObjects{DatabaseConfig, QueryResult, CacheKey, CacheTTL};
use AppExceptions{DatabaseConnectionException, QueryExecutionException};
use AppContracts{CacheInterface, QueryMetricsInterface};
use PDO;
use PDOException;
use PsrLogLoggerInterface;
final readonly class DatabaseOptimizer
{
private PDO $connection;
public function __construct(
DatabaseConfig $config,
private CacheInterface $cache,
private QueryMetricsInterface $metrics,
private LoggerInterface $logger,
) {
$this->connection = $this->createOptimizedConnection($config);
}
private function createOptimizedConnection(DatabaseConfig $config): PDO
{
try {
return new PDO(
$config->dsn,
$config->username,
$config->password,
[
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => implode(';', [
'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci',
'SET SESSION sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"',
'SET SESSION time_zone="+00:00"',
'SET SESSION group_concat_max_len=1000000',
'SET SESSION optimizer_switch="mrr=on,mrr_cost_based=on"',
])
]
);
} catch (PDOException $e) {
throw new DatabaseConnectionException(
"Failed to connect to database: {$e->getMessage()}",
previous: $e
);
}
}
public function executeQuery(string $sql, array $params = []): QueryResult
{
$startTime = hrtime(true);
try {
$stmt = $this->connection->prepare($sql);
$stmt->execute($params);
$data = $stmt->fetchAll();
$executionTime = hrtime(true) - $startTime;
$this->metrics->recordQuery($sql, $params, $executionTime);
return new QueryResult(
data: $data,
executionTime: $executionTime,
rowCount: $stmt->rowCount()
);
} catch (PDOException $e) {
$this->logger->error('Query execution failed', [
'sql' => $sql,
'params' => $params,
'error' => $e->getMessage(),
]);
throw new QueryExecutionException(
"Query execution failed: {$e->getMessage()}",
previous: $e
);
}
}
public function executeQueryWithCache(
string $sql,
array $params = [],
?CacheTTL $ttl = null
): QueryResult {
$cacheKey = CacheKey::forQuery($sql, $params);
// Check cache first
$cached = $this->cache->get($cacheKey);
if ($cached !== null) {
return $cached;
}
$result = $this->executeQuery($sql, $params);
$this->cache->set($cacheKey, $result, $ttl ?? new CacheTTL(300));
return $result;
}
public function transaction(callable $callback): mixed
{
$this->connection->beginTransaction();
try {
$result = $callback($this->connection);
$this->connection->commit();
return $result;
} catch (Throwable $e) {
$this->connection->rollBack();
throw $e;
}
}
}

Prepared Statement Optimization

<?php
declare(strict_types=1);
namespace AppDatabaseStatements;
use AppValueObjects{SqlStatement, BatchResult};
use AppExceptions{StatementExecutionException, BatchExecutionException};
use PDO;
use PDOStatement;
use PDOException;
use PsrLogLoggerInterface;
use WeakMap;
final class PreparedStatementPool
{
/** @var array<string, PDOStatement> */
private array $statements = [];
private readonly WeakMap $statementMetadata;
public function __construct(
private readonly PDO $connection,
private readonly LoggerInterface $logger,
private readonly int $maxStatements = 1000,
) {
$this->statementMetadata = new WeakMap();
}
public function getStatement(SqlStatement $sql): PDOStatement
{
$key = $sql->getHash();
if (!isset($this->statements[$key])) {
if (count($this->statements) >= $this->maxStatements) {
$this->evictOldestStatement();
}
try {
$this->statements[$key] = $this->connection->prepare($sql->value);
$this->statementMetadata[$this->statements[$key]] = [
'created_at' => time(),
'usage_count' => 0,
];
} catch (PDOException $e) {
throw new StatementExecutionException(
"Failed to prepare statement: {$e->getMessage()}",
previous: $e
);
}
}
$stmt = $this->statements[$key];
$metadata = $this->statementMetadata[$stmt];
$metadata['usage_count']++;
$this->statementMetadata[$stmt] = $metadata;
return $stmt;
}
public function executeStatement(SqlStatement $sql, array $params = []): array
{
$stmt = $this->getStatement($sql);
try {
$stmt->execute($params);
return $stmt->fetchAll();
} catch (PDOException $e) {
$this->logger->error('Statement execution failed', [
'sql' => $sql->value,
'params' => $params,
'error' => $e->getMessage(),
]);
throw new StatementExecutionException(
"Statement execution failed: {$e->getMessage()}",
previous: $e
);
}
}
public function executeBatch(SqlStatement $sql, array $batchParams): BatchResult
{
$stmt = $this->getStatement($sql);
$affected = 0;
$errors = [];
$this->connection->beginTransaction();
try {
foreach ($batchParams as $index => $params) {
try {
$stmt->execute($params);
$affected += $stmt->rowCount();
} catch (PDOException $e) {
$errors[$index] = $e->getMessage();
if (count($errors) > 10) { // Fail fast after too many errors
throw new BatchExecutionException(
"Too many errors in batch execution",
$errors
);
}
}
}
if (!empty($errors)) {
$this->connection->rollBack();
throw new BatchExecutionException(
"Batch execution failed with errors",
$errors
);
}
$this->connection->commit();
return new BatchResult(
affectedRows: $affected,
processedCount: count($batchParams),
errors: $errors
);
} catch (Throwable $e) {
$this->connection->rollBack();
throw $e;
}
}
private function evictOldestStatement(): void
{
$oldestKey = null;
$oldestTime = PHP_INT_MAX;
foreach ($this->statements as $key => $stmt) {
$metadata = $this->statementMetadata[$stmt];
if ($metadata['created_at'] < $oldestTime) {
$oldestTime = $metadata['created_at'];
$oldestKey = $key;
}
}
if ($oldestKey !== null) {
unset($this->statements[$oldestKey]);
}
}
public function getPoolStats(): array
{
$stats = [
'total_statements' => count($this->statements),
'max_statements' => $this->maxStatements,
'usage_stats' => [],
];
foreach ($this->statements as $key => $stmt) {
$metadata = $this->statementMetadata[$stmt];
$stats['usage_stats'][$key] = $metadata;
}
return $stats;
}
}

Complex Query Optimization

Subquery Optimization

-- Slow: Correlated subquery
SELECT u.*,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u
WHERE u.status = 'active';
-- Fast: LEFT JOIN with GROUP BY
SELECT u.*, COALESCE(o.order_count, 0) as order_count
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id
WHERE u.status = 'active';
-- Slow: IN subquery with large result set
SELECT * FROM products
WHERE id IN (
SELECT product_id FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE status = 'completed')
);
-- Fast: EXISTS with proper indexing
SELECT p.* FROM products p
WHERE EXISTS (
SELECT 1 FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE oi.product_id = p.id AND o.status = 'completed'
);

Aggregation Optimization

-- Slow: Multiple aggregations in separate queries
$totalOrders = $pdo->query("SELECT COUNT(*) FROM orders")->fetchColumn();
$totalRevenue = $pdo->query("SELECT SUM(total) FROM orders")->fetchColumn();
$avgOrderValue = $pdo->query("SELECT AVG(total) FROM orders")->fetchColumn();
-- Fast: Single query with multiple aggregations
$sql = "SELECT
COUNT(*) as total_orders,
SUM(total) as total_revenue,
AVG(total) as avg_order_value
FROM orders";
$stats = $pdo->query($sql)->fetch();
-- Optimized aggregation with filtering
SELECT
DATE(created_at) as date,
COUNT(*) as order_count,
SUM(total) as revenue,
AVG(total) as avg_value
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;

Performance Monitoring

Slow Query Log Analysis

<?php
declare(strict_types=1);
namespace AppDatabaseMonitoring;
use PDO;
final class SlowQueryAnalyzer
{
public function __construct(
private readonly PDO $pdo
) {}
public function enableSlowQueryLog(): void
{
$this->pdo->exec("SET GLOBAL slow_query_log = 'ON'");
$this->pdo->exec("SET GLOBAL long_query_time = 1");
$this->pdo->exec("SET GLOBAL log_queries_not_using_indexes = 'ON'");
}
public function getSlowQueries(): array
{
$sql = "SELECT
sql_text,
exec_count,
total_latency,
avg_latency,
lock_latency,
rows_sent,
rows_examined
FROM sys.statement_analysis
WHERE avg_latency > 1000000  -- 1 second
ORDER BY total_latency DESC
LIMIT 20";
return $this->pdo->query($sql)->fetchAll();
}
public function getTableScans(): array
{
$sql = "SELECT
object_name,
count_read,
avg_read_latency,
count_write,
avg_write_latency
FROM sys.table_io_waits_summary_by_table
ORDER BY count_read DESC
LIMIT 20";
return $this->pdo->query($sql)->fetchAll();
}
}

Real-time Performance Monitoring

class MySQLMonitor {
private $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
}
public function getPerformanceMetrics(): array {
$sql = "SHOW GLOBAL STATUS WHERE Variable_name IN (
'Connections',
'Threads_running',
'Questions',
'Slow_queries',
'Opens',
'Flush_commands',
'Open_tables',
'Queries_per_second_avg',
'Innodb_buffer_pool_read_requests',
'Innodb_buffer_pool_reads',
'Innodb_buffer_pool_wait_free',
'Innodb_log_waits',
'Innodb_rows_read',
'Innodb_rows_inserted',
'Innodb_rows_updated',
'Innodb_rows_deleted'
)";
$result = $this->pdo->query($sql)->fetchAll();
$metrics = [];
foreach ($result as $row) {
$metrics[$row['Variable_name']] = $row['Value'];
}
// Calculate buffer pool hit ratio
$reads = $metrics['Innodb_buffer_pool_reads'];
$requests = $metrics['Innodb_buffer_pool_read_requests'];
$metrics['buffer_pool_hit_ratio'] = (($requests - $reads) / $requests) * 100;
return $metrics;
}
public function getActiveConnections(): array {
$sql = "SELECT
id,
user,
host,
db,
command,
time,
state,
info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC";
return $this->pdo->query($sql)->fetchAll();
}
public function getInnoDBStatus(): array {
$sql = "SHOW ENGINE INNODB STATUS";
$result = $this->pdo->query($sql)->fetch();
return $this->parseInnoDBStatus($result['Status']);
}
private function parseInnoDBStatus(string $status): array {
$metrics = [];
// Parse buffer pool info
if (preg_match('/Buffer pool sizes+(d+)/', $status, $matches)) {
$metrics['buffer_pool_size'] = $matches[1];
}
// Parse log sequence number
if (preg_match('/Log sequence numbers+(d+)/', $status, $matches)) {
$metrics['log_sequence_number'] = $matches[1];
}
// Parse pending reads/writes
if (preg_match('/Pending normal aio reads:s+(d+)/', $status, $matches)) {
$metrics['pending_reads'] = $matches[1];
}
return $metrics;
}
}

Partitioning Strategies

Range Partitioning

-- Partition by date for time-series data
CREATE TABLE order_history (
id INT AUTO_INCREMENT,
user_id INT,
total DECIMAL(10,2),
created_at TIMESTAMP,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
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
);
-- Hash partitioning for load distribution
CREATE TABLE user_sessions (
id INT AUTO_INCREMENT,
user_id INT,
session_data TEXT,
created_at TIMESTAMP,
PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 8;

Partition Pruning

class PartitionManager {
private $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
}
public function addPartition(string $table, string $partition, string $value): void {
$sql = "ALTER TABLE {$table} ADD PARTITION (
PARTITION {$partition} VALUES LESS THAN ({$value})
)";
$this->pdo->exec($sql);
}
public function dropOldPartitions(string $table, int $keepDays = 90): void {
$cutoffDate = date('Y-m-d', strtotime("-{$keepDays} days"));
$sql = "SELECT
partition_name,
partition_description
FROM information_schema.partitions
WHERE table_name = ? AND partition_name IS NOT NULL
ORDER BY partition_ordinal_position";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([$table]);
$partitions = $stmt->fetchAll();
foreach ($partitions as $partition) {
$partitionDate = $partition['partition_description'];
if ($partitionDate < $cutoffDate) {
$this->dropPartition($table, $partition['partition_name']);
}
}
}
private function dropPartition(string $table, string $partition): void {
$sql = "ALTER TABLE {$table} DROP PARTITION {$partition}";
$this->pdo->exec($sql);
}
}

Advanced Optimization Techniques

Query Result Caching

class QueryResultCache {
private $redis;
private $defaultTTL = 300;
public function __construct(Redis $redis) {
$this->redis = $redis;
}
public function getCachedQuery(string $sql, array $params = [], int $ttl = null): ?array {
$cacheKey = $this->generateCacheKey($sql, $params);
$cached = $this->redis->get($cacheKey);
if ($cached !== false) {
return json_decode($cached, true);
}
return null;
}
public function setCachedQuery(string $sql, array $params, array $result, int $ttl = null): void {
$cacheKey = $this->generateCacheKey($sql, $params);
$ttl = $ttl ?? $this->defaultTTL;
$this->redis->setex($cacheKey, $ttl, json_encode($result));
}
public function invalidateQueryCache(string $table): void {
$pattern = "query:*:{$table}:*";
$keys = $this->redis->keys($pattern);
if (!empty($keys)) {
$this->redis->del($keys);
}
}
private function generateCacheKey(string $sql, array $params): string {
$normalized = $this->normalizeQuery($sql);
$tables = $this->extractTables($normalized);
return 'query:' . md5($sql . serialize($params)) . ':' . implode(',', $tables);
}
private function normalizeQuery(string $sql): string {
// Remove extra whitespace and normalize case
return preg_replace('/s+/', ' ', strtolower(trim($sql)));
}
private function extractTables(string $sql): array {
preg_match_all('/(?:from|join|update|into)s+([a-zA-Z_]w*)/i', $sql, $matches);
return array_unique($matches[1]);
}
}

Database Sharding

class DatabaseShardManager {
private $shards = [];
private $shardCount;
public function __construct(array $shardConfigs) {
$this->shardCount = count($shardConfigs);
foreach ($shardConfigs as $index => $config) {
$this->shards[$index] = new PDO(
$config['dsn'],
$config['username'],
$config['password'],
[PDO::ATTR_PERSISTENT => true]
);
}
}
public function getShardForUser(int $userId): PDO {
$shardIndex = $userId % $this->shardCount;
return $this->shards[$shardIndex];
}
public function executeOnAllShards(string $sql, array $params = []): array {
$results = [];
foreach ($this->shards as $index => $pdo) {
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$results[$index] = $stmt->fetchAll();
}
return $results;
}
public function executeOnShard(int $shardIndex, string $sql, array $params = []): array {
$pdo = $this->shards[$shardIndex];
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll();
}
}

Backup and Recovery Optimization

Hot Backup Strategy

class HotBackupManager {
private $pdo;
private $backupPath;
public function __construct(PDO $pdo, string $backupPath) {
$this->pdo = $pdo;
$this->backupPath = $backupPath;
}
public function createIncrementalBackup(): void {
// Get current binary log position
$sql = "SHOW MASTER STATUS";
$status = $this->pdo->query($sql)->fetch();
$backupInfo = [
'timestamp' => date('Y-m-d H:i:s'),
'log_file' => $status['File'],
'log_position' => $status['Position'],
'type' => 'incremental'
];
// Create backup using xtrabackup
$command = sprintf(
'xtrabackup --backup --target-dir=%s --incremental-basedir=%s',
$this->backupPath . '/incremental_' . date('Y-m-d_H-i-s'),
$this->getLastFullBackup()
);
exec($command, $output, $returnCode);
if ($returnCode !== 0) {
throw new Exception('Backup failed: ' . implode("
", $output));
}
// Save backup metadata
file_put_contents(
$this->backupPath . '/backup_info.json',
json_encode($backupInfo)
);
}
public function createFullBackup(): void {
$backupDir = $this->backupPath . '/full_' . date('Y-m-d_H-i-s');
$command = sprintf(
'xtrabackup --backup --target-dir=%s',
$backupDir
);
exec($command, $output, $returnCode);
if ($returnCode !== 0) {
throw new Exception('Full backup failed: ' . implode("
", $output));
}
// Prepare the backup
$prepareCommand = sprintf('xtrabackup --prepare --target-dir=%s', $backupDir);
exec($prepareCommand);
}
private function getLastFullBackup(): string {
$backups = glob($this->backupPath . '/full_*');
if (empty($backups)) {
throw new Exception('No full backup found');
}
// Sort by modification time, get the latest
usort($backups, function($a, $b) {
return filemtime($b) - filemtime($a);
});
return $backups[0];
}
}

Common Performance Pitfalls

• Over-normalization: Sometimes denormalization improves performance
• Missing indexes: Every WHERE, JOIN, and ORDER BY clause should be indexed
• Too many indexes: Indexes slow down writes, find the right balance
• N+1 queries: Use JOINs or batch queries instead
• Large result sets: Use LIMIT and pagination
• Inefficient GROUP BY: Use covering indexes for grouped queries

Best Practices Summary

• Monitor first: Use slow query log and performance schema
• Index strategically: Focus on high-impact queries
• Optimize configuration: Tune MySQL settings for your workload
• Cache intelligently: Use query result caching for expensive queries
• Partition large tables: Improve query performance and maintenance
• Use prepared statements: Better performance and security
• Regular maintenance: Optimize tables and update statistics

Database optimization is an ongoing process. Start with the biggest bottlenecks, measure the impact of changes, and continuously monitor performance. Remember that the best optimization strategy depends on your specific workload and data patterns.