Home / Database / Fix MySQL Slow Query

Database

Fix MySQL Slow Query

Resolve MySQL slow query issues by analyzing query execution, checking indexes, and optimizing database configuration.

Published: Apr 11, 20266 min readBy FixWikiHub Editorial Team

Abstract illustration for a troubleshooting knowledge base category.

Introduction

MySQL queries are taking too long to execute. Application response times are slow, timeouts are occurring, or queries are consuming excessive resources.

Symptoms

Query timeout:

sql

ERROR 1317 (70100): Query execution was interrupted

Slow query log:

bash

# Time: 2024-01-01T00:00:00.000000Z
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 1000  Rows_examined: 1000000
SELECT * FROM large_table WHERE column = 'value';

Common Causes

1.Missing index - Query scanning full table
2.Inefficient query - Poorly written SQL
3.Outdated statistics - Optimizer using wrong plan
4.Lock contention - Query blocked by locks
5.Resource limits - Memory or I/O constraints
6.Large result set - Query returning too many rows
7.Network latency - Slow network to database

Step-by-Step Fix

1.Check logs for specific error messages
2.Verify configuration settings
3.Test network connectivity
4.Review recent changes
5.Apply corrective action
6.Verify the fix

Step 1: Identify Slow Queries

```sql -- Enable slow query log: SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- Check slow queries: SHOW VARIABLES LIKE 'slow_query%';

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

-- Current running queries: SHOW PROCESSLIST;

-- Long-running queries: SELECT * FROM information_schema.processlist WHERE TIME > 10 ORDER BY TIME DESC; ```

Step 2: Analyze Query Execution

```sql -- Explain plan: EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Extended explain: EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'user@example.com';

-- Analyze table: ANALYZE TABLE users;

-- Check table status: SHOW TABLE STATUS LIKE 'users';

-- Check index cardinality: SHOW INDEX FROM users; ```

Step 3: Check Index Usage

```sql -- List indexes: SHOW INDEX FROM users;

-- Check index usage: SELECT * FROM sys.schema_index_statistics WHERE table_schema = 'mydb' AND table_name = 'users';

-- Find unused indexes: SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- Create index: CREATE INDEX idx_users_email ON users(email);

-- Check if index used: EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Force index: SELECT * FROM users FORCE INDEX (idx_users_email) WHERE email = 'user@example.com'; ```

Step 4: Update Statistics

```sql -- Analyze table: ANALYZE TABLE users;

-- Optimize table: OPTIMIZE TABLE users;

-- Check table info: SHOW TABLE STATUS LIKE 'users';

-- Check information_schema: SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb'; ```

Step 5: Check Lock Contention

```sql -- Current locks: SHOW OPEN TABLES WHERE In_use > 0;

-- InnoDB locks: SELECT * FROM information_schema.INNODB_LOCKS;

-- Lock waits: SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- InnoDB status: SHOW ENGINE INNODB STATUS;

-- Kill blocking query: KILL QUERY process_id;

-- Lock wait timeout: SET innodb_lock_wait_timeout = 50; ```

Step 6: Optimize Query

```sql -- Use indexes effectively: SELECT id, email, name FROM users WHERE email = 'user@example.com';

-- Use LIMIT: SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- Use JOINs instead of subqueries: SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.active = 1;

-- Use covering index: CREATE INDEX idx_users_covering ON users(email, name, created_at); ```

Step 7: Check MySQL Configuration

```sql -- Key variables: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'join_buffer_size'; SHOW VARIABLES LIKE 'sort_buffer_size'; SHOW VARIABLES LIKE 'tmp_table_size';

-- Check settings: SHOW VARIABLES LIKE '%buffer%'; SHOW VARIABLES LIKE '%cache%';

-- Set variables: SET GLOBAL innodb_buffer_pool_size = 4294967296; SET GLOBAL join_buffer_size = 262144;

-- Check status: SHOW STATUS LIKE 'Innodb_buffer_pool%'; ```

Step 8: Check Resource Usage

```bash # MySQL memory: ps aux | grep mysql

# Disk I/O: iostat -x 1

# Disk space: df -h /var/lib/mysql

# Table sizes: mysql -e "SELECT table_schema, table_name, round(((data_length + index_length) / 1024 / 1024), 2) AS size_mb FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC LIMIT 10;"

# Connection count: mysql -e "SHOW STATUS LIKE 'Threads_connected';" mysql -e "SHOW VARIABLES LIKE 'max_connections';" ```

Step 9: Use Query Hints

```sql -- Force index: SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'user@example.com';

-- Ignore index: SELECT * FROM users IGNORE INDEX (PRIMARY) WHERE id > 100;

-- Straight join: SELECT STRAIGHT_JOIN * FROM users u JOIN orders o ON u.id = o.user_id;

-- Query timeout: SET SESSION max_execution_time = 30000; ```

Step 10: Monitor Query Performance

```sql -- Enable performance_schema: UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

-- Query performance: SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000 as total_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Table I/O: SELECT * FROM performance_schema.table_io_waits_summary_by_table WHERE OBJECT_SCHEMA = 'mydb' ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Reset statistics: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest; ```

MySQL Slow Query Checklist

Check	Command	Expected
Query plan	EXPLAIN	Uses indexes
Index usage	SHOW INDEX	Present
Statistics	ANALYZE TABLE	Updated
Locks	SHOW OPEN TABLES	No blocking
Configuration	SHOW VARIABLES	Optimized
Resources	top/iostat	Available

