MySQL Optimization for Web Apps: Make Queries Fast Without Guesswork

If your web app feels “fine” in dev but slows down in production, MySQL is often the bottleneck. The good news: most performance wins come from a small set of practical habits—measuring with EXPLAIN, indexing the right columns (in the right order), and avoiding common query anti-patterns.

This hands-on guide walks through a realistic example: a typical “orders” screen with filtering, pagination, and joins. You’ll learn how to find slow queries, interpret MySQL’s plan, and apply fixes that actually move the needle.

Step 1: Start With a Realistic Schema (and a Slow Query)

Let’s model a simplified e-commerce setup: users place orders, orders have a status and timestamps, and a page lists recent orders for a specific user with an optional status filter.

CREATE TABLE users ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL UNIQUE, created_at DATETIME NOT NULL ) ENGINE=InnoDB; CREATE TABLE orders ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, status ENUM('pending','paid','shipped','cancelled') NOT NULL, total_cents INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB; -- Intentionally minimal indexes at first: CREATE INDEX idx_orders_user_id ON orders(user_id);

A common query for an orders page might look like this:

SELECT id, status, total_cents, created_at FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 0;

This query can still be slow even though we indexed user_id, because MySQL might have to fetch many rows for that user, filter by status, then sort by created_at.

Step 2: Use EXPLAIN to See What MySQL Is Doing

Run EXPLAIN (or EXPLAIN ANALYZE in MySQL 8+) to inspect the plan.

EXPLAIN SELECT id, status, total_cents, created_at FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Key things to look at in the output:

type: “good” usually means ref or range; “bad” often means ALL (full scan).
key: which index (if any) is used.
rows: estimate of rows scanned (lower is better).
Extra: watch for Using filesort (sorting) and Using temporary (often expensive).

If you see Using filesort, it usually means MySQL can’t produce rows in the requested order using an index.

Step 3: Add a Composite Index That Matches Your Query

For this query, we filter by user_id and status, then order by created_at. A composite index can support both filtering and ordering.

CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

Now rerun EXPLAIN. Ideally:

MySQL uses idx_orders_user_status_created
rows scanned drops dramatically
Extra no longer shows Using filesort

Index order matters. Put the most selective equality filters first (often user_id), then other equality filters (status), then the column used for sorting (created_at).

Step 4: Make Pagination Faster With Keyset (Cursor) Pagination

LIMIT ... OFFSET ... gets slower as the page number grows because MySQL still has to scan (and discard) the earlier rows. For “infinite scroll” or deep pagination, switch to keyset pagination.

Instead of “page 10”, you request “next 20 items after this last seen timestamp/id”.

-- First page: SELECT id, status, total_cents, created_at FROM orders WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20; -- Next page (cursor): -- Suppose the last row from the previous page had created_at='2026-01-10 11:00:00' and id=9001 SELECT id, status, total_cents, created_at FROM orders WHERE user_id = 42 AND status = 'paid' AND (created_at < '2026-01-10 11:00:00' OR (created_at = '2026-01-10 11:00:00' AND id < 9001)) ORDER BY created_at DESC, id DESC LIMIT 20;

To support this well, extend the index to include id at the end:

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

This approach stays fast no matter how far the user scrolls.

Step 5: Avoid “SELECT *” and Aim for Covering Indexes

Fetching fewer columns reduces I/O and can allow a covering index, meaning MySQL can answer the query using only the index without reading table rows.

If your list page only needs id, status, total_cents, created_at, don’t select extra columns like shipping address blobs or JSON metadata.

You can even include selected columns in an index (carefully!) to help covering:

-- Use with caution: wider indexes cost more writes and memory CREATE INDEX idx_orders_list_cover ON orders (user_id, status, created_at DESC, id DESC, total_cents);

Rule of thumb: only do this for very hot endpoints, and only include small, frequently used columns.

Step 6: Optimize Joins by Indexing Foreign Keys (Both Sides)

A classic slow query: “show orders with user email”.

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.status = 'paid' ORDER BY o.created_at DESC LIMIT 50;

Make sure:

users.id is the primary key (it is).
orders.user_id is indexed (it is).
The filter/sort columns in orders are indexed if the query starts there (status, created_at).

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

With this index, MySQL can quickly find “paid” orders in the right order, then join to users by primary key.

Step 7: Find Slow Queries in Production (Without Panicking)

Indexes and query rewrites help most, but you still need visibility. Two practical tools:

Slow query log: logs queries that exceed a threshold.
Performance Schema: built-in instrumentation for query stats.

Enable the slow query log and set a sensible threshold (e.g., 200–500ms). Exact configuration depends on your environment, but the goal is simple: capture slow queries, then run them through EXPLAIN and fix them one by one.

While testing locally, you can also simulate “real” performance by seeding a large dataset and measuring query time repeatedly, not once.

Step 8: Practical “Do / Don’t” Checklist

Do run EXPLAIN for any query that hits big tables.
Do build composite indexes that match your WHERE + ORDER BY.
Do switch deep pagination to keyset pagination.
Do select only needed columns.
Don’t add indexes blindly—each index adds write cost and storage.
Don’t rely on OFFSET for large page numbers.
Don’t ignore “Using filesort” and “Using temporary” in EXPLAIN.

A Small, Working Example in Code (Cursor Pagination)

Here’s a minimal Node.js example (using mysql2) that implements cursor pagination safely with prepared statements:

import mysql from "mysql2/promise"; const pool = mysql.createPool({ host: "localhost", user: "app", password: "secret", database: "shop", connectionLimit: 10, }); export async function listPaidOrders({ userId, cursorCreatedAt, cursorId, limit = 20 }) { const params = [userId, "paid"]; let cursorClause = ""; if (cursorCreatedAt && cursorId) { cursorClause = ` AND (created_at < ? OR (created_at = ? AND id < ?)) `; params.push(cursorCreatedAt, cursorCreatedAt, cursorId); } params.push(Number(limit)); const [rows] = await pool.execute( ` SELECT id, status, total_cents, created_at FROM orders WHERE user_id = ? AND status = ? ${cursorClause} ORDER BY created_at DESC, id DESC LIMIT ? `, params ); return rows; }

This pairs directly with the index:

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

Wrap-Up: Optimize the Query You Actually Have

MySQL optimization isn’t magic—it’s alignment. Align your query with an index MySQL can use, and you’ll usually see the biggest improvements immediately. Start with the slowest endpoint, capture the exact SQL, run EXPLAIN, and apply targeted changes: composite indexes, keyset pagination, and lean selects.

If you want a next step, pick one hot query in your app and try this workflow today: measure → EXPLAIN → index/rewriting → measure again. That loop is where real performance wins come from.

MySQL Optimization for Web Apps: Make Queries Fast Without Guesswork