Complete MySQL Optimization Guide: Configuration, Indexes, and Queries

MySQL remains one of the world's most popular relational databases. However, the default MySQL configuration is optimized for systems with minimal resources — not for production servers with gigabytes of RAM and millions of rows. This comprehensive guide covers all aspects of MySQL optimization: from configuration file tuning to building efficient indexes and optimizing slow queries.

Understanding MySQL Architecture

Before optimizing, understanding MySQL's internal workings is essential.

Core Components

MySQL consists of several layers:

Connection Layer. Manages client connections, authentication, and threads. Each connection receives its own thread.

SQL Layer. Parses queries, optimizes them, and executes. Query cache (pre-8.0) and optimizer operate here.

Storage Layer. Storage engines (InnoDB, MyISAM) manage reading and writing data to disk.

Filesystem Layer. Physical data storage on disk.

Storage Engines

InnoDB — primary engine since MySQL 5.5+:

• ACID transaction support
• Row-level locking
• Automatic crash recovery
• Foreign key support
• Clustered indexes

MyISAM — legacy engine:

• No transactions
• Table-level locking
• Faster for read operations
• Not recommended for new projects

All optimization examples in this guide target InnoDB.

Configuring my.cnf: Core Parameters

The my.cnf file (or my.ini on Windows) contains MySQL server settings. File location depends on distribution:

Linux:

• /etc/mysql/my.cnf
• /etc/my.cnf
• /usr/local/mysql/etc/my.cnf
• ~/.my.cnf

Check location:

mysql --help | grep "Default options"

Configuration File Structure

Configuration is divided into sections:

[client]
# Settings for client programs

[mysql]
# Settings for mysql CLI

[mysqld]
# Settings for MySQL server
# Most optimizations go here

[mysqldump]
# Settings for mysqldump

Primary settings are placed in the [mysqld] section.

Optimizing InnoDB Memory

InnoDB is the most critical component for optimization.

innodb_buffer_pool_size

MySQL's most important parameter. Defines RAM allocation for caching data and indexes.

Recommendation: 70-80% of available RAM on dedicated MySQL server.

For 8GB RAM server:

[mysqld]
innodb_buffer_pool_size = 6G

For 32GB RAM server:

[mysqld]
innodb_buffer_pool_size = 24G

Calculation for shared server (MySQL + application):

innodb_buffer_pool_size = Total RAM * 0.5

Check current value:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Important: changes require MySQL restart:

sudo systemctl restart mysql

innodb_buffer_pool_instances

Divides buffer pool into multiple instances to reduce contention.

Recommendation: 1 instance per 1GB buffer pool, maximum 64.

For 24GB buffer pool:

[mysqld]
innodb_buffer_pool_instances = 24

For 8GB buffer pool:

[mysqld]
innodb_buffer_pool_instances = 8

Minimum requirement: only works if innodb_buffer_pool_size >= 1GB.

innodb_log_file_size

Size of each transaction log file (redo log).

Recommendation: 25% of innodb_buffer_pool_size, maximum 4GB per file.

For 24GB buffer pool:

[mysqld]
innodb_log_file_size = 2G
innodb_log_files_in_group = 2

For 8GB buffer pool:

[mysqld]
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

Changing requires special procedure:

Stop MySQL:

sudo systemctl stop mysql

Remove old log files:

sudo rm /var/lib/mysql/ib_logfile*

Modify configuration:

sudo nano /etc/mysql/my.cnf

Start MySQL:

sudo systemctl start mysql

MySQL creates new log files with correct size.

innodb_flush_log_at_trx_commit

Controls log flushing to disk after transactions.

Value 1 (default):

• Full ACID compliance
• Flush to disk after every transaction
• Maximum safety, minimum speed

Value 2:

• Flush to OS cache after every transaction
• Flush to disk every second
• Risk of losing 1 second of transactions on OS crash
• Significantly faster

Value 0:

• Flush to MySQL buffer and disk every second
• Risk of losing 1 second of transactions on MySQL crash
• Fastest option

For high performance with acceptable risk:

[mysqld]
innodb_flush_log_at_trx_commit = 2

For maximum reliability (critical data):

[mysqld]
innodb_flush_log_at_trx_commit = 1

innodb_flush_method

Defines method for writing data to disk.

Value O_DIRECT:

• Bypasses OS file cache
• Avoids double caching (OS + InnoDB)
• Recommended for Linux

