MySQL Performance Optimization: 20 Expert Tips

Why MySQL Performance Optimization Matters

A single slow query can bring down an entire application. Database performance is often the biggest bottleneck in web applications, and MySQL powers over 40 percent of all websites. Whether you are running a small blog or a high-traffic SaaS platform, these 20 expert tips will help you squeeze maximum performance from your MySQL installation.

Tip 1: Master EXPLAIN and EXPLAIN ANALYZE

Before optimizing any query, you must understand how MySQL executes it. The EXPLAIN statement shows the query execution plan.

-- Basic EXPLAIN
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- EXPLAIN with more detail (MySQL 8.0+)
EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

-- JSON format for detailed output
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

Reading EXPLAIN Output

Tip 2: Choose the Right Index Type

B-Tree Indexes (Default)

Best for equality, range queries, and sorting. This is what you want 95 percent of the time.

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Good for: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM users WHERE created_at BETWEEN '2025-01-01' AND '2025-06-01';
SELECT * FROM users WHERE email LIKE 'alice%'; -- uses index
SELECT * FROM users WHERE email LIKE '%alice%'; -- CANNOT use index

Full-Text Indexes

-- For text searching
ALTER TABLE posts ADD FULLTEXT INDEX ft_posts_content (title, content);

-- Usage
SELECT * FROM posts
WHERE MATCH(title, content) AGAINST('mysql performance' IN BOOLEAN MODE);

SELECT *, MATCH(title, content) AGAINST('mysql performance') AS relevance
FROM posts
WHERE MATCH(title, content) AGAINST('mysql performance')
ORDER BY relevance DESC;

Hash Indexes (Memory Engine)

-- Only for exact equality (Memory/HEAP engine only)
CREATE TABLE cache (
  cache_key VARCHAR(255) NOT NULL,
  cache_value TEXT,
  INDEX USING HASH (cache_key)
) ENGINE = MEMORY;

Spatial Indexes

-- For geographic data
ALTER TABLE locations ADD SPATIAL INDEX sp_locations_coords (coordinates);

SELECT name FROM locations
WHERE ST_Contains(
  ST_GeomFromText('POLYGON((-74.1 40.6, -73.9 40.6, -73.9 40.8, -74.1 40.8, -74.1 40.6))'),
  coordinates
);

Tip 3: Composite Index Column Ordering

Column order in composite indexes matters enormously. Follow the Equality–Sort–Range (ESR) rule:

-- Query pattern
SELECT * FROM orders
WHERE status = 'shipped'        -- Equality
  AND customer_id = 42           -- Equality
  AND created_at > '2025-01-01' -- Range
ORDER BY total DESC;             -- Sort

-- Optimal index: equality columns first, then sort, then range
CREATE INDEX idx_orders_optimal ON orders(status, customer_id, total, created_at);

-- BAD: Range column before sort column
CREATE INDEX idx_orders_bad ON orders(status, customer_id, created_at, total);

Tip 4: Covering Indexes

A covering index contains all columns needed by a query, so MySQL never reads the actual table rows. You will see Using index in the EXPLAIN output.

-- This query needs id, name, and email
SELECT id, name, email FROM users WHERE status = 'active';

-- Covering index — includes ALL columns the query needs
CREATE INDEX idx_users_covering ON users(status, id, name, email);

-- EXPLAIN will show "Using index" — no table lookup needed
EXPLAIN SELECT id, name, email FROM users WHERE status = 'active';

Tip 5: Enable and Analyze the Slow Query Log

-- Check current settings
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries taking > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

# Analyze slow query log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Sort by total time (-s t), show top 10 (-t 10)
# Other sort options: -s c (count), -s l (lock time), -s r (rows)

# Using pt-query-digest (Percona Toolkit — more detailed)
pt-query-digest /var/log/mysql/slow-query.log

# Install Percona Toolkit
sudo apt install percona-toolkit

Tip 6: Query Profiling

-- Enable profiling for the session
SET profiling = 1;

-- Run your query
SELECT * FROM orders WHERE customer_id = 42;

-- View profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- Detailed profile with CPU and block I/O
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

-- Performance Schema (modern alternative)
SELECT event_name, timer_wait/1000000000 AS duration_ms
FROM performance_schema.events_statements_history
ORDER BY timer_wait DESC
LIMIT 10;

Tip 7: JOIN Optimization

-- ALWAYS index the JOIN columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Prefer explicit JOIN over implicit
-- GOOD
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- BAD (implicit join)
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND o.status = 'completed';

-- For MySQL 8.0+, check join algorithms
EXPLAIN FORMAT=TREE SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Look for: "Nested loop" vs "Hash join"

