MySQL Query Optimization for Web Apps: From “Slow” to “Snappy” with EXPLAIN, Indexes, and Better Queries

Pictalingo

·

MySQL Query Optimization for Web Apps: From “Slow” to “Snappy” with EXPLAIN, Indexes, and Better Queries

Most “MySQL is slow” problems in web apps aren’t solved by throwing bigger hardware at the database. They’re solved by making MySQL do less work: scanning fewer rows, sorting less data, and returning only what you actually need. In this hands-on guide, you’ll learn a repeatable workflow for tuning queries using EXPLAIN, building the right indexes, and rewriting common slow patterns.

We’ll use a realistic example: a product catalog with search, filtering, sorting, and pagination—exactly the kind of thing that can melt a database when traffic grows.

1) Set up a realistic schema (and a slow query)

Here’s a simplified schema:

-- products table (InnoDB) CREATE TABLE products ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, description TEXT NULL, category_id INT UNSIGNED NOT NULL, price_cents INT UNSIGNED NOT NULL, status ENUM('active','draft','archived') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL ) ENGINE=InnoDB; CREATE TABLE categories ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL ) ENGINE=InnoDB; CREATE TABLE order_items ( id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL ) ENGINE=InnoDB;

A common slow endpoint is “browse products”:

  • Filter: status='active' and maybe category_id
  • Sort: “newest” (created_at DESC)
  • Paginate: page size 20

Many apps start with something like this:

SELECT * FROM products WHERE status = 'active' AND category_id = 42 ORDER BY created_at DESC LIMIT 20 OFFSET 400;

This can get painful as OFFSET grows—MySQL must scan and discard many rows before returning the 20 you want.

2) Use EXPLAIN to see what MySQL is doing

The first step is always: measure and inspect.

EXPLAIN SELECT * FROM products WHERE status = 'active' AND category_id = 42 ORDER BY created_at DESC LIMIT 20 OFFSET 400;

What you’re looking for:

  • type: better is usually const, ref, range; worse is ALL (full table scan).
  • key: which index (if any) is used.
  • rows: estimated rows examined (smaller is better).
  • Extra: scary flags like Using filesort or Using temporary can indicate extra work.

If you see type=ALL and no index chosen, you’ve found your problem: MySQL is scanning the table.

3) Add a composite index that matches your WHERE + ORDER BY

Indexes work best when they match how you filter and sort. For our query, we filter by status and category_id and sort by created_at DESC.

CREATE INDEX idx_products_browse ON products (status, category_id, created_at DESC, id);

Why these columns?

  • status and category_id narrow the search quickly.
  • created_at supports the sort order without a filesort.
  • id makes the index more selective and helps stable ordering when multiple rows share the same created_at.

Now re-run EXPLAIN and confirm key=idx_products_browse and fewer examined rows. If you still see Using filesort, it usually means the index isn’t lining up with the sort, or the query is too broad.

4) Stop using OFFSET for deep pagination: switch to keyset pagination

OFFSET pagination gets slower as you go deeper. A better approach is keyset pagination (a.k.a. “seek method”): use the last seen sort key from the previous page.

First page:

SELECT id, name, price_cents, created_at FROM products WHERE status = 'active' AND category_id = 42 ORDER BY created_at DESC, id DESC LIMIT 20;

Next page: pass the last row’s (created_at, id) as a cursor:

-- Suppose last row on previous page was: -- created_at = '2026-02-10 12:34:56', id = 991234 SELECT id, name, price_cents, created_at FROM products WHERE status = 'active' AND category_id = 42 AND (created_at < '2026-02-10 12:34:56' OR (created_at = '2026-02-10 12:34:56' AND id < 991234)) ORDER BY created_at DESC, id DESC LIMIT 20;

This stays fast because MySQL can jump into the index near the cursor and read the next 20 rows—no scanning and discarding hundreds or thousands.

5) Don’t SELECT *: fetch only what you need

Returning fewer columns reduces I/O and network overhead, and it can allow MySQL to satisfy the query from the index (covering index) in some cases.

Instead of:

