How to Optimize MySQL Query Performance
The most effective ways to optimize MySQL queries are adding proper indexes, rewriting inefficient queries, avoiding SELECT *, and using EXPLAIN to understand query execution plans. Most slow queries are slow because of missing indexes, not because MySQL is slow.
Query optimization follows a consistent pattern: a query that ran fine in development becomes slow in production because the data volume changed. A query that handles 1,000 rows in 5ms might handle 1,000,000 rows in 5 seconds. Here is how to fix that.
Most performance problems in production come down to a handful of recurring mistakes: missing indexes, unnecessary column reads, and subqueries that could have been joins. The good news is that these are all straightforward to fix once you know what to look for. The techniques below are ordered from highest impact to lowest, so start at the top and work your way down.
Start with EXPLAIN
Before optimizing anything, understand what MySQL is actually doing. EXPLAIN shows you the query execution plan.
EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id;The output shows you:
- type - How MySQL accesses the table (ALL = bad, ref = good, eq_ref = best)
- key - Which index MySQL chose to use (NULL = no index used)
- rows - How many rows MySQL estimates it will scan
- Extra - Additional information (Using filesort = bad, Using index = good)
If you see type: ALL with a high row count, you need an index. This is the most common performance problem in production.
One thing to watch out for: EXPLAIN is based on table statistics, and those statistics can be stale. If your table has been heavily updated since the last ANALYZE TABLE, the row estimates might be way off. Run ANALYZE TABLE your_table periodically, especially after bulk inserts or deletes, to keep the optimizer's estimates accurate. Without fresh statistics, even a well-intentioned index might be ignored by the optimizer.
Add Indexes for WHERE Clauses
The single most impactful optimization is adding indexes for columns used in WHERE clauses.
-- Before: Full table scan (1,000,000 rows)
SELECT * FROM orders WHERE user_id = 123;
-- Type: ALL, Rows: 1000000, Time: 1.2s
-- Add index
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- After: Index lookup (3 rows)
SELECT * FROM orders WHERE user_id = 123;
-- Type: ref, Rows: 3, Time: 0.001sThat is a 1,200x speedup from a single index. This happens all the time in production.
Think about which queries your application runs most frequently. A user-facing API endpoint that serves thousands of requests per minute is a higher priority than a one-off analytics query. Start by profiling your actual traffic, not by guessing which queries might be slow. Tools like MySQL's performance schema or a query monitoring service can show you exactly where the load is.
Add Composite Indexes for Multi-Column Queries
If your WHERE clause filters on multiple columns, create a composite index. The order of columns matters.
-- Query filters on user_id AND status
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';
-- Good composite index (matches the query)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Bad: status-only index (does not help this query)
CREATE INDEX idx_orders_status ON orders(status);The rule: put the most selective column first. user_id is more selective than status because there are more unique user IDs than status values.
Avoid SELECT *
SELECT * is not just bad practice, it is a performance problem. It forces MySQL to read every column from disk, even if you only need two columns.
-- Bad: Reads all columns
SELECT * FROM orders WHERE user_id = 123;
-- Good: Reads only needed columns
SELECT id, total, status FROM orders WHERE user_id = 123;If you have a covering index (an index that contains all the columns you need), MySQL can answer the query entirely from the index without reading the table data. This is called an index-only scan and it is very fast.
In practice, this means that when you write a query, think about which columns you actually use in the application. If you are building a list view that shows order ID, total, and status, do not pull in created_at, updated_at, shipping_address, and a dozen other fields you never display. Fewer columns means less I/O, less memory usage, and a better chance of hitting a covering index.
Rewrite Subqueries as JOINs
Correlated subqueries are often slow because they execute once per row. Rewriting them as JOINs lets MySQL optimize the entire query at once.
-- Slow: Correlated subquery (executes 1,000,000 times)
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);
-- Fast: JOIN (executes once)
SELECT o.* FROM orders o
JOIN (
SELECT user_id, AVG(total) as avg_total
FROM orders
GROUP BY user_id
) avg_orders ON o.user_id = avg_orders.user_id
WHERE o.total > avg_orders.avg_total;Use LIMIT for Large Result Sets
If you only need the first N rows, use LIMIT. MySQL will stop scanning as soon as it finds enough rows.
-- Without LIMIT: Scans all matching rows
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
-- Scans 500,000 rows, sorts them, returns all
-- With LIMIT: Stops early
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;
-- Scans until it finds 10 rows, stopsLIMIT becomes critical for pagination in APIs. Instead of loading all matching rows and slicing them in application code, push the LIMIT down into the query. For deep pagination (like page 1,000 of results), consider keyset pagination instead of OFFSET, because OFFSET still requires MySQL to scan and discard all preceding rows. A query with WHERE id > last_seen_id LIMIT 20 is far more efficient than LIMIT 19980, 20.
Optimize ORDER BY with Indexes
If you frequently sort by a column, add an index on that column. Without an index, MySQL must scan all matching rows, sort them in memory, and return the result.
-- Slow: Full scan + filesort
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- Extra: Using filesort
-- Add index
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
-- Fast: Index scan (already sorted)
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;
-- Extra: Using indexUse EXPLAIN ANALYZE for Real Execution Times
EXPLAIN shows estimates. EXPLAIN ANALYZE actually runs the query and shows real execution times. It is more useful for understanding actual performance.
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- Output includes actual execution time:
-- -> Group aggregate: (actual time=0.1..0.15 rows=100)
-- -> Hash join: (actual time=0.08..0.12 rows=500)
-- -> Table scan on u: (actual time=0.01..0.02 rows=100)
-- -> Hash: (actual time=0.05..0.07 rows=500)One caveat: EXPLAIN ANALYZE runs the query for real, so it has side effects. It will acquire locks, modify data if the query is an INSERT or UPDATE, and consume resources. Use it on read-only queries or in a staging environment. For write queries, stick with plain EXPLAIN and interpret the plan without actually executing.
Monitor Slow Queries
Enable the slow query log to find queries that take longer than a threshold.
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- Log queries taking more than 1 second
-- Check slow queries
SHOW VARIABLES LIKE 'slow_query%';Review the slow query log regularly. The queries that appear most often or take the longest are your optimization priorities.
In production, consider setting up automated alerts when the slow query log grows faster than usual. A sudden spike in slow queries often signals a deployment that introduced a regression, a missing index after a schema change, or a traffic pattern shift that exposed a previously hidden bottleneck. Catching these early saves you from paging through the log after users start complaining.
Common Optimization Mistakes
Adding Too Many Indexes
Every index you add speeds up reads but slows down writes. Each INSERT, UPDATE, and DELETE must update every index on the table. A table with 15 indexes might have fast SELECT queries, but INSERT operations become significantly slower because MySQL has to update 15 B-tree structures. Start with the slowest queries and add indexes incrementally.
Indexing Low-Selectivity Columns
Indexing a column with only a few distinct values (like a boolean or status column with 3 options) rarely helps. MySQL estimates that a status index will match a large percentage of rows, so it chooses a full table scan instead. Index columns with high cardinality (many unique values) first, like user IDs, email addresses, or timestamps.
Optimizing Queries That Are Already Fast
A query that runs in 2ms is not worth optimizing. Focus on queries that appear in the slow query log, cause user-visible delays, or show type: ALL in EXPLAIN on large tables. Time spent optimizing fast queries is time not spent on the real bottlenecks.
Key Takeaways
- EXPLAIN is the first step, not the last. Run it before and after every optimization. The execution plan tells you exactly what MySQL is doing and where the bottleneck is.
- Composite indexes beat single-column indexes for multi-column queries. The column order in the index matters, and range conditions should come last.
- SELECT * is a performance problem. It prevents covering index scans and forces MySQL to read every column from disk.
- Rewrite correlated subqueries as JOINs. A correlated subquery executes once per row. A JOIN with a derived table executes once for the entire query.
- Monitor the slow query log continuously. The queries that appear most often are your optimization priorities. Do not guess, measure.
FAQ
How do I know if a query needs optimization?
Run EXPLAIN. If you see type: ALL with a high row count, the query needs an index. If you see Using filesort or Using temporary, the query could be faster with a better index.
Also pay attention to the "rows" column in EXPLAIN output. If it shows 1,000,000 but your query only returns 5 rows, something is wrong with the index or the query structure. The goal is always to make the rows scanned close to the rows returned.
How many indexes should I add?
Add indexes for columns used in WHERE, JOIN ON, and ORDER BY clauses. Do not add indexes for every column. Each index slows down writes and uses disk space. Start with the slowest queries and add indexes incrementally.
Can indexes slow down queries?
Yes, in rare cases. If you have too many indexes, MySQL might choose the wrong one. If you have a low-selectivity index (like a boolean column), it might be faster to do a full table scan. But in most cases, the right index makes queries faster.
What is a covering index?
A covering index contains all the columns needed for a query. MySQL can answer the query entirely from the index without reading the table data. This is the fastest possible query execution.
How do I test if my optimization actually helped?
Run EXPLAIN ANALYZE before and after the change. Compare the actual execution times. Also check that the query plan changed as expected (type moved from ALL to ref, rows scanned decreased, Using filesort disappeared). If the plan did not change, the optimization did not help.
Written by
MasterSQL