10 MySQL Performance Optimization Techniques Every PHP Developer Must Know

Slow MySQL queries can cripple your web application. This guide covers indexing, query optimization, table design, and caching techniques to make your database run 10x faster.
10 MySQL Performance Optimization Techniques Every PHP Developer Must Know

A slow database query can make your entire PHP application feel sluggish. Even a 500ms query running 100 times per page load adds 50 seconds of wait time. I've compiled the 10 most effective MySQL optimization techniques that have helped me speed up real projects.

Use Indexes Properly Indexes are the single most important optimization. Add indexes to columns used in WHERE, JOIN, and ORDER BY:

-- Check existing indexes
SHOW INDEX FROM users;

-- Add index to frequently searched column
ALTER TABLE users ADD INDEX idx_email (email);

-- Composite index for multiple columns
ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at);

EXPLAIN Your Queries Always use EXPLAIN to understand how MySQL executes your query:

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

Look for:
type=ALL → full table scan (BAD)
type=ref or type=range → index used (GOOD)

Select Only What You Need

// BAD - fetches all columns
$users = $db->query("SELECT * FROM users WHERE active = 1")->fetchAll();

// GOOD - fetch only needed columns
$users = $db->query("SELECT id, name, email FROM users WHERE active = 1")->fetchAll();

Solve the N+1 Query Problem

// BAD: N+1 queries
$users = $db->query("SELECT * FROM users")->fetchAll();
foreach ($users as $user) {
    $posts = $db->query("SELECT * FROM posts WHERE user_id = {$user['id']}")->fetchAll();
}

// GOOD: Single JOIN query
$results = $db->query("
    SELECT u.id, u.name, p.title
    FROM users u
    LEFT JOIN posts p ON p.user_id = u.id
")->fetchAll();

Always Paginate Large Results

-- Page 1 (20 results)
SELECT * FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- For very large tables, use cursor pagination
SELECT * FROM products
WHERE id > :last_id
ORDER BY id ASC
LIMIT 20;

Avoid Functions on Indexed Columns

-- BAD: function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- GOOD: range query uses index
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';

Use Proper Data Types

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,   -- not FLOAT for money
  is_active TINYINT(1) DEFAULT 1, -- not VARCHAR for booleans
  description TEXT                 -- only TEXT when needed
);

Optimize JOINs

-- Ensure JOIN columns are indexed
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Prefer INNER JOIN over LEFT JOIN when possible
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Cache Query Results in PHP

function getCachedQuery($db, $key, $sql, $params = [], $ttl = 300) {
    $cacheFile = sys_get_temp_dir() . '/db_' . md5($key) . '.cache';

    if (file_exists($cacheFile) && (time() - filemtime($cacheFile)) < $ttl) {
        return unserialize(file_get_contents($cacheFile));
    }

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    file_put_contents($cacheFile, serialize($result));
    return $result;
}

Monitor Slow Queries

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Quick Checklist
[ ] Add indexes to WHERE, JOIN, ORDER BY columns
[ ] Run EXPLAIN on slow queries
[ ] Replace SELECT * with specific columns
[ ] Fix N+1 queries with JOINs
[ ] Add LIMIT to all queries
[ ] Use range queries instead of functions on indexed columns
[ ] Use correct data types
[ ] Cache repeated queries

[ ] Monitor slow query log

I've also published detailed guides on Web Security, REST API with PHP, CSS Flexbox, and JavaScript Async/Await on my dev tools site.
Check out the free SQL Formatter tool at tsmtools.in/devtools.html — useful for cleaning up complex queries before running them.
What's your biggest MySQL performance challenge? Drop a comment below! 👇