MySQL Query Optimization Checklist: What Should You Check First?
DB
Last updated on

MySQL Query Optimization Checklist: What Should You Check First?

When one API endpoint suddenly gets slow, many teams start by adding another index. In practice, that order is often wrong. A slow MySQL query is rarely just “missing one index.” The real problem may be access path, read volume, sorting cost, join fan-out, oversized results, or even lock waits inside a write path.

This post is for the moment when you think, “I found a slow query. What should I check first?”

  • how to confirm which query is actually slow
  • what to read first in EXPLAIN
  • how to separate index, row-volume, sort, join, result-size, and lock problems

The short version: slow MySQL queries are easier to fix when you narrow them down in order through access path, rows read, sorting, result size, and lock impact instead of rewriting SQL by instinct.

Quick answer

If you want a practical MySQL query optimization checklist, use this order:

  1. identify the real slow query and the slow stage
  2. inspect access path and estimated rows with EXPLAIN
  3. check whether the index matches the query shape, not just whether an index exists
  4. look for unnecessary row reads
  5. reduce SELECT *, oversized result sets, and large OFFSET scans
  6. inspect ORDER BY, GROUP BY, DISTINCT, and temporary-work cost
  7. check join input size and join order
  8. for writes, inspect lock waits and long transactions too
  9. compare before and after on realistic data volume

Following that sequence usually prevents the common trap of changing three things at once and learning nothing from the result.

1. Identify the real slow query before tuning anything

Query optimization is not mainly about making SQL look cleaner. It is about finding the real bottleneck first.

So the first question is not “can I rewrite this statement?” It is “is this actually the bottleneck?”

Separate these cases before you tune:

  • is it always slow, or only slow for some users or time windows?
  • did it become slow right after a deployment, or only after data volume grew?
  • is one SQL statement slow, or is the application calling a medium-cost query too many times?

An API that takes two seconds can mean very different things:

  • one SQL query takes two seconds
  • forty 50ms queries are being issued
  • the database takes 200ms and the rest is application work

So the first job in the checklist is to identify the real slow query precisely.

2. Read EXPLAIN before rewriting the SQL

Once you know which query is slow, the next step is usually EXPLAIN, not blind SQL editing.

Query Analysis Concept

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

At a beginner-to-intermediate level, four fields already tell you a lot:

  • type
  • key
  • rows
  • Extra

These signals are especially useful:

  • type = ALL: possible full scan
  • key = NULL: index may not be used
  • very large rows: too much data is being read
  • Extra contains Using filesort or Using temporary: sorting or temp work may be expensive

EXPLAIN matters because it shows how MySQL plans to reach the data, not whether the SQL text looks elegant.

If you want a deeper plan-reading walkthrough, continue with the MySQL EXPLAIN Guide.

3. Check whether the index matches the query pattern

One of the most common real-world mistakes is thinking “there is already an index, so indexing cannot be the problem.” That is rarely enough.

The better questions are:

  • does the leading part of the index match the WHERE condition?
  • are join keys indexed appropriately?
  • does the index also help the ORDER BY pattern?
  • are functions, casts, or transformations blocking index use?

For example, wrapping the indexed column in a function can reduce index usefulness:

-- often less index-friendly
WHERE DATE(created_at) = '2026-04-13'

-- often more index-friendly as a range predicate
WHERE created_at >= '2026-04-13'
  AND created_at < '2026-04-14'

Likewise, WHERE user_id = ? ORDER BY created_at DESC may need more than a filter-only index if the sort step is expensive.

The key idea is that an index is not just something the table “has.” It is a structure meant to serve a specific query shape well.

4. Ask whether the query is reading far too many rows

In many slow-query investigations, the biggest win is not micro-tuning CPU work. It is making the query read dramatically less data in the first place.

That means asking:

  • is the query reading only the rows it truly needs?
  • is the filter selective enough?
  • is LIMIT missing where it should exist?
  • is the query scanning a wider range than the use case actually needs?

If a page shows 20 recent rows but the database is reading tens of thousands before sorting and trimming, the real issue is read volume.

Large OFFSET pagination is a common source of this:

  • LIMIT 20 OFFSET 10000 often gets more expensive as users go deeper
  • keyset pagination may fit better for some high-volume lists

In practice, “can we read much less?” is often the highest-value optimization question.

5. Reduce result size and selected columns

People often focus on filters and indexes first, but some queries are slow mainly because they return too much data.

