MySQL Optimization for Web Apps: Fix Slow Queries with Indexes, EXPLAIN, and Smart Pagination

Pictalingo

·

MySQL Optimization for Web Apps: Fix Slow Queries with Indexes, EXPLAIN, and Smart Pagination

When a web app feels “slow,” the database is often the real bottleneck. The good news: most MySQL performance wins come from a repeatable workflow—measure, inspect the query plan, add the right index, and re-check. This guide is hands-on and aimed at junior/mid developers who want practical tactics that work in production.

We’ll optimize a common scenario: listing “orders” with filters and sorting—something nearly every web app has.

1) Start with the problem: a query that gets slower over time

Imagine you have these tables:

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

Your API powers a dashboard page like:

  • Show orders for a customer
  • Filter by status
  • Sort by newest
  • Paginate

The naive query might look like this:

SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 123 AND status IN ('paid', 'shipped') ORDER BY created_at DESC LIMIT 50 OFFSET 0;

This can be fast at 10k rows and painfully slow at 10M rows—especially when filtering + sorting forces MySQL to scan lots of rows.

2) Use EXPLAIN to see what MySQL is really doing

Run:

EXPLAIN SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 123 AND status IN ('paid', 'shipped') ORDER BY created_at DESC LIMIT 50 OFFSET 0;

Look for these red flags:

  • type: ALL (full table scan)
  • rows is huge (MySQL expects to read a lot)
  • Extra: Using filesort (sorting lots of rows outside an index)
  • possible_keys empty (you have no useful indexes)

If you see ALL + large rows, you’re paying for scanning. If you see Using filesort, you’re paying for sorting work. Both are common and fixable.

3) Add the right composite index (and why column order matters)

To speed this query, you want MySQL to:

  • Quickly find rows for customer_id
  • Apply status filter efficiently
  • Return results already sorted by created_at DESC

A composite index matching the WHERE and ORDER BY is usually the win:

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

Why this order?

  • customer_id is highly selective for “orders of one customer”
  • status further narrows
  • created_at aligns with sorting so MySQL can walk the index in order

Re-run EXPLAIN. Ideally you now see:

  • type: ref or range
  • key: idx_orders_customer_status_created
  • much smaller rows
  • no (or reduced) Using filesort

Tip: Indexes are not free. Each additional index costs memory/disk and slows writes. Create them for actual query patterns you use often.

4) Make pagination faster with “seek” (keyset) pagination

LIMIT 50 OFFSET 50000 gets slower the deeper you page because MySQL still needs to walk/skips rows to reach that offset.

Instead, use keyset pagination (also called “seek pagination”): use the last seen sort key to fetch the next page.

First page (no cursor):

SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 123 AND status IN ('paid', 'shipped') ORDER BY created_at DESC, id DESC LIMIT 50;

Next page: pass the last row’s created_at and id as a cursor:

-- Suppose last row on previous page had: -- created_at = '2026-04-01 10:20:30' -- id = 88421 SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = 123 AND status IN ('paid', 'shipped') AND ( created_at < '2026-04-01 10:20:30' OR (created_at = '2026-04-01 10:20:30' AND id < 88421) ) ORDER BY created_at DESC, id DESC LIMIT 50;

Why add id? Because created_at can collide. Adding a unique tiebreaker makes pagination stable.

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

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

This approach keeps pagination fast even on huge tables.

5) Avoid “SELECT *” and consider a covering index

If your query returns only id, status, total_cents, created_at, MySQL may still need to fetch full rows from the primary key after scanning the secondary index (a “bookmark lookup”).

Sometimes you can make it faster by turning the index into a covering index—an index that contains all columns needed for the query so MySQL can return results directly from the index.

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

Now, the engine can often satisfy:

SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = ? AND status IN (?, ?) ORDER BY created_at DESC, id DESC LIMIT 50;

Trade-off: Covering indexes can get large. Use them for hot endpoints where latency matters.

6) Find the worst offenders with slow query logging

If you’re optimizing “blind,” enable the slow query log in a staging or controlled production environment. In MySQL, you can set:

-- Enable slow query logging (session examples; production often uses my.cnf) SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.5; -- seconds SET GLOBAL log_queries_not_using_indexes = 'ON';

Then review the log for:

  • Queries that run often (high total time)
  • Queries that scan many rows
  • Patterns that need composite indexes

Don’t optimize rare queries first—optimize the ones that consume the most total DB time.

7) Practical example: optimizing a JOIN + filter

Now a more realistic dashboard query: show latest orders for customers created recently (or matching email domain), etc.

SELECT o.id, o.status, o.total_cents, o.created_at, c.email FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.email LIKE '%@example.com' AND o.status = 'paid' ORDER BY o.created_at DESC LIMIT 50;

Problem: LIKE '%@example.com' can’t use a normal index well because it starts with a wildcard. That forces scanning many customer rows.

Fix options:

  • Store email domain separately (recommended for large datasets)
  • Use a generated column for domain and index it

Generated column approach:

ALTER TABLE customers ADD COLUMN email_domain VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1)) STORED; CREATE INDEX idx_customers_email_domain ON customers (email_domain);

Now query becomes index-friendly:

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

And you’ll likely want an index on orders for this pattern too:

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

Notice how the “best” index depends on the query shape. You don’t index tables—you index query patterns.

8) A tiny Python script to benchmark before/after

You should measure improvements with realistic data. Here’s a minimal script using mysql-connector-python to time a query.

import time import mysql.connector QUERY = """ SELECT id, status, total_cents, created_at FROM orders WHERE customer_id = %s AND status IN ('paid','shipped') ORDER BY created_at DESC, id DESC LIMIT 50 """ def main(): conn = mysql.connector.connect( host="127.0.0.1", user="app_user", password="app_pass", database="app_db", ) cur = conn.cursor() # Warm up cur.execute(QUERY, (123,)) cur.fetchall() runs = 20 start = time.perf_counter() for _ in range(runs): cur.execute(QUERY, (123,)) cur.fetchall() end = time.perf_counter() avg_ms = (end - start) * 1000 / runs print(f"Average: {avg_ms:.2f} ms over {runs} runs") cur.close() conn.close() if __name__ == "__main__": main()

Run it before and after adding indexes. Combine it with EXPLAIN so you know why it improved.

Checklist: the optimization workflow you can reuse

  • EXPLAIN the slow query and find scans/filesorts
  • Add a composite index matching WHERE + ORDER BY (mind column order)
  • Replace deep OFFSET pagination with keyset pagination
  • Avoid SELECT *; consider a covering index for hot paths
  • Use slow query logs to prioritize what matters
  • Re-measure after each change—don’t assume

If you adopt just two habits—running EXPLAIN routinely and using keyset pagination for big lists—you’ll prevent a lot of “it was fast last month” incidents and keep your app responsive as data grows.

Leave a Reply

Your email address will not be published. Required fields are marked *