MySQL Query Optimization for Web Apps: From “It’s Slow” to Measurably Faster (with EXPLAIN + Index Fixes)

If your web app “feels slow,” there’s a good chance it’s waiting on the database. The good news: a lot of MySQL performance work is practical and repeatable. In this hands-on guide, you’ll optimize a common endpoint (a paginated, filtered list) using EXPLAIN, better indexes, and a couple of query rewrites that junior/mid developers can apply today.

We’ll use a realistic example: an orders table powering an admin page that filters by customer, status, and date range.

1) The Baseline Schema (and a “Slow” Query)

Here’s a simplified schema. (If you already have tables, skim this and focus on the query patterns.)

CREATE TABLE customers ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY uniq_customers_email (email) ); CREATE TABLE orders ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, customer_id BIGINT UNSIGNED NOT NULL, status ENUM('pending','paid','shipped','canceled') NOT NULL, total_cents INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(id) );

A typical endpoint query:

SELECT o.id, o.customer_id, o.status, o.total_cents, o.created_at FROM orders o WHERE o.customer_id = 123 AND o.status IN ('paid','shipped') AND o.created_at >= '2026-01-01' ORDER BY o.created_at DESC LIMIT 25 OFFSET 500;

This can degrade badly as OFFSET grows and if indexes don’t match the filter + sort. Let’s measure it properly.

2) Use EXPLAIN (and Know What You’re Looking For)

Run EXPLAIN on the query:

EXPLAIN SELECT o.id, o.customer_id, o.status, o.total_cents, o.created_at FROM orders o WHERE o.customer_id = 123 AND o.status IN ('paid','shipped') AND o.created_at >= '2026-01-01' ORDER BY o.created_at DESC LIMIT 25 OFFSET 500;

In the output, focus on:

type: worst-to-best is roughly ALL (full scan), index, range, ref, eq_ref, const.
rows: how many rows MySQL estimates it will examine.
key: which index it chose (or NULL).
Extra: red flags include Using filesort (sorting after fetching) and Using temporary.

If you see type=ALL or a huge rows estimate, you’re likely missing an index that matches your WHERE + ORDER BY.

3) Add an Index That Matches the Query (Composite Indexes Matter)

A common mistake is adding separate indexes like INDEX(customer_id) and INDEX(created_at) and expecting MySQL to “combine” them optimally. Sometimes it can, but often the best fix is a composite index that matches the query pattern.

For our query, we filter by customer_id, status, and created_at, and we sort by created_at DESC. A good starting composite index is:

ALTER TABLE orders ADD INDEX idx_orders_customer_status_created (customer_id, status, created_at);

Why this order?

Put equality filters first (customer_id), then the next filter (status), then the range/sort column (created_at).
This often lets MySQL seek to the relevant subset and read rows in the right order.

Run EXPLAIN again. You’re hoping to see:

key=idx_orders_customer_status_created
Lower rows
Ideally no Using filesort (not always guaranteed, but often improved)

4) Kill Deep OFFSET Pagination with Keyset Pagination

Even with a good index, OFFSET 500 means MySQL still has to walk past the first 500 matching rows. For large pages, switch to “keyset” pagination (a.k.a. seek pagination). It’s faster and more consistent.

Instead of OFFSET, pass the last seen created_at (and a tie-breaker like id) from the previous page.

First page:

SELECT o.id, o.customer_id, o.status, o.total_cents, o.created_at FROM orders o WHERE o.customer_id = 123 AND o.status IN ('paid','shipped') AND o.created_at >= '2026-01-01' ORDER BY o.created_at DESC, o.id DESC LIMIT 25;

Next page (client sends lastCreatedAt and lastId from the last row):

SELECT o.id, o.customer_id, o.status, o.total_cents, o.created_at FROM orders o WHERE o.customer_id = 123 AND o.status IN ('paid','shipped') AND o.created_at >= '2026-01-01' AND ( o.created_at < '2026-02-10 12:34:56' OR (o.created_at = '2026-02-10 12:34:56' AND o.id < 987654) ) ORDER BY o.created_at DESC, o.id DESC LIMIT 25;