Check for these patterns:

  • SELECT * used by default
  • fetching columns the page or API does not actually need
  • pulling large text or JSON columns into list views unnecessarily

For a list page, a query like this is often enough:

SELECT id, title, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;

If the query also reads the full body, large metadata blobs, and extra fields that are not used, both I/O and transfer cost grow.

Selecting only what you need can also improve opportunities for covering indexes. So reducing result size is often not a minor cleanup; it can be a major performance improvement.

6. Inspect sorting, grouping, and temporary work

A query can have a decent filter and still be slow because the expensive part happens after the read, especially around ORDER BY, GROUP BY, and DISTINCT.

Check these questions:

  • does Extra show Using filesort?
  • does it show Using temporary?
  • is filtering reasonably narrow but sorting still expensive because the sort key is misaligned?
  • are you reducing the input set enough before grouping?

For example, “filter by user_id but sort by created_at” can behave very differently depending on index design.

Also, DISTINCT sometimes hides an upstream problem. It may look like a clean fix, but it can actually mean the query shape is creating too many duplicate rows before the final step.

That is why it often helps to ask not only “is DISTINCT expensive?” but also “why are so many duplicates being produced in the first place?“

7. For joins, input size and fan-out matter most

With joins, the length of the SQL statement is not the main issue. The bigger question is how much data each side is contributing before and during the join.

JOIN Optimization Concept

Use this checklist:

  • is one side narrowed enough before the join?
  • are join keys indexed?
  • is a one-to-many join exploding row counts?
  • are filters being applied later than they need to be?

Suppose you need the latest 20 orders with details. Narrowing the 20 orders first and then attaching details is very different from joining large order tables and detail tables first, then trimming the result at the end.

So the core join question is usually not “can we join faster?” It is “can we join after shrinking the inputs much more?“

8. For write queries, include lock and transaction impact

Not every slow query is a read-optimization problem. UPDATE, DELETE, and large INSERT statements can feel slow because of lock waits or long transactions rather than the plan alone.

Check for:

  • execution inside a long transaction
  • repeated writes to hotspot rows or ranges
  • lock wait timeout or deadlock signals in the same incident window
  • application code holding transactions open longer than needed

In those cases, improving SQL text alone may not solve the issue. Bigger wins may come from:

  • shrinking transaction scope
  • standardizing write order
  • reducing batch size
  • spreading hotspot updates

So if a write query feels slow, do not stop at EXPLAIN. Ask whether the real bottleneck is contention.

9. Verify against realistic data and keep a before/after baseline

Query tuning becomes repetitive when the conclusion is only “it feels faster now.” A simple before/after baseline is much more valuable.

At minimum, keep track of:

  • EXPLAIN before and after
  • latency or execution-time change
  • rows-read change
  • whether sort or temporary-work signals improved

Small test environments can easily hide the real problem. A query that looks fine on 10,000 local rows may degrade badly on tens of millions of production rows because the sort or scan behavior changes at scale.

So the last checklist question should always be:

“Does this improvement still hold on data that looks like production?”

Common mistakes

1. Adding one index and stopping there

The real bottleneck may be sort cost, result size, or join fan-out instead.

2. Editing the SQL without checking EXPLAIN

That often turns tuning into guesswork instead of diagnosis.

3. Treating SELECT * and large OFFSET values as harmless defaults

They may feel fine early on but often become bottlenecks as data grows.

4. Trusting only a tiny test environment

Different data distribution and scale can completely change the result.

FAQ

Q. Should I always add an index first when a query is slow?

No. First separate access-path issues from row-volume, sort, result-size, and lock problems. Indexes are important, but they are not always the first answer.

Q. What are the first warning signs to notice in EXPLAIN?

For most readers, type, key, rows, and Extra are the best first fields to watch. In particular, ALL, NULL, large rows, Using filesort, and Using temporary are strong early warning signs.

Q. How do I know whether to redesign the query or redesign the index?

If the query reads far too much data or sorts too much even after filtering, query shape may be the bigger problem. If the filter, join, or sort pattern has no matching index support, index design needs attention too. In practice, those decisions often belong together.

  • If you want a calmer walkthrough of execution plans, continue with the MySQL EXPLAIN Guide.
  • For a broader operational workflow around slow-query diagnosis, read the MySQL Slow Query Guide.
  • If the issue looks more like waiting than scanning, prioritizing transaction scope and lock ordering checks is a useful next step.

Start Here

Continue with the core guides that pull steady search traffic.

Sponsored

Related Posts

More in this category