SELECT * FROM products WHERE status = 'active' ORDER BY created_at DESC LIMIT 20;

Prefer:

SELECT id, name, price_cents, created_at FROM products WHERE status = 'active' ORDER BY created_at DESC, id DESC LIMIT 20;

Bonus: if your index includes all selected columns, MySQL may avoid extra lookups to the table rows (less disk work).

6) Fix the classic N+1 pattern using a join (or a single aggregation query)

Another common performance killer is N+1 queries: you fetch a list of products, then query each product’s sales count separately.

Bad (N+1):

-- Query 1: fetch products SELECT id, name FROM products WHERE status='active' ORDER BY created_at DESC LIMIT 20; -- Then for each product: SELECT SUM(quantity) AS total_sold FROM order_items WHERE product_id = ?;

Better: do it in one query with an aggregate:

SELECT p.id, p.name, COALESCE(SUM(oi.quantity), 0) AS total_sold FROM products p LEFT JOIN order_items oi ON oi.product_id = p.id WHERE p.status = 'active' GROUP BY p.id, p.name ORDER BY p.id DESC LIMIT 20;

Add an index to make the join fast:

CREATE INDEX idx_order_items_product_id ON order_items (product_id);

If you need “top-selling products”, you might sort by total_sold (computed). That can still be expensive at scale—at that point consider maintaining a counter table or periodic summary.

7) Avoid functions on indexed columns in WHERE clauses

If you wrap an indexed column in a function, MySQL often can’t use the index efficiently.

Bad:

SELECT id FROM products WHERE DATE(created_at) = '2026-02-18';

Better: use a range:

SELECT id FROM products WHERE created_at >= '2026-02-18 00:00:00' AND created_at < '2026-02-19 00:00:00';

This allows MySQL to use an index on created_at (or a composite index that includes it).

8) Use slow query logging (and fix what’s actually slow)

In real systems, you don’t guess—you look at the slow query log. If you manage MySQL, enable it and set a reasonable threshold (e.g., 200–500ms for web workloads). Then you’ll get a list of the worst offenders, with frequency and timing.

  • Optimize the queries users hit most often.
  • Prefer fixing the query and index before touching server knobs.
  • Re-check with EXPLAIN after each change.

9) A practical checklist you can apply today

  • Start with EXPLAIN: confirm index usage and estimated rows examined.
  • Create the right composite index: match WHERE columns first, then ORDER BY.
  • Replace deep OFFSET pagination with keyset pagination (created_at + id cursor).
  • Select fewer columns: avoid SELECT * in hot paths.
  • Kill N+1 queries using joins or batch queries.
  • Don’t apply functions to indexed columns in filters; use ranges instead.
  • Measure after changes: query time and EXPLAIN should improve together.

10) One final “real-world” example: fast search + filter

Suppose you add a simple keyword search on product name:

SELECT id, name, price_cents FROM products WHERE status='active' AND category_id=42 AND name LIKE '%shoe%' ORDER BY created_at DESC LIMIT 20;

LIKE '%shoe%' can’t use a normal B-tree index efficiently because of the leading wildcard. Options:

  • If “starts with” is acceptable, use name LIKE 'shoe%' and index (status, category_id, name).
  • If you need contains-search at scale, use FULLTEXT (with tradeoffs) or an external search engine.

Example using FULLTEXT on name and description:

ALTER TABLE products ADD FULLTEXT INDEX ft_products_text (name, description); SELECT id, name, price_cents FROM products WHERE status='active' AND category_id=42 AND MATCH(name, description) AGAINST ('shoe' IN NATURAL LANGUAGE MODE) ORDER BY created_at DESC LIMIT 20;

This won’t replace a dedicated search engine for complex relevance and filtering, but it can be a solid step up from wildcard LIKE for many web apps.

Wrap-up

MySQL performance tuning is mostly about building a habit: inspect the plan, add the right index, and rewrite the query to avoid unnecessary work. If you apply just two changes from this article—(1) composite indexes aligned with your filters/sorts and (2) keyset pagination—you’ll often see dramatic improvements on real production endpoints.

Leave a Reply

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