Update the index to support the tie-breaker ordering:

ALTER TABLE orders DROP INDEX idx_orders_customer_status_created, ADD INDEX idx_orders_customer_status_created_id (customer_id, status, created_at, id);

This is one of the biggest “web-app-feel” wins you can make for list endpoints.

5) Avoid “SELECT *” and Use Covering Indexes When It Helps

If your query only needs a few columns, fetch only those. In some cases, MySQL can satisfy the query using only the index (a “covering index”), which avoids extra table lookups.

Example: if the list page only shows id, status, total_cents, and created_at, you could add them to the index for heavy read endpoints (trade-off: larger index, slower writes).

ALTER TABLE orders ADD INDEX idx_orders_list_cover ( customer_id, status, created_at, id, total_cents );

Then run EXPLAIN and check Extra. If you see Using index, it may be using a covering index.

Tip: don’t blindly “cover everything.” Covering indexes are great for hot endpoints, not for every query.

6) Watch for Functions on Indexed Columns (They Break Index Usage)

This is a classic performance trap:

-- Bad: wraps created_at in a function, often prevents range index usage WHERE DATE(created_at) = '2026-03-09'

Rewrite it to a range:

-- Good: index-friendly range WHERE created_at >= '2026-03-09 00:00:00' AND created_at < '2026-03-10 00:00:00'

Similarly, avoid LOWER(email) in the WHERE clause. Prefer case-insensitive collations or store normalized values.

7) Make Slow Queries Visible (Slow Query Log + Quick Triage)

Optimization is easiest when you can see the worst offenders. Enable and tune the slow query log in a non-dev environment (staging, or carefully in prod):

-- Check current settings SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; -- Common adjustments (set in config; shown here as runtime examples) SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.2; -- 200ms threshold (adjust to your app) SET GLOBAL log_queries_not_using_indexes = 'ON';

Then you can identify hot queries and apply the same process: EXPLAIN → index design → query rewrite → verify improvements.

8) A Practical Example in Application Code (Parameterization + Keyset)

Here’s a minimal Node.js example using mysql2 with keyset pagination. The main idea: never concatenate user input into SQL; always pass parameters.

// npm i mysql2 import mysql from "mysql2/promise"; const pool = mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER, database: process.env.DB_NAME, password: process.env.DB_PASS, connectionLimit: 10, }); export async function listOrders({ customerId, statuses, fromDate, pageSize = 25, cursor, // { lastCreatedAt: string, lastId: number } or null }) { const baseWhere = ` o.customer_id = ? AND o.status IN (${statuses.map(() => "?").join(",")}) AND o.created_at >= ? `; const params = [customerId, ...statuses, fromDate]; let cursorWhere = ""; if (cursor) { cursorWhere = ` AND ( o.created_at < ? OR (o.created_at = ? AND o.id < ?) ) `; params.push(cursor.lastCreatedAt, cursor.lastCreatedAt, cursor.lastId); } params.push(pageSize); const sql = ` SELECT o.id, o.status, o.total_cents, o.created_at FROM orders o WHERE ${baseWhere} ${cursorWhere} ORDER BY o.created_at DESC, o.id DESC LIMIT ? `; const [rows] = await pool.execute(sql, params); return rows; }

When the UI requests the next page, send the last row’s created_at and id as the cursor.

9) Quick Checklist You Can Apply Today

Run EXPLAIN for slow endpoints and check type, rows, key, and Extra.
Create composite indexes that match WHERE + ORDER BY (equality columns first, then range/sort).
Replace deep OFFSET pagination with keyset pagination (created_at + id cursor).
Avoid functions on indexed columns in WHERE; rewrite as ranges.
Select only needed columns; consider covering indexes for your hottest read paths.
Enable slow query logging and optimize based on real traffic, not guesses.

If you do just two things—add the right composite index and switch deep pagination to keyset—you’ll usually see immediate, measurable improvements on list-heavy admin pages and dashboards.

MySQL Query Optimization for Web Apps: From “It’s Slow” to Measurably Faster (with EXPLAIN + Index Fixes)