[mysqld]
innodb_flush_method = O_DIRECT

Value O_DSYNC:

• Used on some Unix systems
• Synchronizes file metadata

innodb_io_capacity and innodb_io_capacity_max

Inform MySQL about disk subsystem speed.

For HDD (7200 RPM):

[mysqld]
innodb_io_capacity = 200
innodb_io_capacity_max = 400

For SSD:

[mysqld]
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

For NVMe SSD:

[mysqld]
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000

Check disk IOPS:

sudo fio --name=random-read --ioengine=libaio --rw=randread --bs=4k --size=1G --numjobs=4 --runtime=60 --time_based --iodepth=32

Optimizing Connections

max_connections

Maximum number of simultaneous connections.

Calculation:

max_connections = (Available RAM - MySQL buffers) / (per-thread buffers)

Typical values:

[mysqld]
max_connections = 200

For high-load servers:

[mysqld]
max_connections = 500

Important: each connection consumes memory for thread buffers.

Check maximum usage:

SHOW STATUS LIKE 'Max_used_connections';

If close to max_connections, increase parameter.

thread_cache_size

Caches threads for reuse.

Recommendation:

[mysqld]
thread_cache_size = 50

For high-load systems:

[mysqld]
thread_cache_size = 100

Check cache efficiency:

SHOW STATUS LIKE 'Threads_created';
SHOW STATUS LIKE 'Connections';

Good ratio: Threads_created < 1% of Connections.

wait_timeout and interactive_timeout

Timeouts for inactive connections.

Close inactive connections after 5 minutes:

[mysqld]
wait_timeout = 300
interactive_timeout = 300

For applications with long-lived connections:

[mysqld]
wait_timeout = 600
interactive_timeout = 600

Optimizing Query Cache

Note: Query Cache removed in MySQL 8.0. These settings apply to MySQL 5.7 and below.

query_cache_type and query_cache_size

For MySQL 5.7:

