MySQL Query Optimization: Best Practices to Improve Performance
Written By ROMAN AGABEKOV | MAY 15, 2025
Last update | OCTOBER 22, 2026
To optimize a MySQL SELECT query, find the work it does unnecessarily: reading rows it will discard, sorting a large result, repeating lookups, or making too many calls from your application. Use the execution plan to identify that work, choose a targeted change, and compare the same result before and after.
A useful index can solve one access problem, while a query rewrite or fewer database calls can solve another. Start with the decision table below, then follow a SELECT example from the application's requirement to a measured improvement.
A useful index can solve one access problem, while a query rewrite or fewer database calls can solve another. Start with the decision table below, then follow a SELECT example from the application's requirement to a measured improvement.
Contents
- How to Optimize a MySQL SELECT Query
- MySQL SELECT Optimization Example: Filter, Sort, and LIMIT
- MySQL Query Optimization Techniques and Best Practices
- Choose the Next Query to Optimize
- Verify Query Performance Before and After a Change
- When a Query Rewrite Will Not Fix the Slowdown
- How Releem Helps You Find and Optimize High-Impact Queries
How to Optimize a MySQL SELECT Query
Start with the symptom, not a favorite optimization technique.
Use this five-step workflow:
What you observe | What to investigate first | What would support the change |
Many rows are read to return a small result | Filters and an index that matches them | Less work in the plan, with the same returned rows |
Filtering is reasonable, but sorting is expensive | The relationship between WHERE, ORDER BY, the index, and LIMIT | Fewer rows to sort, or an index-ordered access path |
A function on a filtered column prevents a useful lookup | An equivalent range condition or an appropriate expression index | The same boundaries and meaning, with a better access path |
A join produces a large intermediate result | Join relationships, indexes, and whether all matches are needed | Correct duplicates and NULL handling, with less intermediate work |
Individual statements are fast, but the endpoint is slow | Repeated calls, N+1 queries, and response size | Lower endpoint latency and fewer unnecessary calls |
The first run is slow, or the query waits under load | Cold data, locks, I/O, and resource contention | Evidence of the wait or resource bottleneck, rather than a guess about SQL |
Use this five-step workflow:
- Save the SQL, parameter values, required result, and current timing.
- Read the execution plan and identify the likely source of extra work.
- Test one change that addresses that work.
- Compare the returned rows, their order, and repeated execution times.
- Check the effect on the application and the rest of the workload.
MySQL SELECT Optimization Example: Filter, Sort, and LIMIT
An order-management screen needs the latest 20 paid orders for one tenant. It displays the order ID, creation time, and amount. Newer orders appear first, and the order ID breaks ties when timestamps match.
The Query and Its Baseline
We tested this example on MySQL Community Server 8.4.7 with InnoDB in an isolated local database. The table contained 300,000 orders. Tenant 42 had 100,000 orders, including 40,000 paid orders. A smaller tenant had 100 orders. Here is the starting schema:
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL,
tenant_id BIGINT UNSIGNED NOT NULL,
customer_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
total_cents INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
KEY idx_orders_tenant (tenant_id)
) ENGINE = InnoDB;Here, the application stores created_at as UTC. The screen runs:
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;The existing index locates a tenant's orders, but it does not organize them by payment status and creation time. For a tenant with a long order history, finding the newest paid orders can still involve filtering many rows and sorting the matches. LIMIT 20 limits the output, but it does not necessarily limit the search to 20 rows.
Before changing anything, save the returned rows and time several ordinary executions using the same data, parameters, connection path, and cache conditions.
Before changing anything, save the returned rows and time several ordinary executions using the same data, parameters, connection path, and cache conditions.
What EXPLAIN Reveals
Inspect the proposed execution plan:
EXPLAIN FORMAT=TREE
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;Look for the access path into orders, where the status filter is applied, and whether a separate sort is needed. An index lookup alone does not mean the query is efficient: it may still retrieve a large set and discard most of it.
EXPLAIN ANALYZE runs the SELECT and adds actual row counts and iterator timings. Run it in the test environment to inspect the work behind the query. The MySQL EXPLAIN reference defines those fields. Our EXPLAIN guide explains how to read them.
The measured baseline plan was:
EXPLAIN ANALYZE runs the SELECT and adds actual row counts and iterator timings. Run it in the test environment to inspect the work behind the query. The MySQL EXPLAIN reference defines those fields. Our EXPLAIN guide explains how to read them.
The measured baseline plan was:
-> Limit: 20 row(s) (cost=2385 rows=20) (actual time=87.7..87.7 rows=20 loops=1)
-> Sort: orders.created_at DESC, orders.id DESC, limit input to 20 row(s) per chunk (cost=2385 rows=149500) (actual time=87.7..87.7 rows=20 loops=1)
-> Filter: (orders.`status` = 'paid') (cost=2385 rows=149500) (actual time=0.911..81.6 rows=40000 loops=1)
-> Index lookup on orders using idx_orders_tenant (tenant_id=42) (cost=2385 rows=149500) (actual time=0.908..76 rows=100000 loops=1)- Read this from the bottom up: the tenant lookup produced 100,000 rows, the status filter passed 40,000, and a separate sort selected the newest 20. Those are actual iterator row counts with one loop, not the optimizer's estimates beside cost. The hypothesis is specific: an index that combines the two filters with the requested order could avoid processing the tenant's whole history.
Test One Targeted Change
- The query has two equality filters followed by a fixed ordering. A candidate index for this access pattern is:
CREATE INDEX idx_orders_tenant_status_created_id
ON orders (tenant_id, status, created_at DESC, id DESC);Test this change on the isolated copy, not directly on a production table. It places one tenant's paid orders together and orders that group by the same columns as the SELECT. MySQL can use that ordering to retrieve the first matching entries without separately sorting the whole matching set. Whether it chooses that path is something to confirm in the new plan.
Keep the original SELECT unchanged so the comparison measures the effect of the index.
Keep the original SELECT unchanged so the comparison measures the effect of the index.
- The amount column is not in this index. Returning total_cents still requires reading the corresponding table records, so this is not a covering index for the SELECT. Adding more columns might avoid those reads, but would also make the index larger. Test the narrower candidate before expanding it.
Compare the Result and the Work
- After creating the index, MySQL chose this plan without an index hint:
-> Limit: 20 row(s) (cost=9241 rows=20) (actual time=0.945..0.947 rows=20 loops=1)
-> Index lookup on orders using idx_orders_tenant_status_created_id (tenant_id=42, status='paid') (cost=9241 rows=83508) (actual time=0.945..0.946 rows=20 loops=1)The index lookup now produced only the 20 required rows, and the separate sort disappeared. The SELECT, its parameters, and the data stayed unchanged. All returned IDs, timestamps, amounts, and their order matched before and after.
We measured ordinary SELECT executions separately from EXPLAIN ANALYZE: three series of 25 executions before and after the change, following warm-ups. These warm-cache measurements used one client over a local Unix socket and a 256 MiB InnoDB buffer pool.
The improvement appeared in all three series: baseline medians ranged from 77.17 to 82.01 ms, versus 1.00 to 1.03 ms with the new index. Removing the index brought the median back to 82.45 ms in another 25 executions.
We measured ordinary SELECT executions separately from EXPLAIN ANALYZE: three series of 25 executions before and after the change, following warm-ups. These warm-cache measurements used one client over a local Unix socket and a 256 MiB InnoDB buffer pool.
Check | Before the change | After the change |
Returned result | 20 rows | The same 20 rows, values, and order |
Selected index | idx_orders_tenant | idx_orders_tenant_status_created_id |
Separate sort | Present | Absent |
Actual rows produced by the index lookup, one loop | 100,000 | 20 |
Ordinary SELECT median | 79.85 ms | 1.01 ms |
Ordinary SELECT p95 | 102.97 ms | 1.11 ms |
The improvement appeared in all three series: baseline medians ranged from 77.17 to 82.01 ms, versus 1.00 to 1.03 ms with the new index. Removing the index brought the median back to 82.45 ms in another 25 executions.
Check Other Queries and Write Costs
We also measured the small tenant and tenant 42's list without the status filter, keeping each SELECT unchanged.
For the small tenant, the absolute saving was only about 0.05 ms. For the all-status list, MySQL kept the original tenant index and a separate sort. The median did not improve, and the tail was more variable. Once status is no longer fixed, the candidate index's ordering by status comes before its ordering by creation time. It does not supply the same global date order.
The extra index occupied 739 InnoDB pages, or 11.55 MiB, for the 300,000-row table. We measured its write cost using equal copies with and without the index: 30 batches of 1,000 rows per operation and copy, including commit time. Binary logging was disabled. Autocommit and innodb_flush_log_at_trx_commit=1 were enabled.
The index made the paid-order list faster, while adding storage and write work. Evaluate that trade-off against how often your application reads and changes these orders.
Query | Median before | Median after | p95 before | p95 after |
Paid orders, small tenant with 100 orders | 0.136 ms | 0.088 ms | 0.170 ms | 0.101 ms |
All statuses, tenant with 100,000 orders | 80.16 ms | 80.28 ms | 95.38 ms | 145.90 ms |
For the small tenant, the absolute saving was only about 0.05 ms. For the all-status list, MySQL kept the original tenant index and a separate sort. The median did not improve, and the tail was more variable. Once status is no longer fixed, the candidate index's ordering by status comes before its ordering by creation time. It does not supply the same global date order.
The extra index occupied 739 InnoDB pages, or 11.55 MiB, for the 300,000-row table. We measured its write cost using equal copies with and without the index: 30 batches of 1,000 rows per operation and copy, including commit time. Binary logging was disabled. Autocommit and innodb_flush_log_at_trx_commit=1 were enabled.
Write operation, 1,000 rows per batch | Median without candidate index | Median with candidate index |
Insert batch | 4.22 ms | 5.44 ms |
Update payment status | 5.64 ms | 7.82 ms |
The index made the paid-order list faster, while adding storage and write work. Evaluate that trade-off against how often your application reads and changes these orders.
MySQL Query Optimization Techniques and Best Practices
The example combines filtering, indexing, ordering, and validation. These six techniques apply that reasoning to other common query problems.
Match Indexes to Filters and Sort Order
Design an index around a query's access pattern rather than indexing each referenced column separately. For our order list, (tenant_id, status, created_at DESC, id DESC) supports the two fixed filters and the requested order.
A different query has a different requirement:
A different query has a different requirement:
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;Here, (tenant_id, created_at DESC, id DESC) is a candidate to evaluate. The previous index is not an equivalent ordering index when multiple statuses are included.
Do not reduce index design to "put the most selective column first." Equality filters, range conditions, ordering, and other queries all affect the decision. MySQL's multiple-column index documentation explains how the order of indexed columns affects their use.
Confirm the chosen access path and check whether an existing index already serves the purpose before adding another.
Do not reduce index design to "put the most selective column first." Equality filters, range conditions, ordering, and other queries all affect the decision. MySQL's multiple-column index documentation explains how the order of indexed columns affects their use.
Confirm the chosen access path and check whether an existing index already serves the purpose before adding another.
Write Searchable Filters Without Changing Their Meaning
A filter is often easier to serve with a normal index when it compares the indexed column directly with a value or range. For the UTC DATETIME column in our example, this daily filter:
SELECT id, created_at
FROM orders
WHERE tenant_id = 42
AND DATE(created_at) = '2026-09-14';can be written as an equivalent half-open range:
SELECT id, created_at
FROM orders
WHERE tenant_id = 42
AND created_at >= '2026-09-14 00:00:00'
AND created_at < '2026-09-15 00:00:00';The second form exposes the date range directly to an appropriate index. Verify the plan and compare results as sets: neither SELECT requests a particular output order. If the application's day is defined in another time zone, calculate the corresponding UTC boundaries rather than silently changing which orders belong to that day.
The same principle does not make different text searches interchangeable. LIKE 'acme%' asks for a prefix, while LIKE '%acme%' asks for a substring. A suitable B-tree index can help a prefix search, while a leading wildcard generally prevents that prefix-range lookup. Full-text search has different matching rules again. Choose the search behavior the application needs before choosing its access method.
The same principle does not make different text searches interchangeable. LIKE 'acme%' asks for a prefix, while LIKE '%acme%' asks for a substring. A suitable B-tree index can help a prefix search, while a leading wildcard generally prevents that prefix-range lookup. Full-text search has different matching rules again. Choose the search behavior the application needs before choosing its access method.
Optimize ORDER BY, LIMIT, and Pagination Together
For a list endpoint, investigate how MySQL finds the next rows in the requested order. A small LIMIT can still require substantial work if MySQL must read and sort a large candidate set first. An ordering index helps only when its column order fits the query, as the MySQL ORDER BY documentation explains.
Deep OFFSET pagination can also become expensive because the server must pass over earlier results. For a "next page" interface, carry the last displayed row's sort values as a cursor:
Deep OFFSET pagination can also become expensive because the server must pass over earlier results. For a "next page" interface, carry the last displayed row's sort values as a cursor:
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
AND (
created_at < '2026-09-14 18:25:00'
OR (
created_at = '2026-09-14 18:25:00'
AND id < 93821
)
)
ORDER BY created_at DESC, id DESC
LIMIT 20;The timestamp and ID must come from the last row of the previous page. The ID tie-breaker prevents equal timestamps from making the ordering ambiguous. Check the range access in the plan rather than assuming any cursor predicate will be efficient.
Use keyset pagination for forward navigation, and retain OFFSET where users need arbitrary page numbers. Define how the list should handle orders added or changed between requests.
Use keyset pagination for forward navigation, and retain OFFSET where users need arbitrary page numbers. Define how the list should handle orders added or changed between requests.
Check JOINs and Subqueries Before Rewriting Them
First decide whether the application needs matching detail rows or just proof that a match exists. Suppose customers.id is a primary key and the screen needs each customer who has at least one paid order:
SELECT c.id, c.email
FROM customers AS c
WHERE c.tenant_id = 42
AND EXISTS (
SELECT 1
FROM orders AS o
WHERE o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.status = 'paid'
);EXISTS expresses that requirement without returning one customer row for every matching order. If you replace it with a plain join, customers with multiple paid orders can appear multiple times. Adding DISTINCT changes the work again.
Check the access into both tables and the number of intermediate rows. An index on the order-side tenant, customer, and status columns is a candidate for this lookup. MySQL can transform eligible IN and EXISTS queries into semijoin strategies, so a hand-written join is not automatically faster. Preserve duplicates and NULL behavior when evaluating a rewrite.
Check the access into both tables and the number of intermediate rows. An index on the order-side tenant, customer, and status columns is a candidate for this lookup. MySQL can transform eligible IN and EXISTS queries into semijoin strategies, so a hand-written join is not automatically faster. Preserve duplicates and NULL behavior when evaluating a rewrite.
Read and Return Only What the Application Needs
If a screen needs an order ID, timestamp, and amount, request those columns rather than every column:
SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
AND status = 'paid'
ORDER BY created_at DESC, id DESC
LIMIT 20;This keeps unrelated fields, such as a large order payload, out of the response if they exist in your real schema. It can reduce transfer and application processing, and an appropriate projection may make a covering access path possible.
Check response size and execution work separately: a shorter column list reduces the returned data, while filtering and the access path determine which rows MySQL reads.
Check response size and execution work separately: a shorter column list reduces the returned data, while filtering and the access path determine which rows MySQL reads.
Reduce Repeated Queries and N+1 Calls
A page can be slow even when each SQL statement is quick. Loading 20 orders and then fetching one customer for each order creates 20 additional database calls.
One option is to collect the distinct customer IDs and fetch the needed customers in a batch:
One option is to collect the distinct customer IDs and fetch the needed customers in a batch:
SELECT id, email
FROM customers
WHERE tenant_id = ?
AND id IN (?, ?, ?);These are prepared-statement placeholders. Build the placeholder list for the actual number of IDs and bind the values. Do not concatenate untrusted input into SQL. Map the returned customers back to the orders in the application. A carefully designed join is another option when it preserves the required result.
Measure call count and endpoint latency, not just the speed of the batch query. Application caching can help repeated reads too, but choose expiration and invalidation rules that preserve the required freshness and tenant isolation. MySQL 8.4 does not have the old MySQL query cache. It is not a setting to enable for this workflow.
Measure call count and endpoint latency, not just the speed of the batch query. Application caching can help repeated reads too, but choose expiration and invalidation rules that preserve the required freshness and tenant isolation. MySQL 8.4 does not have the old MySQL query cache. It is not a setting to enable for this workflow.
Choose the Next Query to Optimize
After improving one SELECT, prioritize the next candidate by its effect on the workload. A slow execution and a costly query group are related, but different, problems.
For a one-hour review window, consider these two groups:
The order-list lookup accumulates more execution time even though each call is much faster. Reducing its repeated work could be a better use of engineering time than starting with the longest individual query.
Cumulative execution time includes overlapping calls, so it can exceed the one-hour window and should not be read as CPU time. Also consider business impact: a checkout timeout or blocking query can take priority over the largest aggregate.
For a one-hour review window, consider these two groups:
Query group | Executions in the hour | Average execution time | Cumulative execution time |
Order-list lookup | 120,000 | 25 ms | 3,000 seconds |
Administrative report | 60 | 2 seconds | 120 seconds |
The order-list lookup accumulates more execution time even though each call is much faster. Reducing its repeated work could be a better use of engineering time than starting with the longest individual query.
Cumulative execution time includes overlapping calls, so it can exceed the one-hour window and should not be read as CPU time. Also consider business impact: a checkout timeout or blocking query can take priority over the largest aggregate.
- Use the same collection period for execution counts and timings. If you work from cumulative Performance Schema counters, compare compatible snapshots and account for resets or lost entries. A lifetime total is not an hourly result.
Verify Query Performance Before and After a Change
Use this checklist to confirm that the change improves both the query and the application:
- Preserve the task. Compare required rows, values, duplicate behavior, and ordering. Test empty results, common parameters, and a large tenant, not only one convenient request.
- Compare like with like. Keep data, parameters, server version, and measurement path comparable. Separate cold-cache observations from warmed executions, and record whether other workload was present.
- Measure ordinary executions. Use EXPLAIN ANALYZE to investigate work, but measure latency separately. Repeat observations, as a single fast run may reflect cache state or noise.
- Check the application. Measure endpoint latency, database calls, and response size. Calculate p95 from individual observations, not from an average execution time.
- Check the cost elsewhere. Look for write regressions, extra storage, and slower neighboring queries after an index or query-shape change.
- Plan the production change. Review the SQL, deployment method, monitoring, and recovery action with the database owner, including the resources and locks needed to create the index.
- For a query rewrite, the recovery action may be restoring the previous application code. An index change needs its own reviewed DDL procedure. Issuing ROLLBACK is not an undo button for CREATE INDEX.
When a Query Rewrite Will Not Fix the Slowdown
Some slowdowns need a different next step:
For that broader investigation, follow the MySQL Performance Tuning Guide.
Pattern | Next investigation |
Long elapsed time with lock waits | The blocking transaction and why the wait exists |
Slow first run, faster repeat runs | Data and index reads, cache state, and the same query parameters |
Many queries slow at the same time | CPU, storage latency, memory pressure, and concurrency |
Database timing improves but endpoint timing does not | Application work, connection waits, network time, and repeated calls |
How Releem Helps You Find and Optimize High-Impact Queries
- Working through a single SELECT is manageable. Finding the next important query, understanding its context, and checking changes across a growing workload takes more time. Releem brings query execution statistics and optimization recommendations into the same workflow, so your team can spend less time hunting and more time improving the application.
Find the Queries Worth Your Attention
- Open Query Analytics and select the period you want to investigate. Compare execution count, average execution time, and Load on Total Time. Sort by total time to find query groups that accumulate substantial database time, then check whether they affect the endpoint or business operation you want to improve.
Use frequency and execution time together to decide which query to investigate next.
This surfaces both individually slow statements and frequently repeated queries that are easy to overlook. It gives the team a concrete starting point instead of a generic list of tuning rules.
This surfaces both individually slow statements and frequently repeated queries that are easy to overlook. It gives the team a concrete starting point instead of a generic list of tuning rules.
Move from a Query to a Practical Recommendation
Open a query to inspect its SQL, EXPLAIN plan, and schema details. Use Get Recommendations to investigate missing indexes and query rewrites in that context.
Releem supports automatic weekly analysis of impactful queries and on-demand optimization for a selected query. Engineers review the recommendations, test the proposed SQL or index, and apply the chosen change through their normal workflow.
That keeps the useful parts of manual optimization together: the reason to prioritize a query, the details needed to understand it, and a concrete improvement to evaluate. Explore Releem's SQL query optimization for the recommendation workflow.
Releem supports automatic weekly analysis of impactful queries and on-demand optimization for a selected query. Engineers review the recommendations, test the proposed SQL or index, and apply the chosen change through their normal workflow.
That keeps the useful parts of manual optimization together: the reason to prioritize a query, the details needed to understand it, and a concrete improvement to evaluate. Explore Releem's SQL query optimization for the recommendation workflow.
Check What Changed and Keep Improving
After deploying an improvement, return to the query's execution statistics over a comparable period. Read changes in average time together with execution count and total time: a workload that doubled is different from an unchanged workload that became slower. Use application measurements to confirm that users benefit too.
The goal is a repeatable cycle: find high-impact work, understand the cause, test the right fix, and observe the result. Releem helps you keep that cycle moving as your data and application change.
Find Your Highest-Impact Queries
The goal is a repeatable cycle: find high-impact work, understand the cause, test the right fix, and observe the result. Releem helps you keep that cycle moving as your data and application change.
Find Your Highest-Impact Queries
Article by
- Founder & CEORoman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.
Get your own database advisor
Try Releem premium today for FREE!
Try Releem premium today for FREE!