Tip 8: Subquery vs JOIN Performance

-- SLOW: Correlated subquery (executes once per row)
SELECT name, (
  SELECT COUNT(*) FROM orders WHERE user_id = u.id
) AS order_count
FROM users u;

-- FAST: Rewrite as JOIN
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- EXISTS is often faster than IN for large datasets
-- SLOW
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- FAST
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
);

Tip 9: LIKE Query Optimization

-- GOOD: Prefix search uses B-Tree index
SELECT * FROM users WHERE email LIKE 'alice%';

-- BAD: Leading wildcard cannot use index — full scan
SELECT * FROM users WHERE email LIKE '%alice%';

-- SOLUTION 1: Full-text index for contains searches
ALTER TABLE users ADD FULLTEXT INDEX ft_users_email (email);
SELECT * FROM users WHERE MATCH(email) AGAINST('alice' IN BOOLEAN MODE);

-- SOLUTION 2: Generated column for suffix search
ALTER TABLE users ADD COLUMN email_reversed VARCHAR(255)
  GENERATED ALWAYS AS (REVERSE(email)) STORED;
CREATE INDEX idx_email_reversed ON users(email_reversed);
-- Search for emails ending in @gmail.com
SELECT * FROM users WHERE email_reversed LIKE REVERSE('%@gmail.com');

Tip 10: Pagination Optimization

-- SLOW: Offset-based pagination (scans and discards rows)
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 100000;
-- MySQL scans 100,010 rows and discards 100,000!

-- FAST: Cursor-based (keyset) pagination
-- Page 1
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 10;

-- Next page: pass the last created_at and id from the previous page
SELECT * FROM posts
WHERE (created_at, id) < ('2025-05-01 12:00:00', 9950)
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- FAST: Deferred join technique for offset pagination
SELECT p.*
FROM posts p
INNER JOIN (
  SELECT id FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 100000
) AS sub ON p.id = sub.id
ORDER BY p.created_at DESC;

Tip 11: Connection Pooling

# Install ProxySQL for MySQL connection pooling
sudo apt install proxysql

# Configure via ProxySQL admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

# Add backend MySQL server
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

# Add user
INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user', 'password', 1);

# Set connection pool size
UPDATE mysql_servers SET max_connections = 200 WHERE hostname = '127.0.0.1';

# Load configuration
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;

Tip 12: InnoDB Buffer Pool Sizing

-- Check current buffer pool size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check buffer pool usage
SHOW ENGINE INNODB STATUSG
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;

-- Rule of thumb: Set to 70-80% of available RAM on dedicated DB servers
-- For a server with 16GB RAM:
-- SET GLOBAL innodb_buffer_pool_size = 12884901888; -- 12GB

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8   # One per GB of buffer pool
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1 # Durability (set 2 for speed)
innodb_flush_method = O_DIRECT

Tip 13: Query Cache Alternatives

The MySQL query cache was removed in MySQL 8.0 due to scalability issues with multi-core systems. Use these alternatives instead:

-- Use application-level caching with Redis
-- Node.js example
-- const redis = require('redis');
-- const client = redis.createClient();

-- async function getUser(id) {
--   const cached = await client.get(`user:${id}`);
--   if (cached) return JSON.parse(cached);
--   const user = await db.query('SELECT * FROM users WHERE id = ?', [id]);
--   await client.setEx(`user:${id}`, 3600, JSON.stringify(user));
--   return user;
-- }

-- ProxySQL query caching (transparent to application)
-- mysql -u admin -padmin -h 127.0.0.1 -P 6032
INSERT INTO mysql_query_rules (rule_id, match_pattern, cache_ttl)
VALUES (1, '^SELECT .* FROM products WHERE category_id', 60000);
LOAD MYSQL QUERY RULES TO RUNTIME;

Tip 14: Table Partitioning Strategies

