How to find and optimize slow MySQL queries

1. Find slow queries with the slow query log
2. Diagnose with EXPLAIN
3. Add the right index
4. Rewrite index-unfriendly queries
5. EXPLAIN ANALYZE on MySQL 8.0
6. Conclusion

When a request is slow, the database is usually to blame, and the database is usually slow because of a missing index or a query written in a way the optimizer can't use. MySQL query optimization isn't guesswork: there's a repeatable loop. Find the slow queries with the slow query log, diagnose each one with EXPLAIN, then fix it with an index or a rewrite. This guide walks through that loop end to end.

#Find slow queries with the slow query log

You can't optimize slow MySQL queries until you know which ones are slow. The slow query log records every statement that takes longer than a threshold you set.

Check the current settings:

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Enable it at runtime (no restart needed) and log anything over half a second:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

To make it survive a restart, set the same values in my.cnf (usually /etc/mysql/mysql.conf.d/mysqld.cnf):

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

log_queries_not_using_indexes is worth turning on temporarily, it catches full table scans even when they're currently fast, before the table grows.

The raw log is noisy. Use mysqldumpslow to aggregate it, so you see the worst offenders first instead of one line per execution:

# Top 10 queries by total time spent
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-s t sorts by total time (the queries actually costing you the most), -t 10 limits to ten results. Start at the top of that list.

#Diagnose with EXPLAIN

Once you have a slow query, put EXPLAIN in front of it to see how MySQL plans to run it:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

Three columns tell you almost everything:

• type — the access method. ALL means a full table scan, every row read, and is the clearest sign of trouble. ref, eq_ref, range, or const mean an index is doing the work.
• key — the index MySQL chose. NULL means no index is being used for this query.
• rows — the estimated number of rows MySQL expects to examine. A number close to the table's total row count confirms a scan.

So type = ALL, key = NULL, and a large rows estimate together mean the query is scanning the whole table. That's the query to fix next.

#Add the right index

Most of the time, the fix for a scanning query is an index on the columns in the WHERE clause. For the query above, a composite index covering both filtered columns lets MySQL jump straight to the matching rows:

ALTER TABLE orders ADD INDEX idx_orders_customer_status (customer_id, status);

Re-run EXPLAIN and confirm type is now ref and key shows your new index. Don't index blindly, though: indexes slow down writes and the column order matters. For when to add a composite index, the leftmost-prefix rule, and the write cost, see the full walkthrough in how database indexing works.

#Rewrite index-unfriendly queries

An index only helps if the query is written so MySQL can use it. These rewrites fix the most common cases where key stays NULL even after you add an index.

Don't SELECT *. Selecting only the columns you need keeps result sets small and can let a covering index satisfy the query without touching the table at all:

-- Reads every column, can't be covered by an index
SELECT * FROM orders WHERE customer_id = 42;

-- Only what you need
SELECT id, total, created_at FROM orders WHERE customer_id = 42;

Don't wrap an indexed column in a function. The index on created_at is useless here because MySQL has to compute DATE() for every row:

-- Can't use the index
SELECT id FROM orders WHERE DATE(created_at) = '2026-06-23';

-- Range condition, uses the index
SELECT id FROM orders
WHERE created_at >= '2026-06-23' AND created_at < '2026-06-24';

Avoid a leading wildcard in LIKE. '%smith' forces a scan; 'smith%' can use the index because the prefix is fixed:

SELECT id FROM customers WHERE name LIKE 'smith%';

Prefer range conditions and always limit large result sets. Returning 100,000 rows to the application is slow regardless of indexing, page the results:

SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

#EXPLAIN ANALYZE on MySQL 8.0

EXPLAIN shows the optimizer's plan. On MySQL 8.0, EXPLAIN ANALYZE actually runs the query and reports real timings per step, so you can see where the time genuinely goes rather than relying on estimates:

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42;

If you're still on 5.7 you won't have this, and you're missing optimizer improvements and better index limits too, see upgrading MySQL 5.7 to 8.0 on Ubuntu. Server-wide tuning (buffer pool size, connection limits) is a separate lever covered in optimizing MySQL performance.

#Conclusion

MySQL query optimization is a loop, not a one-time fix: enable the slow query log to find the worst queries, run EXPLAIN to see why they're slow, then add a focused index or rewrite the query so the index can be used. Confirm every change with EXPLAIN before moving on, and keep watching the slow log as your data grows, the query that's fast today on ten thousand rows is the full table scan that pages you at ten million.