Verification

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

ANALYZE TABLE users;

SHOW INDEX FROM users;

SELECT * FROM information_schema.processlist WHERE TIME > 10;

SHOW STATUS LIKE 'Innodb_buffer_pool%';

SELECT DIGEST_TEXT, SUM_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 5; ```

[Fix MySQL Connection Timeout](/articles/fix-mysql-connection-timeout)
[Fix PostgreSQL Slow Query](/articles/fix-postgresql-slow-query)
[Fix MySQL Replication Lag High](/articles/fix-mysql-replication-lag-high)

[Database troubleshooting: Fix Backup Exclusive Lock Table Production Writes ](backup-exclusive-lock-table-production-writes)
[Fix Connection Pool Leak Application Not Closing Issue in Database](connection-pool-leak-application-not-closing)
[Fix Connection Reset Idle Timeout Firewall Issue in Database](connection-reset-idle-timeout-firewall)
[Fix Connection Reset Idle Timeout Serverless Database Issue in Database](connection-reset-idle-timeout-serverless-database)
[Fix Connection String Encoding Special Characters Issue in Database](connection-string-encoding-special-characters)

Was this guide helpful?

Related search paths

People also search for

If the symptom is close but not identical, these search paths usually surface the right neighboring fixes faster than scrolling the full archive.

MySQL Slow Query MySQL Slow Query Database MySQL Slow Query troubleshooting MySQL Slow Query fix Resolve MySQL slow query issues by analyzing query execution, checking indexes, and optimizing database configuration Database Resolve MySQL slow query issues by analyzing query execution, checking indexes, and optimizing database configuration

Explore Related Topics

Browse Guides from Other Categories

Discover troubleshooting guides from related categories to expand your knowledge.

FAQ

Database Troubleshooting FAQs

Common questions about troubleshooting and preventing similar issues

How do I know if this database-errors troubleshooting guide applies to my situation?

This guide is designed for database-errors issues. If you're experiencing similar symptoms described in the article, follow the step-by-step instructions. Start with the most common causes and work through the diagnostic process.

Is it safe to follow these database-errors troubleshooting steps?

Yes, all steps are designed to be safe and non-destructive. We recommend creating backups before making significant changes and testing each step before proceeding to the next.

How long does it typically take to resolve this type of database-errors issue?

Most database-errors issues can be resolved within 30 minutes to 2 hours, depending on the complexity and root cause. Follow the troubleshooting flow to identify and fix the problem efficiently.

How can I prevent this database-errors issue from happening again?

Regular maintenance, monitoring, and following best practices for database-errors configuration can help prevent recurrence. Consider implementing automated checks and alerts for early detection.

Written by

FixWikiHub Editorial Team

Our editorial team consists of experienced DevOps engineers, systems administrators, and cloud architects with hands-on experience in production environments across AWS, Azure, GCP, and on-premises infrastructure.

Every guide undergoes technical review for accuracy and is updated when software versions, commands, or best practices change.

Last updated: Apr 11, 2026

About our team

Important Notice

Disclaimer & Safety Guidelines

The troubleshooting steps in this guide are provided for educational and informational purposes. Before applying any changes to production systems:

Test in a staging environment first — Always verify commands and configurations in a non-production environment before deploying to live systems.
Create backups — Ensure you have current backups of databases, configurations, and critical files before making changes.
Understand the impact — Review how each step may affect your specific environment, dependencies, and users.
Consult official documentation — This guide supplements, but does not replace, official vendor documentation and best practices.

FixWikiHub is not responsible for any damages arising from the use of this content. See our Terms of Use for more information.

Resources

Official Documentation & Further Reading

For authoritative information, consult the official documentation for the technologies discussed in this guide. Our troubleshooting content supplements, but does not replace, vendor documentation.

AWS Documentation — Official Amazon Web Services guides and API references
Kubernetes Documentation — Official Kubernetes documentation
Nginx Documentation — Official Nginx web server documentation
Apache Documentation — Official Apache HTTP Server documentation
Docker Documentation — Official Docker container documentation

Fix MySQL Slow Query

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Identify Slow Queries

Step 2: Analyze Query Execution

Step 3: Check Index Usage

Step 4: Update Statistics

Step 5: Check Lock Contention

Step 6: Optimize Query

Step 7: Check MySQL Configuration

Step 8: Check Resource Usage

Step 9: Use Query Hints

Step 10: Monitor Query Performance

MySQL Slow Query Checklist

Verification

People also search for

Browse Guides from Other Categories

WordPress

SSL

DNS

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading

Fix MySQL Slow Query

Introduction

Symptoms

Common Causes

Step-by-Step Fix

Step 1: Identify Slow Queries

Step 2: Analyze Query Execution

Step 3: Check Index Usage

Step 4: Update Statistics

Step 5: Check Lock Contention

Step 6: Optimize Query

Step 7: Check MySQL Configuration

Step 8: Check Resource Usage

Step 9: Use Query Hints

Step 10: Monitor Query Performance

MySQL Slow Query Checklist

Verification

Related Issues

Related Articles

People also search for

Share this guide

More Database Troubleshooting Guides

Browse Guides from Other Categories

Database Troubleshooting FAQs

FixWikiHub Editorial Team

Disclaimer & Safety Guidelines

Official Documentation & Further Reading