-- Range partitioning by date (most common)
CREATE TABLE logs (
  id BIGINT AUTO_INCREMENT,
  message TEXT,
  created_at DATETIME NOT NULL,
  level ENUM('info', 'warn', 'error'),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Drop old data instantly (instead of slow DELETE)
ALTER TABLE logs DROP PARTITION p2023;

-- List partitioning
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT,
  region VARCHAR(20),
  total DECIMAL(10,2),
  PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS (region) (
  PARTITION p_us VALUES IN ('us-east', 'us-west'),
  PARTITION p_eu VALUES IN ('eu-west', 'eu-central'),
  PARTITION p_asia VALUES IN ('asia-east', 'asia-south')
);

Tip 15: Replication (Master-Replica Setup)

-- On the master server
-- /etc/mysql/mysql.conf.d/mysqld.cnf
-- server-id = 1
-- log_bin = /var/log/mysql/mysql-bin.log
-- binlog_do_db = myapp_db

-- Create replication user on master
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;

-- On the replica server
-- server-id = 2
-- relay-log = /var/log/mysql/mysql-relay-bin.log

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'master-ip',
  SOURCE_USER = 'replica_user',
  SOURCE_PASSWORD = 'StrongPassword123!',
  SOURCE_LOG_FILE = 'mysql-bin.000001',
  SOURCE_LOG_POS = 154;

START REPLICA;
SHOW REPLICA STATUSG

Tip 16: Read Replicas for Scaling

// Application-level read/write splitting (Node.js)
const mysql = require('mysql2/promise');

const writePool = mysql.createPool({
  host: 'master-db.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp',
  connectionLimit: 10
});

const readPool = mysql.createPool({
  host: 'replica-db.example.com',
  user: 'app_user',
  password: 'password',
  database: 'myapp',
  connectionLimit: 20
});

async function getUser(id) {
  const [rows] = await readPool.query('SELECT * FROM users WHERE id = ?', [id]);
  return rows[0];
}

async function updateUser(id, data) {
  await writePool.query('UPDATE users SET name = ? WHERE id = ?', [data.name, id]);
}

Tip 17: pt-query-digest for Analysis

# Basic usage
pt-query-digest /var/log/mysql/slow-query.log

# Filter by time range
pt-query-digest --since "2025-05-01" --until "2025-05-31" /var/log/mysql/slow-query.log

# Save report to file
pt-query-digest /var/log/mysql/slow-query.log > /tmp/digest-report.txt

# Analyze from processlist (live queries)
pt-query-digest --processlist h=localhost --iterations 60 --run-time 60

Tip 18: Schema Design Best Practices

-- Use appropriate data types (smaller = faster)
-- BAD
CREATE TABLE events (
  id BIGINT,                    -- Overkill for small tables
  name VARCHAR(5000),           -- Too large
  status VARCHAR(255),          -- Use ENUM instead
  is_active VARCHAR(5)          -- Use TINYINT(1) instead
);

-- GOOD
CREATE TABLE events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- 4 bytes
  name VARCHAR(200) NOT NULL,                  -- Realistic max
  status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
  is_active TINYINT(1) NOT NULL DEFAULT 1,     -- 1 byte
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Normalization vs Denormalization
-- Normalize for write-heavy workloads (avoid update anomalies)
-- Denormalize for read-heavy workloads (avoid expensive JOINs)

-- Denormalized example: store category_name alongside category_id
ALTER TABLE products ADD COLUMN category_name VARCHAR(100);
-- Trade-off: Faster reads, but must update category_name when category changes

Tip 19: Stored Procedures vs Application Logic

-- Use stored procedures for complex, multi-step operations
DELIMITER //
CREATE PROCEDURE TransferFunds(
  IN p_from_account INT,
  IN p_to_account INT,
  IN p_amount DECIMAL(10,2)
)
BEGIN
  DECLARE v_balance DECIMAL(10,2);

  START TRANSACTION;

  SELECT balance INTO v_balance FROM accounts WHERE id = p_from_account FOR UPDATE;

  IF v_balance < p_amount THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  ELSE
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;

    INSERT INTO transactions (from_account, to_account, amount, created_at)
    VALUES (p_from_account, p_to_account, p_amount, NOW());

    COMMIT;
  END IF;
END //
DELIMITER ;

-- Call the procedure
CALL TransferFunds(1, 2, 500.00);

Tip 20: Common Slow Query Patterns and Rewrites

-- Pattern 1: Function on indexed column prevents index usage
-- SLOW
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- FAST
SELECT * FROM users WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- Pattern 2: OR conditions that cannot use index
-- SLOW
SELECT * FROM products WHERE category_id = 5 OR brand_id = 10;
-- FAST (use UNION)
SELECT * FROM products WHERE category_id = 5
UNION
SELECT * FROM products WHERE brand_id = 10;

-- Pattern 3: SELECT * when you only need a few columns
-- SLOW
SELECT * FROM users WHERE status = 'active';
-- FAST
SELECT id, name, email FROM users WHERE status = 'active';

-- Pattern 4: NOT IN with subquery
-- SLOW
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blocked_users);
-- FAST
SELECT u.* FROM users u
LEFT JOIN blocked_users b ON u.id = b.user_id
WHERE b.user_id IS NULL;

-- Pattern 5: Implicit type conversion
-- SLOW (phone is VARCHAR but comparing to INT)
SELECT * FROM users WHERE phone = 5551234567;
-- FAST
SELECT * FROM users WHERE phone = '5551234567';

Quick Reference Cheat Sheet