MySQL Optimization for Web Apps: Indexing, Query Tuning, and Safer Pagination (Hands-On)

When a web app “gets slow,” it’s often not the framework—it’s the database doing extra work. The good news: you don’t need wizard-level SQL to make MySQL fast. In this practical guide, you’ll learn a repeatable workflow: measure with EXPLAIN, add the right indexes, rewrite expensive queries, and avoid common performance traps like OFFSET pagination on large tables.

All examples work on MySQL 8.x (and mostly on 5.7). We’ll use a realistic scenario: an e-commerce-ish app with orders and users.

1) Set up a realistic schema (and a “slow query” to fix)

Let’s create two tables and a few indexes (intentionally not perfect yet):

-- users: 1M rows in real life CREATE TABLE users ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL, status ENUM('active','blocked') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, UNIQUE KEY uq_users_email (email), KEY idx_users_status (status) ) ENGINE=InnoDB; -- orders: 10M rows in real life CREATE TABLE orders ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, total_cents INT NOT NULL, state ENUM('new','paid','shipped','refunded','canceled') NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), KEY idx_orders_user_id (user_id), KEY idx_orders_created_at (created_at) ) ENGINE=InnoDB;

Now imagine your app shows “recent paid orders” with user emails:

SELECT o.id, o.total_cents, o.created_at, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.state = 'paid' ORDER BY o.created_at DESC LIMIT 50;

On a big dataset, this can be slow if MySQL can’t efficiently filter state and sort by created_at without scanning lots of rows.

2) Your first tool: EXPLAIN (read it like a checklist)

Run EXPLAIN to see how MySQL plans to execute your query:

EXPLAIN SELECT o.id, o.total_cents, o.created_at, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.state = 'paid' ORDER BY o.created_at DESC LIMIT 50;

What you’re looking for:

type: better is usually const, ref, range. Worse is ALL (full scan).
key: which index it uses (if any).
rows: estimated rows examined (lower is better).
Extra: red flags like Using filesort or Using temporary on big tables.

If you see MySQL scanning a huge portion of orders and doing a filesort, your fix is usually an index that matches both filtering and ordering.

3) The most useful indexing rule: match WHERE + ORDER BY

Our query filters by o.state and orders by o.created_at. A composite index in that order often helps:

CREATE INDEX idx_orders_state_created ON orders (state, created_at DESC);

Now MySQL can:

Jump directly to rows where state='paid'
Read them already sorted by created_at (no expensive filesort)
Stop after LIMIT 50 without reading millions of rows

Tip: In many cases, DESC in an index is optional because MySQL can traverse B-trees in reverse. But being explicit in 8.x is fine and can improve clarity.

4) Covering indexes: avoid extra table lookups (big win)

Even with a good index, MySQL may still “go back” to the table to fetch columns not in the index. That’s extra random I/O on large tables.

For our query, we read id, total_cents, created_at, and user_id (to join). We can create a covering index that contains these columns:

CREATE INDEX idx_orders_paid_feed ON orders (state, created_at DESC, user_id, total_cents, id);

MySQL can satisfy the query directly from the index for the orders part (often shown as “Using index” in Extra), then join to users by primary key. Covering indexes use more disk space, so reserve them for hot endpoints.

5) Fix “count + filters” endpoints with the right index

A common slow endpoint:

SELECT COUNT(*) FROM orders WHERE user_id = 123 AND state = 'paid' AND created_at > '2026-01-01';

Index the most selective equality filters first (=), then ranges (>, BETWEEN):

CREATE INDEX idx_orders_user_state_created ON orders (user_id, state, created_at);

That lets MySQL locate the user’s paid orders quickly, then apply the date range efficiently.

6) Stop using OFFSET pagination on big tables

This looks harmless:

SELECT id, user_id, total_cents, created_at FROM orders WHERE state = 'paid' ORDER BY created_at DESC LIMIT 50 OFFSET 50000;

But MySQL still has to walk past 50,000 rows (or more), doing work you throw away. The deeper the page, the worse it gets.

