MySQL: MySQL Performance Optimization

Last updated: 2026-08-26

During its year-end sale, Alice’s e-commerce platform saw its database response time skyrocket from 50 ms to 5 seconds, with users complaining that pages wouldn’t load and order submissions timed out. The operations team conducted an emergency investigation and discovered that several unoptimized slow queries were bringing the entire system to a crawl. By enabling slow query logging to pinpoint the problematic SQL statements, analyzing the execution plans with EXPLAIN, adding missing indexes, rewriting inefficient queries, and tuning the InnoDB configuration parameters, they ultimately reduced the response time back to under 100 ms.

1. What You'll Learn

• Methods for Configuring, Collecting, and Analyzing Slow Query Logs
• An In-Depth Analysis of the Fields in the EXPLAIN Execution Plan (type/key/rows/Extra)
• Core strategies for index optimization: avoiding invalid indexes, covering indexes, and the leftmost prefix of composite indexes
• Query Rewriting Techniques: Avoid SELECT *, Replace Subqueries with JOIN, Optimize Deep Pagination
• Tuning key configuration parameters: innodb_buffer_pool_size, max_connections, etc.

2. The End-to-End Performance Optimization Process

Performance optimization is not a one-time task, but rather a cyclical process of "discovery → analysis → optimization → validation."

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

flowchart TD
    A[Identifying Slow Queries] --> B[EXPLAIN Analyze the Execution Plan]
    B --> C{Issue Type?}
    C -->|Missing Index| D[Index Optimization]
    C -->|Inefficient query syntax| E[Query Rewrite]
    C -->|Unreasonable configuration| F[Configuration Tuning]
    D --> G[Verifying Performance Improvements]
    E --> G
    F --> G
    G -->|Still does not meet the standards| A
    G -->|Meet the requirements| H[Deployment Monitoring]

3. Slow Query Log

The slow query log is the first line of defense for identifying performance issues; it automatically logs SQL statements whose execution time exceeds a certain threshold.

(1) Getting Started and Configuration

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

(2) Log Analysis Tools

mysqldumpslow is a slow-query log aggregation tool built into MySQL that sorts queries by execution time or frequency.

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

(3) Performance Schema Alternatives

MySQL 5.6 and later versions support using Performance Schema to collect slow queries, which can be enabled dynamically without modifying the configuration file.

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME = 'events_statements_history_long';

▶ Example: Enabling the slow query log and identifying the top 5 slow queries

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query_log_file';

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

Output:

---------+------------+------------+------------+---------------------+--------------
query_id | LEFT(query | query_text | exec_count | avg_timer_ms        | rows_examined
---------+------------+------------+------------+---------------------+--------------
1        | value_1    | value_1    | 10         | 2024-01-15 10:30:00 | value_1      
2        | value_2    | value_2    | 15         | 2024-01-15 10:30:00 | value_2      
3        | value_3    | value_3    | 20         | 2024-01-15 10:30:00 | value_3      
---------+------------+------------+------------+---------------------+--------------
3 rows in set

▶ Example: Quickly View Slow Queries Using the sys View

SELECT query_id, LEFT(query, 80) AS query_text,
       exec_count, avg_timer_ms, rows_examined
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY avg_timer_ms DESC LIMIT 10;

Output:

Output displayed

4. An In-Depth Analysis of the EXPLAIN Execution Plan

EXPLAIN is the most essential diagnostic tool for SQL tuning; it shows how MySQL executes queries.

(1) Overview of EXPLAIN Output Fields

(2) Detailed Explanation of the "type" Field

The type field is the most critical field in EXPLAIN; it directly reflects the query's efficiency.

(3) Extra Field Key Values

• Using index: Covering index; no need to look up the table; best performance
• Using where: After the storage engine returns the data, it is filtered by the server layer
• Using filesort: Cannot sort using the index; an additional sort operation is required.
• Using temporary: A temporary table was used; this is common when a GROUP BY clause has no index.
• Using index condition: Index Pushdown (ICP), reducing the number of table lookups

▶ Example: Analyzing a Single-Table Query with EXPLAIN

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE user_id = 42 AND status = 'PAID';

+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra                     |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 4    |  120 | Using where               |
+----+-------------+--------+------+---------------+------+---------+------+------+-----------------------+

▶ Example: Analyzing a Joined Query with EXPLAIN

EXPLAIN SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2025-01-01';

Output:

Output displayed

5. Index Optimization Strategies

Indexes are powerful tools for speeding up queries, but using the wrong index is more dangerous than having no index at all.

(1) Covering Index

A covering index is one in which all fields used in a query are included in the index, eliminating the need to access the table to read row data.

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, total_amount);

A query for SELECT user_id, status, total_amount FROM orders WHERE user_id = 42 retrieves data entirely from the index.

(2) Composite Indexes and the Leftmost Prefix

Composite indexes follow the leftmost prefix principle: query conditions must begin matching from the leftmost column of the index.

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

(3) Quick Reference for Index Failure Scenarios

▶ Example: Validating the Leftmost Prefix of a Composite Index

ALTER TABLE products ADD INDEX idx_cat_brand_price (category_id, brand_id, price);

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND brand_id = 10;
EXPLAIN SELECT * FROM products WHERE brand_id = 10;

