MySQL Performance Tuning for Developers

Learn how to optimize your MySQL database for maximum performance in high-traffic PHP applications.

Understanding Query Performance

Using EXPLAIN

sql

1EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

Key columns to watch:

type: Join type (ALL is bad, const/eq_ref is good)

possible_keys: Indexes that could be used

key: Index actually used

rows: Estimated rows to examine

Indexing Strategies

Single Column Index

sql

1CREATE INDEX idx_email ON users(email);

Composite Index

sql

1CREATE INDEX idx_user_status ON users(status, created_at);

Order matters! Put the most selective column first.

Covering Index

sql

1CREATE INDEX idx_user_lookup ON users(email, name, status);
2
3-- This query uses only the index (no table access)
4SELECT name, status FROM users WHERE email = 'john@example.com';

Query Optimization

Avoid SELECT *

php

1// BAD - Retrieves unnecessary data
2$users = $db->query('SELECT * FROM users');
3
4// GOOD - Only get what you need
5$users = $db->query('SELECT id, name, email FROM users');

Use LIMIT

php

1// Always paginate large result sets
2$page = $_GET['page'] ?? 1;
3$perPage = 20;
4$offset = ($page - 1) * $perPage;
5
6$users = $db->query(
7    'SELECT id, name, email FROM users LIMIT ? OFFSET ?',
8    [$perPage, $offset]
9);

Optimize JOINs

sql

1-- BAD - N+1 query problem
2SELECT * FROM posts;
3-- Then for each post:
4SELECT * FROM users WHERE id = ?;
5
6-- GOOD - Single query with JOIN
7SELECT 
8    p.id, p.title, p.content,
9    u.name as author_name, u.email as author_email
10FROM posts p
11INNER JOIN users u ON p.user_id = u.id;

PHP Implementation

Efficient Data Loading

php

1class PostRepository
2{
3    public function __construct(private Database $db) {}
4    
5    public function findWithAuthor(int $id): ?array
6    {
7        $results = $this->db->query(
8            'SELECT 
9                p.id, p.title, p.content, p.created_at,
10                u.id as author_id, u.name as author_name
11            FROM posts p
12            INNER JOIN users u ON p.user_id = u.id
13            WHERE p.id = ?
14            LIMIT 1',
15            [$id]
16        );
17        
18        return $results[0] ?? null;
19    }
20    
21    public function findRecent(int $limit = 10): array
22    {
23        return $this->db->query(
24            'SELECT p.*, u.name as author_name
25            FROM posts p
26            INNER JOIN users u ON p.user_id = u.id
27            ORDER BY p.created_at DESC
28            LIMIT ?',
29            [$limit]
30        );
31    }
32}

Caching Strategies

Query Result Caching

php

1class CachedPostRepository
2{
3    public function __construct(
4        private PostRepository $repository,
5        private CacheInterface $cache
6    ) {}
7    
8    public function findRecent(int $limit = 10): array
9    {
10        $cacheKey = "posts:recent:$limit";
11        
12        $cached = $this->cache->get($cacheKey);
13        if ($cached !== null) {
14            return $cached;
15        }
16        
17        $posts = $this->repository->findRecent($limit);
18        $this->cache->set($cacheKey, $posts, 300); // 5 minutes
19        
20        return $posts;
21    }
22}

Database Schema Optimization

Proper Data Types

sql

1-- BAD
2CREATE TABLE users (
3    id VARCHAR(255),
4    age VARCHAR(10),
5    is_active VARCHAR(5)
6);
7
8-- GOOD
9CREATE TABLE users (
10    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
11    age TINYINT UNSIGNED,
12    is_active BOOLEAN DEFAULT TRUE
13);

Normalization vs Denormalization

Normalized (good for writes):

sql

1CREATE TABLE posts (
2    id INT PRIMARY KEY,
3    user_id INT,
4    title VARCHAR(255)
5);
6
7CREATE TABLE users (
8    id INT PRIMARY KEY,
9    name VARCHAR(100)
10);

Denormalized (good for reads):

sql

1CREATE TABLE posts (
2    id INT PRIMARY KEY,
3    user_id INT,
4    user_name VARCHAR(100), -- Duplicated for performance
5    title VARCHAR(255)
6);

Monitoring Performance

Slow Query Log

Enable in my.cnf:

ini

1slow_query_log = 1
2slow_query_log_file = /var/log/mysql/slow.log
3long_query_time = 1

PHP Query Profiling

php

1class ProfilingDatabase extends Database
2{
3    private array $queries = [];
4    
5    public function query(string $sql, array $params = []): array
6    {
7        $start = microtime(true);
8        $result = parent::query($sql, $params);
9        $duration = microtime(true) - $start;
10        
11        $this->queries[] = [
12            'sql' => $sql,
13            'params' => $params,
14            'duration' => $duration
15        ];
16        
17        if ($duration > 0.1) {
18            error_log("Slow query ($duration s): $sql");
19        }
20        
21        return $result;
22    }
23    
24    public function getQueryLog(): array
25    {
26        return $this->queries;
27    }
28}

Best Practices

Index foreign keys - Always index columns used in JOINs

Use appropriate data types - Smaller is faster

Avoid OR in WHERE - Use UNION instead

Batch operations - Insert multiple rows at once

Use connection pooling - Reuse database connections

Regular maintenance - OPTIMIZE TABLE monthly

Conclusion

Database performance is critical for application scalability. Profile your queries, add appropriate indexes, and cache aggressively for the best results!