MySQL Optimization in Practice: Faster Queries with EXPLAIN, Better Indexes, and Safer Pagination (Hands-On)

When an app starts to feel “randomly slow,” MySQL is often the bottleneck: a few unindexed filters, a heavy sort, or pagination that scans millions of rows. This guide shows a practical workflow you can apply today: measure the slow query, read the execution plan, add the right index, and adjust the query pattern. You’ll get copy-paste SQL examples you can run on a local MySQL 8+ instance.

1) A realistic schema and dataset

Let’s optimize something concrete: a common “orders list” page that filters by status, date range, and customer, sorted by newest first.

-- Example tables CREATE TABLE customers ( id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL UNIQUE, created_at DATETIME NOT NULL ); CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT NOT NULL, status ENUM('pending','paid','shipped','canceled') NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(id) ); -- Start with only a basic index (often what people forget to expand) CREATE INDEX idx_orders_customer ON orders(customer_id);

A “slow-ish” query we’ll optimize:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

This looks harmless, but OFFSET 5000 can force MySQL to scan and discard thousands of rows before returning 50.

2) Measure first: confirm the problem

Two quick tools to get signal:

EXPLAIN (what MySQL plans to do)
EXPLAIN ANALYZE (what actually happened, with timings)

EXPLAIN SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

What you’re looking for in the output:

type should ideally be ref, range, or const — not ALL (full table scan).
rows is MySQL’s estimate of how many rows it will examine.
Extra contains warnings like Using filesort (means it can’t satisfy the sort from an index).

Then run the measured plan:

EXPLAIN ANALYZE SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

If you see a big “rows examined” count and “filesort,” it’s a strong sign you need a composite index that matches the WHERE + ORDER BY.

3) Build an index that matches the query shape

Rule of thumb for composite indexes:

Put equality filters first (e.g., status = ...)
Then range filters (e.g., created_at >= ...)
Then columns used for sorting (e.g., ORDER BY created_at DESC)

For our query, a strong index is:

CREATE INDEX idx_orders_status_created_at ON orders(status, created_at DESC);

Now rerun EXPLAIN/EXPLAIN ANALYZE. Ideally:

MySQL uses idx_orders_status_created_at
The plan shows range access on created_at
The “filesort” disappears or becomes less expensive

Why this works: MySQL can jump directly to the portion of the index matching status='paid', walk the created_at range in sorted order, and stop once it has enough rows (though OFFSET can still hurt).

4) Fix the real killer: OFFSET pagination

OFFSET pagination gets slower as the page number grows. A better pattern is keyset pagination (a.k.a. “seek method”), which uses the last seen value as a cursor.

Instead of page 101 with OFFSET, the client sends the last row’s created_at (and id as a tie-breaker) from the previous page.

First, make the index cursor-friendly:

CREATE INDEX idx_orders_status_created_id ON orders(status, created_at DESC, id DESC);

Now query the “next page”:

-- First page (no cursor yet) SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' ORDER BY created_at DESC, id DESC LIMIT 50; -- Next page (cursor provided by the last row of the previous page) -- Suppose the last row had created_at='2026-01-18 10:22:11' and id=88421 SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' AND ( created_at < '2026-01-18 10:22:11' OR (created_at = '2026-01-18 10:22:11' AND id < 88421) ) ORDER BY created_at DESC, id DESC LIMIT 50;

Benefits:

Stable performance even on “page 500.”
Prevents duplicate/missing rows when new data is inserted (with the tie-breaker id).
Works great with an index that matches the ordering.

5) Avoid “SELECT *” and use covering indexes (when it matters)

If your query only needs a few columns, selecting fewer fields reduces IO. You can go further: build a covering index so MySQL can answer the query from the index alone (no table lookups).

Example: your list view needs only (id, created_at, total_cents) for paid orders sorted by date:

CREATE INDEX idx_orders_paid_list_cover ON orders(status, created_at DESC, id DESC, total_cents); SELECT id, total_cents, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 50;

In EXPLAIN, look for Using index in the Extra column (that’s usually the hint you’re getting an index-only plan). Don’t overdo covering indexes everywhere—each index has a write cost—use them for your hottest list queries.

6) Common traps that sabotage indexes

Even with indexes, small query mistakes can force scans.

Functions on indexed columns (prevents index usage)

-- Bad (can't use created_at range efficiently) WHERE DATE(created_at) = '2026-01-10'
-- Good
WHERE created_at >= '2026-01-10 00:00:00'
AND created_at < '2026-01-11 00:00:00'

Leading wildcard LIKE (can’t use a normal B-Tree index)

-- Bad WHERE email LIKE '%@example.com'
-- Better approaches:
-- 1) store domain separately and index it
-- 2) use FULLTEXT for certain search needs (not for domains)

Mismatched collations/implicit casts (especially joins)

-- If one side is VARCHAR and other is INT, MySQL may cast and skip indexes. -- Keep join keys consistent types and collations.

7) Join optimization: index the join and the filter

A frequent pattern is filtering orders by customer email. You might write:

SELECT o.id, o.total_cents, o.created_at FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.email = '[email protected]' AND o.status = 'paid' ORDER BY o.created_at DESC LIMIT 50;

Indexes that help:

customers(email) (already unique in our schema)
orders(customer_id, status, created_at DESC, id DESC) if you frequently filter by customer + status and sort by date

CREATE INDEX idx_orders_customer_status_created ON orders(customer_id, status, created_at DESC, id DESC);

Tip: Put the column that narrows the most first. If most screens start from a specific customer, customer_id leading is great. If most screens start from status/date and only sometimes join customer, prefer the earlier (status, created_at) index. Real apps often need both for different endpoints—measure which queries are hottest before adding many indexes.

8) A quick “optimization checklist” you can apply on any slow query

Run EXPLAIN ANALYZE and note: access type, rows examined, “filesort,” and join order.
Make the WHERE clause sargable (no functions on indexed columns; avoid implicit casts).
Add a composite index that matches: equality filters → range filters → sort order.
Replace deep OFFSET pagination with keyset pagination using a stable cursor.
Select only the columns you need; consider covering indexes for high-traffic list endpoints.
After changes: rerun EXPLAIN ANALYZE and compare rows examined/time.

9) Putting it together: a “before vs after” query pair

Before (works, but degrades on large datasets):

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50 OFFSET 5000;

After (scales better):

-- Index CREATE INDEX idx_orders_status_created_id ON orders(status, created_at DESC, id DESC); -- Keyset pagination SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE status = 'paid' AND ( created_at < '2026-01-18 10:22:11' OR (created_at = '2026-01-18 10:22:11' AND id < 88421) ) ORDER BY created_at DESC, id DESC LIMIT 50;

If you implement only one improvement from this article, make it this: keyset pagination + a composite index aligned with your filters and sort. That combo removes the “slow on page 200” problem and makes performance predictable.

Next step to deepen your skill: pick your slowest endpoint, copy its SQL, run EXPLAIN ANALYZE, and try one targeted index change. You’ll learn more from one real query than from ten generic tips.

MySQL Optimization in Practice: Faster Queries with EXPLAIN, Better Indexes, and Safer Pagination (Hands-On)