Use keyset pagination (“seek method”) instead. You keep the last seen sort keys and continue from there:

-- First page SELECT id, user_id, total_cents, created_at FROM orders WHERE state = 'paid' ORDER BY created_at DESC, id DESC LIMIT 50; -- Next page: pass last_created_at and last_id from the previous page SELECT id, user_id, total_cents, created_at FROM orders WHERE state = 'paid' AND (created_at, id) < ('2026-03-20 10:15:00', 987654321) ORDER BY created_at DESC, id DESC LIMIT 50;

Why include id in ordering? Because many rows can share the same created_at. Adding id makes ordering stable and pagination correct.

Support it with an index:

CREATE INDEX idx_orders_paid_seek ON orders (state, created_at DESC, id DESC);

7) “It’s still slow”: rewrite the query to reduce work

Sometimes the join makes MySQL do more than needed. If your feed only needs 50 rows, fetch order IDs first (fast using index), then join:

-- Step 1: get 50 order ids quickly using the index SELECT id FROM orders WHERE state = 'paid' ORDER BY created_at DESC, id DESC LIMIT 50;

-- Step 2: fetch the details + join users (small IN list) SELECT o.id, o.total_cents, o.created_at, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.id IN ( ...50 ids... ) ORDER BY o.created_at DESC, o.id DESC;

In apps, you’d do this as two queries. It can outperform a single complex query because you force the “limit early” behavior.

8) MySQL config you can safely use in dev/staging

Two features help you find issues without guessing:

Slow query log: log queries above a threshold so you can optimize what actually hurts.
Performance Schema: inspect top queries by time, rows examined, etc.

Example: enable slow query logging (on a dev/staging server):

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.2; -- seconds SET GLOBAL log_queries_not_using_indexes = 'ON';

Then review the log and optimize the top offenders first.

9) A tiny Python helper to profile queries (optional but useful)

If you want a quick “measure before/after” script, here’s one using mysql-connector-python:

pip install mysql-connector-python

import time import mysql.connector QUERY = """ SELECT o.id, o.total_cents, o.created_at, u.email FROM orders o JOIN users u ON u.id = o.user_id WHERE o.state = 'paid' ORDER BY o.created_at DESC, o.id DESC LIMIT 50; """ def run(): conn = mysql.connector.connect( host="127.0.0.1", user="app", password="app_pw", database="shop", autocommit=True, ) cur = conn.cursor() # Warm-up cur.execute(QUERY) cur.fetchall() times = [] for _ in range(20): t0 = time.perf_counter() cur.execute(QUERY) cur.fetchall() times.append(time.perf_counter() - t0) cur.close() conn.close() times.sort() p50 = times[len(times)//2] p95 = times[int(len(times)*0.95)-1] print(f"p50={p50*1000:.2f}ms p95={p95*1000:.2f}ms best={times[0]*1000:.2f}ms") if __name__ == "__main__": run()

Run this before and after adding indexes. It’s not a replacement for real APM, but it’s a clean sanity check.

10) Quick checklist for junior/mid devs

Start with EXPLAIN: Is it scanning lots of rows? Is it sorting with filesort?
Add composite indexes that match WHERE then ORDER BY.
Prefer keyset pagination over LIMIT/OFFSET for large datasets.
Use covering indexes only for hot paths (space vs speed tradeoff).
Make ordering stable (e.g., ORDER BY created_at, id).
Optimize what’s real: use slow query logs / metrics, not guesses.

Wrap-up

MySQL optimization is mostly about reducing unnecessary work: fewer rows scanned, fewer sorts, fewer random reads. With a tight loop—EXPLAIN → index/rewrite → measure—you can speed up most slow endpoints without changing your app architecture. If you want a next step, pick your slowest endpoint, paste its query into EXPLAIN, and design an index that matches the filter + sort pattern. That’s where the biggest wins usually live.

MySQL Optimization for Web Apps: Indexing, Query Tuning, and Safer Pagination (Hands-On)