MySQL Optimization for Real Apps: From “Slow” to “Snappy” with EXPLAIN + Indexing

If you’ve built a web app long enough, you’ve seen it: pages that felt instant in dev become sluggish in production. Most of the time, it’s not “MySQL being slow” — it’s a query (or two) doing far more work than you intended. The good news: you can usually fix the worst offenders with a repeatable workflow: measure → understand → index → verify.

This hands-on guide walks through practical MySQL optimization for junior/mid devs: using EXPLAIN, creating the right indexes (including covering indexes), avoiding common anti-patterns, and verifying the win.

1) Set up a realistic example

Let’s say you’re building a blog or ecommerce dashboard with two core queries:

List recent posts by author (with pagination).
Search posts by status and date range.

Here’s a simplified schema (InnoDB):

CREATE TABLE posts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, author_id BIGINT UNSIGNED NOT NULL, status ENUM('draft','published','archived') NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;

Seed enough rows (tens/hundreds of thousands) to feel the pain. You don’t need millions to learn the patterns.

2) Find the slow query (don’t guess)

In production, you’d use the slow query log and/or APM. Locally, you can still simulate real behavior:

Measure with your app’s timing logs
Run the query directly in MySQL and note execution time
Use EXPLAIN to see what MySQL is doing

Example “recent posts by author” query:

SELECT id, title, created_at FROM posts WHERE author_id = 42 AND status = 'published' ORDER BY created_at DESC LIMIT 20 OFFSET 0;

This looks harmless. But without indexes, MySQL may scan lots of rows and sort them.

3) Read EXPLAIN like a checklist

Run:

EXPLAIN SELECT id, title, created_at FROM posts WHERE author_id = 42 AND status = 'published' ORDER BY created_at DESC LIMIT 20 OFFSET 0;

What you’re looking for (high-level):

type: Avoid ALL (full table scan) for hot paths.
rows: Rough estimate of rows examined — lower is better.
key: Which index is used (if any).
Extra: Watch for Using filesort (sort operation) and Using temporary.

If you see type=ALL and Using filesort, you’re paying for a scan + sort. The fix is usually an index that matches your WHERE and ORDER BY.

4) Create a composite index that matches the query

For this query, MySQL filters by author_id and status, then orders by created_at. A composite index in that order is often ideal:

CREATE INDEX idx_posts_author_status_created ON posts (author_id, status, created_at DESC);

Now re-run EXPLAIN. You want to see:

key=idx_posts_author_status_created
Much smaller rows estimate
No Using filesort (often disappears if the index can serve the order)

Why this works: MySQL can jump directly to the author+status range in the index and read entries in the exact order of created_at, grabbing the first 20.

5) Use covering indexes for “list pages”

List endpoints often return only a few columns. If your index includes all selected columns, MySQL can avoid reading the table rows entirely (fewer I/O operations). This is called a covering index.

Our query selects id, title, created_at. The current index includes author_id, status, created_at — but not title. We can extend it:

DROP INDEX idx_posts_author_status_created ON posts; CREATE INDEX idx_posts_author_status_created_cover ON posts (author_id, status, created_at DESC, id, title);

Now EXPLAIN may show Extra containing Using index, which often indicates the query is served from the index without touching the table.

Trade-off: covering indexes speed reads but cost disk space and slow writes (INSERT/UPDATE) because more index data must be maintained. Use them for hot, read-heavy endpoints.

6) Fix pagination: avoid huge OFFSETs with keyset pagination

Classic pagination uses LIMIT 20 OFFSET 40000. That becomes slow because MySQL still has to walk past those 40,000 rows before returning the next 20.

Instead, use keyset pagination (“seek method”). You remember the last row from the previous page (usually created_at + id as a tiebreaker).

First page:

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

Next page (pass the last row’s created_at and id):

SELECT id, title, created_at FROM posts WHERE author_id = 42 AND status = 'published' AND (created_at < '2026-03-10 12:30:00' OR (created_at = '2026-03-10 12:30:00' AND id < 987654)) ORDER BY created_at DESC, id DESC LIMIT 20;

To support this, your index should include the ordering columns:

CREATE INDEX idx_posts_author_status_created_id ON posts (author_id, status, created_at DESC, id DESC);

Keyset pagination is one of the highest ROI improvements for infinite-scroll feeds, admin lists, and “recent activity” pages.

7) Don’t sabotage indexes with common WHERE mistakes

Even with indexes, certain patterns force MySQL to work harder:

Functions on indexed columns (prevents index range scans):
Bad: WHERE DATE(created_at) = '2026-03-18'
Better: WHERE created_at >= '2026-03-18 00:00:00' AND created_at < '2026-03-19 00:00:00'
Leading wildcards in LIKE (can’t use a normal b-tree index):
Bad: WHERE title LIKE '%docker%'
Better: consider FULLTEXT indexes for this use case.
SELECT * on list pages: you lose the chance for a covering index and pull large columns (like TEXT) unnecessarily.

8) Optimize a date-range query with the right composite index

Say you have an endpoint:

SELECT id, author_id, title, created_at FROM posts WHERE status = 'published' AND created_at >= '2026-01-01' AND created_at < '2026-02-01' ORDER BY created_at DESC LIMIT 50;

A good index here is:

CREATE INDEX idx_posts_status_created ON posts (status, created_at DESC);

Rule of thumb: Put equality filters first (status = ...), then range filters and ordering (created_at). This often gives MySQL a tight index range in the correct order.

9) Verify in the application (example: Python)

After you add indexes, verify your endpoint got faster. Here’s a tiny Python snippet using mysql-connector-python to time a query (you can adapt to your stack):

import time import mysql.connector cnx = mysql.connector.connect( host="127.0.0.1", user="app", password="secret", database="appdb", ) cur = cnx.cursor(dictionary=True) query = """ SELECT id, title, created_at FROM posts WHERE author_id = %s AND status = 'published' ORDER BY created_at DESC, id DESC LIMIT 20 """ start = time.perf_counter() cur.execute(query, (42,)) rows = cur.fetchall() elapsed_ms = (time.perf_counter() - start) * 1000 print(f"Fetched {len(rows)} rows in {elapsed_ms:.2f}ms") cur.close() cnx.close()

For real validation, test with production-like data volume and confirm that CPU, disk I/O, and p95 response times improve — not just one local run.

10) Practical indexing guidelines you can apply today

Index the columns you filter on frequently (especially in hot endpoints).
Match your index to your query shape: equality filters first, then ordering/ranges.
Use composite indexes deliberately; many single-column indexes rarely beat one well-chosen composite index.
Keep indexes minimal on write-heavy tables; every index slows inserts/updates.
Prefer keyset pagination over large offsets for scrolling lists.
Use EXPLAIN as your feedback loop before and after changes.

Wrap-up

MySQL optimization isn’t magic — it’s aligning your queries with indexes so the database can do less work. Start with the slowest endpoint, run EXPLAIN, add (or adjust) a composite index that matches WHERE + ORDER BY, consider covering indexes for list pages, and swap OFFSET pagination for keyset pagination when scale demands it.

If you want, share a slow query (and its EXPLAIN output) and I’ll suggest an index strategy and a safer pagination pattern for it.

MySQL Optimization for Real Apps: From “Slow” to “Snappy” with EXPLAIN + Indexing