[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

Disable query cache (recommended for write-heavy):

[mysqld]
query_cache_type = 0
query_cache_size = 0

Check efficiency:

SHOW STATUS LIKE 'Qcache%';

Signs of inefficiency:

• High Qcache_lowmem_prunes
• Low Qcache_hits relative to Com_select

Configuring Thread Buffers

sort_buffer_size

Buffer for sort operations (ORDER BY, GROUP BY).

Conservative value:

[mysqld]
sort_buffer_size = 2M

For large sorts:

[mysqld]
sort_buffer_size = 4M

Don't set too large — allocated per connection.

join_buffer_size

Buffer for joins without indexes.

[mysqld]
join_buffer_size = 2M

For complex JOINs:

[mysqld]
join_buffer_size = 4M

read_buffer_size and read_rnd_buffer_size

Buffers for sequential and random reads.

[mysqld]
read_buffer_size = 1M
read_rnd_buffer_size = 2M

Optimizing Temporary Tables

tmp_table_size and max_heap_table_size

Maximum size for temporary tables in memory.

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

For large GROUP BY and DISTINCT:

[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M

Check disk temporary table usage:

SHOW STATUS LIKE 'Created_tmp%';

If Created_tmp_disk_tables > 25% of Created_tmp_tables, increase sizes.

Optimizing Binary Logs

binlog_cache_size

Cache size for transactional binary logs.

[mysqld]
binlog_cache_size = 1M

For large transactions:

[mysqld]
binlog_cache_size = 4M

expire_logs_days

Automatic removal of old binary logs.

Keep 7 days:

[mysqld]
expire_logs_days = 7

For MySQL 8.0+:

[mysqld]
binlog_expire_logs_seconds = 604800

Complete Configurations for Different Scenarios

Configuration for 8GB RAM Server

Optimized for web applications with moderate load:

[mysqld]
# Core parameters
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB settings
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_file_per_table = 1
innodb_open_files = 400

# Connections
max_connections = 200
thread_cache_size = 50
wait_timeout = 300
interactive_timeout = 300

# Query buffers
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

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

# Binary logs
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M

# Security
bind-address = 127.0.0.1

# Character set
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# MyISAM (if used)
key_buffer_size = 256M

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

Configuration for 32GB RAM Server

Optimized for high-load applications:

[mysqld]
# Core parameters
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB settings
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 24
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_open_files = 1000
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 50

# Connections
max_connections = 500
thread_cache_size = 100
wait_timeout = 600
interactive_timeout = 600

# Query buffers
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M

# Temporary tables
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M

# Tables
table_open_cache = 4000
table_definition_cache = 2000

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

# Binary logs
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 0
binlog_cache_size = 4M

# Performance
query_cache_type = 0
query_cache_size = 0

# Security
bind-address = 127.0.0.1

# Character set
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# MyISAM
key_buffer_size = 512M

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

Configuration for Read-Heavy Workload

Optimized for applications with predominantly read operations:

[mysqld]
# Increased buffer pool
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 20

# More read threads
innodb_read_io_threads = 16
innodb_write_io_threads = 4

# Aggressive caching
table_open_cache = 8000
table_definition_cache = 4000

# Read optimization
innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 0

# More connections
max_connections = 1000
thread_cache_size = 200

Configuration for Write-Heavy Workload

Optimized for applications with intensive writes:

[mysqld]
# More write threads
innodb_write_io_threads = 16
innodb_read_io_threads = 4

# Larger transaction logs
innodb_log_file_size = 4G
innodb_log_files_in_group = 2

# Write optimization
innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 1
innodb_doublewrite = 1

# High IO capacity
innodb_io_capacity = 20000
innodb_io_capacity_max = 40000

# Disable query cache
query_cache_type = 0
query_cache_size = 0

Applying Configuration

Backup Current Configuration

Create backup before changes:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

With date:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

Check Syntax

Verify configuration before restart:

mysqld --verbose --help | grep -A 1 'Default options'

Gradual Implementation

Don't apply all changes simultaneously. Change parameters in groups:

Day 1: Buffer pool and logs Day 2: Connections and threads Day 3: Query buffers Day 4: Temporary tables

Monitor performance after each change.

Restart MySQL

Apply changes:

sudo systemctl restart mysql

Check status:

sudo systemctl status mysql

Check logs for errors:

sudo tail -f /var/log/mysql/error.log

Verify Applied Parameters

Connect to MySQL:

mysql -u root -p

Check specific parameter:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Check all InnoDB parameters:

SHOW VARIABLES LIKE 'innodb%';

Export all settings:

SELECT * FROM performance_schema.global_variables ORDER BY VARIABLE_NAME \G

Indexes: Fundamentals

Indexes are MySQL's most powerful optimization tool. Proper indexes can accelerate queries by hundreds of times.

How Indexes Work

Indexes are data structures (typically B-Tree) enabling fast row lookup by column values.

Without index: MySQL scans entire table (full table scan) With index: MySQL uses index for direct row access

Analogy: book's index. Instead of flipping through all pages, you check the index and open the needed page.

Index Types in MySQL

PRIMARY KEY:

• Unique row identifier
• Automatically creates index
• Cannot be NULL
• One table = one PRIMARY KEY

UNIQUE:

• Guarantees value uniqueness
• Automatically creates index
• Can be NULL
• Multiple UNIQUE indexes possible

INDEX (regular index):

• No uniqueness requirement
• Accelerates column searches
• Most common type

FULLTEXT:

• For full-text search
• Works with TEXT, VARCHAR columns
• Special search logic

SPATIAL:

• For geospatial data
• Types: GEOMETRY, POINT, POLYGON

Index Structures

B-Tree (default):

• Balanced tree
• Suitable for most cases
• Supports =, >, <, >=, <=, BETWEEN, LIKE 'prefix%'

Hash:

• Hash table
• Only exact matches (=)
• No range support
• MEMORY tables only

Full-text:

• Inverted index
• Text search
• Supports MATCH AGAINST

Creating Indexes

Creating Simple Index

Index on single column:

CREATE INDEX idx_email ON users(email);

With explicit method specification:

CREATE INDEX idx_email ON users(email) USING BTREE;

Creating Composite Index

Index on multiple columns:

CREATE INDEX idx_user_date ON orders(user_id, order_date);

Column order is critical:

• First column: most selective or frequently used in WHERE
• Following: order of usage in queries

Creating Unique Index

Guarantees value uniqueness:

CREATE UNIQUE INDEX idx_username ON users(username);

Composite unique index:

CREATE UNIQUE INDEX idx_email_domain ON users(email, domain);

Adding Index to Existing Table

Via ALTER TABLE:

ALTER TABLE products ADD INDEX idx_category (category_id);

Multiple indexes:

ALTER TABLE products
  ADD INDEX idx_category (category_id),
  ADD INDEX idx_price (price),
  ADD INDEX idx_status (status);

Creating Prefix Index

For long string fields, index prefix:

CREATE INDEX idx_description ON products(description(100));

Only first 100 characters indexed, saving space.

Creating Full-Text Index

For full-text search:

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

Usage:

SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql optimization' IN NATURAL LANGUAGE MODE);

Creating Index During Table Creation

Define indexes in CREATE TABLE:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY idx_username (username),
    UNIQUE KEY idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

Index Strategies

"Leftmost Prefix" Rule

Composite index (a, b, c) can be used for:

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

But NOT for:

• WHERE b = ?
• WHERE c = ?
• WHERE b = ? AND c = ?

Example:

CREATE INDEX idx_compound ON orders(user_id, status, order_date);

This index is efficient for:

SELECT * FROM orders WHERE user_id = 123;
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' AND order_date > '2024-01-01';

Inefficient for:

SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE order_date > '2024-01-01';

Covering Index

Index containing all query columns — MySQL doesn't access table.

Query:

SELECT user_id, order_date, total FROM orders WHERE user_id = 123;

Create covering index:

CREATE INDEX idx_covering ON orders(user_id, order_date, total);

MySQL retrieves all data from index without table access.

Indexing for Sorting

Indexes used not only for WHERE but also ORDER BY:

CREATE INDEX idx_date_status ON orders(order_date, status);

Efficient sorting:

SELECT * FROM orders ORDER BY order_date, status;

Indexing for JOIN

Index columns used in JOIN:

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON order_items(product_id);

Query:

SELECT o.*, oi.*, p.*
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 123;

Required indexes:

• orders(user_id)
• order_items(order_id)
• order_items(product_id)

Partial Indexes

MySQL doesn't directly support WHERE in indexes, but can simulate via virtual columns:

ALTER TABLE users ADD COLUMN is_active_bool BOOLEAN AS (status = 'active') STORED;
CREATE INDEX idx_active ON users(is_active_bool);

Managing Indexes

Viewing Table Indexes

Show all indexes:

SHOW INDEX FROM table_name;

Detailed information:

SHOW INDEX FROM table_name \G

Via information_schema:

SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    CARDINALITY,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

Index Size

Check size of all table indexes:

SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROUND(SUM(stat_value) * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_database'
  AND table_name = 'your_table'
GROUP BY TABLE_NAME, INDEX_NAME;

Index sizes across database:

SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Removing Index

Via DROP INDEX:

DROP INDEX idx_email ON users;

Via ALTER TABLE:

ALTER TABLE users DROP INDEX idx_email;

Remove multiple indexes:

ALTER TABLE products
  DROP INDEX idx_category,
  DROP INDEX idx_price,
  DROP INDEX idx_old_status;

Renaming Index

MySQL 5.7+:

ALTER TABLE users RENAME INDEX old_name TO new_name;

For older versions — drop and recreate.

Rebuilding Indexes

Rebuild all table indexes:

ALTER TABLE table_name ENGINE=InnoDB;

Or via OPTIMIZE TABLE:

OPTIMIZE TABLE table_name;

Important: OPTIMIZE locks table. Use during maintenance windows.

Analyzing Index Usage

Checking Unused Indexes

MySQL 5.7+ has sys.schema_unused_indexes:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_database';

Or via performance_schema:

SELECT 
    object_schema,
    object_name,
    index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_schema, object_name;

Monitoring Index Usage

Enable statistics collection:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/io/table/%';

Check usage:

SELECT 
    object_name,
    index_name,
    count_star AS uses,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_star DESC;

Analyzing Duplicate Indexes

Find redundant indexes with sys:

SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema = 'your_database';

Example duplicates:

• INDEX (user_id)
• INDEX (user_id, order_date)

First index is redundant — second covers its functionality.

Query Optimization with EXPLAIN

EXPLAIN shows query execution plan.

Basic EXPLAIN Usage

Analyze SELECT query:

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

Extended information:

EXPLAIN EXTENDED SELECT * FROM users WHERE email = 'user@example.com';
SHOW WARNINGS;

EXPLAIN FORMAT=JSON

Detailed JSON output:

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;

Interpreting EXPLAIN

Key columns:

type — access type (best to worst):

• system — one-row table
• const — single row by PRIMARY KEY or UNIQUE
• eq_ref — one row for each combination from previous tables
• ref — multiple rows with same index value
• range — index value range
• index — index scan
• ALL — full table scan (bad!)

possible_keys — indexes MySQL might use

key — index MySQL actually uses

key_len — length of used index portion

rows — approximate rows examined

Extra — additional information:

• Using index — covering index (excellent!)
• Using where — MySQL-level filtering
• Using temporary — temporary table used (bad)
• Using filesort — file sorting (can be slow)
• Using join buffer — JOIN without indexes (bad)

Analysis Examples

Bad query:

EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Problem: function on column prevents index use.

Fix:

CREATE INDEX idx_order_date ON orders(order_date);

EXPLAIN SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

Inefficient JOIN:

EXPLAIN SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Problem: no index on users.country.

Fix:

CREATE INDEX idx_country ON users(country);

Optimizing Slow Queries

Enable Slow Query Log

Enable slow query logging:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

In configuration:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Analyzing Slow Query Log

Use mysqldumpslow:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Parameters:

• -s t — sort by time
• -t 10 — top 10 queries

Alternative sorts:

• -s c — by count
• -s l — by lock time
• -s r — by rows read

Detailed analysis:

mysqldumpslow -s t -t 20 -g 'SELECT' /var/log/mysql/slow.log

Using pt-query-digest

Install Percona Toolkit:

wget percona.com/get/pt-query-digest
chmod +x pt-query-digest

Analyze log:

pt-query-digest /var/log/mysql/slow.log > slow-digest.txt

With sort by execution time:

pt-query-digest --order-by Query_time:sum /var/log/mysql/slow.log

Query Profiling

Enable profiling:

SET profiling = 1;

Execute queries:

SELECT * FROM large_table WHERE column = 'value';

View profiles:

SHOW PROFILES;

Details for specific query:

SHOW PROFILE FOR QUERY 1;

CPU and block IO details:

SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

Optimizing Specific Query Types

Optimizing SELECT

Problem: SELECT *

SELECT * FROM users WHERE id = 123;

Solution: select only needed columns

SELECT id, username, email FROM users WHERE id = 123;

Problem: functions in WHERE

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Solution: rewrite condition

SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

Optimizing JOIN

Problem: multiple JOINs without indexes

SELECT o.*, u.username, p.name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

Solution: add indexes on JOIN columns

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON orders(product_id);

Use STRAIGHT_JOIN for forced order:

SELECT STRAIGHT_JOIN o.*, u.username
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US';

Optimizing GROUP BY

Problem: GROUP BY without index

SELECT category, COUNT(*) FROM products GROUP BY category;

Solution: index on GROUP BY column

CREATE INDEX idx_category ON products(category);

Covering index optimization:

CREATE INDEX idx_category_price ON products(category, price);

SELECT category, AVG(price) FROM products GROUP BY category;

Optimizing ORDER BY

Problem: sorting without index

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

Solution: index on ORDER BY column

CREATE INDEX idx_order_date ON orders(order_date DESC);

Composite index for WHERE + ORDER BY:

CREATE INDEX idx_user_date ON orders(user_id, order_date DESC);

SELECT * FROM orders 
WHERE user_id = 123 
ORDER BY order_date DESC 
LIMIT 10;

Optimizing COUNT

Problem: COUNT(*) on large table

SELECT COUNT(*) FROM orders WHERE status = 'pending';

Solution 1: covering index

CREATE INDEX idx_status ON orders(status);

Solution 2: denormalization (counter in separate table)

CREATE TABLE order_stats (
    status VARCHAR(20) PRIMARY KEY,
    count INT DEFAULT 0
);

UPDATE order_stats SET count = count + 1 WHERE status = 'pending';

Solution 3: approximate COUNT

SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_NAME = 'orders';

Optimizing DISTINCT

Problem: DISTINCT on large table

SELECT DISTINCT category FROM products;

Solution: GROUP BY instead of DISTINCT

SELECT category FROM products GROUP BY category;

With index:

CREATE INDEX idx_category ON products(category);

Optimizing Subqueries

Problem: correlated subquery

SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 10;

Solution: rewrite with JOIN

SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
    HAVING order_count > 10
) o ON u.id = o.user_id;

Problem: IN with large subquery

SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

Solution: EXISTS or JOIN

SELECT p.* FROM products p
WHERE EXISTS (
    SELECT 1 FROM categories c 
    WHERE c.id = p.category_id AND c.active = 1
);

Or:

SELECT p.* FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

Table Partitioning

Partitioning divides large tables into manageable pieces.

Partitioning Types

RANGE — by value range

CREATE TABLE orders (
    id INT,
    order_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

LIST — by value list

CREATE TABLE users (
    id INT,
    country VARCHAR(2),
    username VARCHAR(50)
)
PARTITION BY LIST COLUMNS(country) (
    PARTITION pNorthAmerica VALUES IN ('US', 'CA', 'MX'),
    PARTITION pEurope VALUES IN ('UK', 'DE', 'FR'),
    PARTITION pAsia VALUES IN ('JP', 'CN', 'IN')
);

HASH — by value hash

CREATE TABLE logs (
    id INT,
    message TEXT,
    created_at DATETIME
)
PARTITION BY HASH(id)
PARTITIONS 10;

KEY — like HASH, MySQL chooses function

CREATE TABLE sessions (
    session_id VARCHAR(128),
    user_id INT,
    data TEXT
)
PARTITION BY KEY(session_id)
PARTITIONS 8;

Adding Partitions

For RANGE partitions:

ALTER TABLE orders ADD PARTITION (
    PARTITION p2025 VALUES LESS THAN (2026)
);

Removing Partitions

Delete old data by dropping partition:

ALTER TABLE orders DROP PARTITION p2022;

Significantly faster than DELETE.

Viewing Partitions

Partition information:

SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    PARTITION_METHOD,
    PARTITION_EXPRESSION,
    TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'orders';

Performance Monitoring

Performance Schema

Enable Performance Schema:

[mysqld]
performance_schema = ON

Basic statistics:

SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Sys Schema

Slowest queries:

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
LIMIT 10;

Tables without primary keys:

SELECT * FROM sys.schema_tables_with_full_table_scans
WHERE object_schema = 'your_database';

Suboptimal indexes:

SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'your_database'
ORDER BY rows_selected DESC;

Monitoring InnoDB

InnoDB status:

SHOW ENGINE INNODB STATUS\G

Buffer pool size:

SELECT 
    (PagesData*PageSize)/POWER(1024,3) AS data_gb,
    (PagesFree*PageSize)/POWER(1024,3) AS free_gb,
    (PagesData*PageSize)/POWER(1024,3) + (PagesFree*PageSize)/POWER(1024,3) AS total_gb
FROM (
    SELECT 
        variable_value AS PagesData
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_buffer_pool_pages_data'
) AS A,
(
    SELECT 
        variable_value AS PagesFree
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_buffer_pool_pages_free'
) AS B,
(
    SELECT 
        variable_value AS PageSize
    FROM performance_schema.global_status
    WHERE variable_name='Innodb_page_size'
) AS C;

Key Metrics

Check buffer pool efficiency:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Good ratio: 99%+ cache hits.

Pending transactions count:

SELECT COUNT(*) FROM information_schema.INNODB_TRX;

Current locks:

SELECT * FROM performance_schema.data_locks;

Optimization Tools

mysqltuner

Installation:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

Run analysis:

./mysqltuner.pl

With additional checks:

./mysqltuner.pl --checkversion --updateversion

pt-query-digest

Analyze slow queries:

pt-query-digest /var/log/mysql/slow.log

Output to file:

pt-query-digest /var/log/mysql/slow.log --output slowlog --report > report.txt

mytop

Installation:

sudo apt install mytop

Launch:

mytop -u root -p

Interactive real-time query monitoring.

innotop

Installation:

sudo apt install innotop

Launch:

innotop -u root -p

Real-time InnoDB monitoring.

Conclusion

MySQL optimization is an iterative process. Core principles:

Start with configuration. Proper my.cnf parameters provide 50-70% improvement without code changes.

Index strategically. Don't create indexes on everything. Analyze real queries and create indexes purposefully.

Use EXPLAIN. Always check execution plans for slow queries.

Monitor constantly. Use slow query log, Performance Schema, and sys schema for issue tracking.

Test changes. Modify parameters gradually and measure results.

Document everything. Record changes and reasons. Helps with rollbacks or scaling.

Proper MySQL optimization can improve performance 10-100x. Invest time understanding your workload and apply these techniques methodically.