MySQL Query Optimization and Performance Tuning Guide

This article provides a comprehensive checklist of MySQL optimization techniques, covering general SQL statement improvements, configuration parameter tuning, proper index usage, and common pitfalls.

General Statement Optimization

Choose appropriate data types and character sets to reduce storage and improve query speed. For example, use TINYINT(1) for boolean values and latin1 for English‑only text.

CREATE TABLE users (
    is_active TINYINT(1)
);

CREATE TABLE messages (
    content VARCHAR(255) CHARACTER SET latin1
);

Avoid SELECT *; select only the needed columns. SELECT id, name, email FROM users; Use JOINs wisely and ensure indexed columns are used in join conditions.

SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

Prefer JOIN or EXISTS over subqueries.

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

Replace OR conditions with UNION when appropriate, and avoid leading‑wildcard LIKE patterns.

SELECT id, name FROM users WHERE status = 'active'
UNION
SELECT id, name FROM users WHERE status = 'pending';

Batch inserts reduce overhead; disable unique checks and foreign key checks during massive inserts.

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'),
('Bob', '[email protected]');

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
-- batch insert
SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;

Enable query cache (note: removed in MySQL 8.0).

SET GLOBAL query_cache_size = 1048576;
SET GLOBAL query_cache_type = ON;

Prefer WHERE over HAVING for filtering.

SELECT user_id, COUNT(*) FROM orders
WHERE order_date > '2020-01-01'
GROUP BY user_id
HAVING COUNT(*) > 1;

Configuration Parameter Tuning

Key InnoDB and server variables that impact performance: innodb_buffer_pool_size: set to 60‑80% of physical memory. query_cache_size: deprecated in MySQL 8.0. thread_cache_size: increase to reduce thread creation overhead. table_open_cache, tmp_table_size, max_heap_table_size: enlarge to reduce I/O. innodb_flush_log_at_trx_commit: balance durability vs. speed. innodb_log_file_size and innodb_log_buffer_size: increase for better write performance. innodb_io_capacity: tune according to disk I/O capability. max_connections: raise to support more concurrent clients. sort_buffer_size and read_buffer_size: enlarge for faster sorting and sequential scans.

SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL thread_cache_size = 100;
SET GLOBAL table_open_cache = 4000;
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_log_file_size = 256M;
SET GLOBAL innodb_log_buffer_size = 16M;
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL max_connections = 500;
SET GLOBAL sort_buffer_size = 4M;
SET GLOBAL read_buffer_size = 2M;

Proper Index Usage

Create indexes on frequently used query and join columns, follow the left‑most prefix rule for composite indexes, and avoid functions on indexed columns.

CREATE INDEX idx_abc ON table_name (a, b, c);
SELECT * FROM table_name WHERE a = 1 AND b = 2;

Do not create duplicate indexes and be cautious with indexes on columns that are updated often.

CREATE INDEX idx_update_col ON table_name (update_col); -- use only if updates are rare

Use covering indexes when all required columns are contained in the index.

CREATE INDEX idx_covering ON orders (order_id, order_date, customer_id);
SELECT order_id, order_date, customer_id FROM orders WHERE customer_id = 123;

Other Pitfalls

Avoid SELECT DISTINCT unless necessary.

Use LIMIT 1 when only one row is needed.

Prefer UNION ALL over UNION when duplicate removal is not required.

Do not use leading‑wildcard LIKE '%keyword%'; use full‑text search or trailing wildcard instead.

Avoid IS NULL or IS NOT NULL on indexed columns.

Replace negative conditions ( !=, NOT IN) with positive ones.

For large pagination, avoid large OFFSET; use range queries based on a unique key.

Select appropriate lock types (row vs. table) to prevent deadlocks.

Explain Details

Use EXPLAIN to view the execution plan of a query. Important columns include type, possible_keys, key, and Extra. Types from best to worst: system, const, eq_ref, ref, range, index, ALL.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

Based on the output, you can create or adjust indexes, rewrite conditions to avoid functions, use covering indexes, or split complex queries into simpler ones.

Summary

Write and run EXPLAIN for the target query.

Analyze type, key, and Extra columns.

Optimize indexes, query predicates, and table structures accordingly.

Re‑run EXPLAIN to verify improvements.