Output:

Output displayed

The second query has a type of ALL because it skips the leftmost column, category_id.

▶ Example: Using a covering index to eliminate table lookups

ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, total_amount);

EXPLAIN SELECT user_id, status, total_amount
FROM orders WHERE user_id = 42;

Output:

Output displayed

If Using index appears in the Extra column, it means no table lookup is required.

6. Query Optimization Techniques

Even with an index, poorly written queries still cannot take advantage of it.

(1) Avoid using SELECT *

SELECT * reads all columns, increases I/O, and may cause the covering index to become ineffective.

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

(2) Rewrite subqueries as JOINs

The related subquery is executed once for each row; rewriting it as a JOIN can significantly reduce the number of scans.

SELECT o.order_id, o.total_amount
FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE vip_level >= 3);

Optimized to:

SELECT o.order_id, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id AND u.vip_level >= 3;

(3) Deep Pagination Optimization

The traditional LIMIT offset, n performs extremely poorly when the offset is large.

SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

Optimization Plan—Cursor Pagination:

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

(4) Quick Reference for Query Optimization Techniques

7. Table Structure Optimization

A well-designed table structure is the foundation of performance; rewriting SQL can only optimize the existing structure.

(1) Selecting a Field Type

(2) Anti-paradigm Design

In scenarios with high query load, moderate redundancy can reduce the number of JOIN operations.

CREATE TABLE order_summary (
    order_id BIGINT PRIMARY KEY,
    user_id BIGINT,
    username VARCHAR(64),
    total_amount DECIMAL(10,2),
    INDEX idx_user (user_id)
);

Replicate the username column in the orders table to avoid having to JOIN the users table with every query.

8. Config Parameter Tuning

MySQL configuration parameters directly affect the behavior of storage engines and resource allocation.

(1) Key Configuration Parameters and Recommended Values

(2) Dynamically Adjusting Parameters

Some parameters can be modified online without restarting the instance.

SET GLOBAL innodb_buffer_pool_size = 8589934592;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

9. Performance Schema Introduction

Performance Schema is MySQL's built-in performance monitoring engine, which provides more granular diagnostic data than the slow query log.

(1) Enable the Performance Schema

SHOW VARIABLES LIKE 'performance_schema';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

(2) Common Monitoring Views

10. Comprehensive Hands-On Exercise: The Complete Performance Diagnosis Process

Using Alice's e-commerce platform as an example, we'll demonstrate the complete process from identifying slow queries to verifying performance improvements.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

mysqldumpslow -s t -t 5 /var/lib/mysql/slow.log

Output:

Query OK, 0 rows affected

Count: 328  Time=4.52s  Rows=1.0  Rows_examined=890000
SELECT * FROM orders WHERE YEAR(create_time)=2025 AND status='PAID';

EXPLAIN SELECT * FROM orders
WHERE YEAR(create_time) = 2025 AND status = 'PAID';

type: ALL | key: NULL | rows: 890000 | Extra: Using where

Reason for index failure: The YEAR() function was used on create_time. Solution:

ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

EXPLAIN SELECT order_id, user_id, total_amount
FROM orders
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
  AND status = 'PAID';

type: range | key: idx_status_time | rows: 3200 | Extra: Using index condition

The number of rows scanned dropped from 890,000 to 3,200, and the query time decreased from 4.5 seconds to 0.08 seconds. Finally, adjust the buffer pool:

SET GLOBAL innodb_buffer_pool_size = 8589934592;

❓ FAQ

📖 Summary

• The slow query log is the starting point for performance optimization; use mysqldumpslow or system views to quickly identify bottlenecks.
• The EXPLAIN command's "type" field ranges from "system" to "ALL" in descending order of quality; the goal is to achieve at least the "ref" level.
• Index Optimization Key Points: Avoid index mis-matches caused by functions or implicit conversions; make good use of covering indexes and the leftmost prefix of composite indexes.
• Query Rewriting: Replace SELECT * with specific columns, replace subqueries with JOINs, and use cursors or lazy joins for deep pagination
• Configuration Tuning: innodb_buffer_pool_size is the most critical parameter; the buffer pool hit rate should be maintained at 99% or higher.
• Performance Schema provides fine-grained monitoring that can identify high-frequency, short queries and unused indexes that are not captured in the slow log

📝 Exercises

1. Basic Question (Difficulty: ⭐): Enable the slow query log, set the threshold to 0.5 seconds, and use mysqldumpslow to identify the top 5 slow queries with the highest number of executions.

2. Advanced Exercise (Difficulty ⭐⭐): For a query with a type of ALL, use EXPLAIN to analyze it, then add appropriate indexes to verify that the type is upgraded to the ref or range level.

3. Challenge Question (Difficulty: ⭐⭐⭐): Design an indexing scheme for an order table with 1 million rows, using a covering index to optimize the query SELECT user_id, status, total_amount FROM orders WHERE user_id = ? AND status = ?, and compare the differences in the rows and Extra fields of the EXPLAIN output before and after optimization.

4. Practical Exercise (Difficulty: ⭐⭐⭐): Rewrite a slow SQL query containing a subquery using a JOIN, then optimize it with indexes to reduce the query time from seconds to less than 100 milliseconds. Document the entire optimization process.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us