A query that took 50 milliseconds six months ago now takes 12 seconds. Your application is crawling. Users are complaining. You know the database is the bottleneck, but you do not know where to start.
This guide walks you through finding and fixing slow MySQL queries in order of impact — highest payoff fixes first, deep tuning later.
Find and Fix Slow MySQL Queries
You cannot fix what you cannot see. MySQL has built-in tools to identify exactly which queries are dragging your application down.
Enable the Slow Query Log
This captures every query that exceeds a time threshold you set. Two minutes of configuration gives you a prioritized hitlist.
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL log_queries_not_using_indexes = ON;
This logs every query taking longer than 1 second and every query that skips indexes entirely. Adjust long_query_time based on your application — for a fast web app, even 0.5 seconds might be too slow.
Analyze the Log with mysqldumpslow
Reading raw log files is painful. Use mysqldumpslow to surface the worst offenders:
# Top 10 slowest queries by average time mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # Top 10 most frequent slow queries mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
Focus first on queries with a high rows-examined-to-rows-sent ratio. A ratio above 1000:1 almost always means a missing index — MySQL is reading thousands of rows to return a handful.
Use Performance Schema for Live Monitoring
If you need real-time data instead of log analysis:
SELECT digest_text, count_star, avg_timer_wait/1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10;
This shows you the top 10 slowest query patterns across your entire server right now.
Fix 1: Add Missing Indexes (Fixes ~70% of Slow Queries)
Missing indexes are the single most common cause of slow MySQL queries. Without them, MySQL scans every row in the table to find what you asked for. On a 500,000-row table, that means reading all 500,000 rows even if the query returns 3 results.
How to Confirm the Problem
Run EXPLAIN before the slow query:
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = ‘pending’;
Look at these columns in the output:
- type — if it shows ALL, that is a full table scan. Bad.
- key — if it shows NULL, no index is being used. Bad.
- rows — the number of rows MySQL estimates it will examine. If this is close to the total rows in the table, you need an index.
How to Fix It
Add an index on the columns in your WHERE clause:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
For queries that filter on multiple columns, use a composite index with the columns in order of selectivity, the most selective column first.
Important Caveats
Do not index every column. Every index slows down INSERT, UPDATE, and DELETE operations because MySQL must update every index on each write. Only add indexes that your actual queries use.
Also — wrapping an indexed column in a function kills the index:
-- Bad: index on created_at is ignored SELECT * FROM orders WHERE YEAR(created_at) = 2025; -- Good: rewrite as range, index works SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';
This single rewrite pattern fixes more slow queries than almost any other technique.
Fix 2: Stop Using SELECT *
SELECT * retrieves every column in the table even when you only need two or three. This wastes memory, increases network transfer, and prevents MySQL from using covering indexes.
-- Bad: pulls all 20 columns SELECT * FROM users WHERE email = '[email protected]'; -- Good: only what you need SELECT id, name, email FROM users WHERE email = '[email protected]';
A covering index — one that contains all the columns your query needs — lets MySQL answer entirely from the index without touching the table at all. This shows up as Using index in EXPLAIN and is one of the most effective optimizations for read-heavy queries.
Fix 3: Fix N+1 Query Problems
This is the most common performance killer in application code that talks to MySQL. It happens when your code runs one query to fetch N parent rows, then runs N more queries to fetch related data — one per parent.
-- One query to get 100 orders SELECT id FROM orders WHERE customer_id = 42; -- Then 100 separate queries, one per order SELECT * FROM order_items WHERE order_id = ?; -- (repeated 100 times) That is 101 database round trips instead of 1. Fix it with a JOIN or a single IN query: -- One query, one round trip SELECT o.id, oi.* FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.customer_id = 42;
If you use an ORM like Eloquent, Django ORM, or ActiveRecord, check for eager loading options — with() in Laravel, select_related() and prefetch_related() in Django, includes() in Rails. These exist specifically to prevent N+1.
Fix 4: Use EXPLAIN ANALYZE for Deeper Diagnosis
EXPLAIN shows what MySQL plans to do. EXPLAIN ANALYZE (available in MySQL 8.0+) actually runs the query and shows real execution times and row counts.
EXPLAIN ANALYZE SELECT customer_id, SUM(total) FROM orders WHERE created_at >= '2026-01-01' GROUP BY customer_id;
Use this when the optimizer’s estimates differ significantly from actual performance. If EXPLAIN says it will examine 1,000 rows but EXPLAIN ANALYZE shows it examined 500,000, your table statistics are stale. Fix that with:
ANALYZE TABLE orders;
Run ANALYZE TABLE periodically on high-write tables to refresh statistics. Stale statistics cause the optimizer to pick bad execution plans.
Fix 5: Optimize JOINs
Slow JOINs usually come from missing indexes on the JOIN columns or joining on mismatched data types.
— Make sure both sides of the JOIN have indexes
CREATE INDEX idx_orders_customer ON orders (customer_id); CREATE INDEX idx_customers_id ON customers (id);
Also check for implicit type coercion — if customer_id is INT in one table and VARCHAR in another, MySQL converts every row before comparing. This silently kills performance:
-- Bad: types don't match, coerces every row SELECT * FROM users WHERE user_id = '42'; -- Good: types match SELECT * FROM users WHERE user_id = 42;
Fix 6: Tune Your MySQL Configuration
Query optimization should come first, but server settings matter too. These are the highest-impact settings:
innodb_buffer_pool_size
This is the most important MySQL setting. It controls how much data and indexes MySQL keeps in memory instead of reading from disk. Disk reads are orders of magnitude slower than memory reads.
Set it to 60-75% of available RAM on a dedicated database server. If you are running MySQL alongside your application on the same server, be more conservative — 40-50%.
— Check current setting
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
Other Settings Worth Checking
- innodb_io_capacity — match to your storage IOPS
- sort_buffer_size — increase for sort-heavy workloads
- max_connections — check you are not hitting the ceiling
Always test configuration changes on a staging environment before applying to production.
Fix 7: Batch Large Operations
Large UPDATE or DELETE operations lock rows and block other queries. Batch them with LIMIT to reduce lock contention:
-- Bad: locks potentially millions of rows DELETE FROM logs WHERE created_at < '2025-01-01'; -- Good: batch in chunks DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 10000; -- Repeat until 0 rows affected
This keeps your database responsive while the cleanup runs in the background.
Fix 8: Consider Partitioning for Large Tables
If a table has grown past millions of rows and queries consistently filter by date range, partitioning splits the table into smaller physical segments MySQL can scan independently.
CREATE TABLE orders ( id BIGINT, order_date DATE, customer_id INT ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION p2026 VALUES LESS THAN (2027) );
A query filtering WHERE order_date >= ‘2026-01-01’ now only scans the 2026 partition instead of the entire table.
Partitioning is not a first-line fix. Use it when a table has genuinely outgrown what indexes alone can handle.
Fix 9: Cache Repeated Expensive Queries
Some queries are inherently expensive and run frequently — dashboard aggregations, report summaries, complex analytics. Instead of hitting MySQL every time, cache the results.
Common caching approaches:
- Redis or Memcached — store query results with a TTL, serve from memory on subsequent requests
- Application-level caching — your framework likely has built-in caching (Laravel Cache, Django cache framework)
- Materialized summary tables — pre-compute expensive aggregations into a separate table, refresh on schedule
Note: MySQL 8.0 removed the built-in query cache. If you are on MySQL 5.7 or earlier, be aware that the query cache can actually hurt performance on write-heavy workloads. For modern MySQL, use application-level caching instead.
Critical 2026 Update: MySQL 8.0 Has Reached End of Life
As of April 2026, MySQL 8.0 has officially reached End of Life. Oracle released version 8.0.46 as the final 8.0 release. No more security patches, no more bug fixes.
If you are running MySQL 8.0 in production, upgrading to MySQL 8.4 LTS is strongly recommended. Beyond the security implications, MySQL 8.4 fixed several optimizer bugs that directly caused slow queries in 8.0 — particularly around prepared DELETE and UPDATE statements and partitioned table scans.
MySQL 8.4 also brings automatic histogram updates when running ANALYZE TABLE, improved InnoDB buffer pool optimizations, and performance improvements for common query patterns.
Check your version right now:
SELECT VERSION();
If it starts with 8.0, plan your upgrade. MySQL’s official recommendation is to move to MySQL 8.4 LTS or MySQL 9.7 LTS.
Quick Diagnostic Checklist
When a query suddenly gets slow, work through this in order:
- Run EXPLAIN — is it doing a full table scan? Add an index.
- Check if the query wraps an indexed column in a function — rewrite as a range.
- Look for Using filesort or Using temporary in EXPLAIN — these are expensive operations that often indicate a missing index on ORDER BY or GROUP BY columns.
- Check if table statistics are stale — run ANALYZE TABLE.
- Look at table size — has it grown past a size where existing indexes stopped being selective?
- Check for lock contention — are other queries or batch operations blocking this one?
Most slow queries trace back to a missing index. Start there before investigating anything else.
FAQ
1. What is the fastest way to fix a full table scan?
Add an index to the column in the WHERE clause. Confirm the scan with EXPLAIN — if the type column shows ALL, that is a full table scan. Adding the right index typically reduces query time from seconds to milliseconds.
2. Can too many indexes cause problems?
Yes. Every index slows down write operations because MySQL must update all indexes on each INSERT, UPDATE, or DELETE. Only add indexes that your queries actually use. Check for unused indexes with sys.schema_unused_indexes before adding new ones.
3. What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the optimizer’s estimated plan — what MySQL intends to do. EXPLAIN ANALYZE actually runs the query and shows real execution times and row counts, which is more accurate for diagnosing problems. Available in MySQL 8.0 and later.
4. Is MySQL 8.0 still safe to use in 2026?
MySQL 8.0 reached End of Life in April 2026 with version 8.0.46 as the final release. It no longer receives security patches or bug fixes from Oracle. Migrating to MySQL 8.4 LTS is strongly recommended. Cloud providers like AWS and Google Cloud offer extended support windows, but these are time-limited and often incur additional costs.
5. Should I use a composite index or multiple single-column indexes?
For queries that filter on multiple columns in the WHERE clause, a single composite index is almost always faster than multiple separate indexes. MySQL can use multiple indexes per query but must merge the results, which is slower than reading from one well-designed composite index.
