MySQL Query Optimization for Web Apps: Indexes, EXPLAIN, and Safe Pagination (Hands-On)

MySQL Query Optimization for Web Apps: Indexes, EXPLAIN, and Safe Pagination (Hands-On)

When a web app “gets slow,” the database is often the first bottleneck. The good news: you can usually fix the worst MySQL performance issues with a small toolkit—good indexes, reading EXPLAIN, and avoiding patterns that force full table scans. This guide is hands-on and aimed at junior/mid developers who want practical wins fast.

We’ll work through a realistic scenario: a posts table powering a feed/admin list where users filter by status, author, and date, then paginate. We’ll make it faster without rewriting the whole app.

1) A realistic schema (and a slow query)

Here’s a simplified table you might actually have:

CREATE TABLE posts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, author_id BIGINT UNSIGNED NOT NULL, status ENUM('draft','published','archived') NOT NULL, title VARCHAR(255) NOT NULL, body MEDIUMTEXT NOT NULL, published_at DATETIME NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB;

Now a common “admin list” query:

SELECT id, author_id, status, title, published_at FROM posts WHERE status = 'published' AND published_at >= '2026-01-01' ORDER BY published_at DESC LIMIT 20 OFFSET 2000;

This can be painfully slow at scale because:

• Filtering on status + published_at without an index can force a scan.
• ORDER BY published_at DESC without an index means sorting large result sets.
• OFFSET 2000 forces MySQL to walk past 2000 rows before returning 20 (wasted work).

2) First step: measure with EXPLAIN (don’t guess)

Before you add indexes, inspect the plan:

EXPLAIN SELECT id, author_id, status, title, published_at FROM posts WHERE status = 'published' AND published_at >= '2026-01-01' ORDER BY published_at DESC LIMIT 20 OFFSET 2000;

What you’re looking for in EXPLAIN:

• type: best is const or ref, OK is range, bad is ALL (full scan).
• key: which index MySQL chose (empty means no index used).
• rows: how many rows MySQL estimates it must examine.
• Extra: red flags like Using filesort (sorting without index) and Using temporary.

If you see type=ALL and a big rows estimate, indexing is the next move.

3) Create the right composite index (order matters)

For this query, MySQL benefits from an index that supports both filtering and ordering:

CREATE INDEX idx_posts_status_published_at ON posts (status, published_at DESC);

Why this works:

• MySQL can quickly find rows where status='published'.
• Within that subset, it can walk published_at in descending order using the index (often avoiding a sort).

Run EXPLAIN again. You want to see key=idx_posts_status_published_at and ideally no Using filesort.

Rule of thumb: Put the most selective equality filters first (like status), then range/sort columns (like published_at).

4) Covering indexes: speed up “list pages” dramatically

List pages often read only a few columns. If the index includes everything needed, MySQL may avoid reading the table (“covering index”), which can be a big win.

For our select list (id, author_id, status, title, published_at), we can extend the index. In InnoDB, the primary key (id) is implicitly stored with secondary indexes, so you usually don’t need to add it. But you can add the remaining columns:

CREATE INDEX idx_posts_status_published_at_cover ON posts (status, published_at DESC, author_id, title);

Now check EXPLAIN for Extra showing Using index, which often indicates a covering index is being used.

Caution: Covering indexes increase storage and write cost. Use them for hot endpoints (frequent reads) and keep them targeted.

5) Fix the hidden killer: OFFSET pagination

Even with a good index, deep pagination with OFFSET gets slower as the offset grows, because MySQL still has to walk past those rows.

Use keyset pagination (a.k.a. “seek method”) instead. Instead of “page 101,” you ask for “the next 20 rows before this last timestamp/id.”

First page:

SELECT id, author_id, status, title, published_at FROM posts WHERE status = 'published' ORDER BY published_at DESC, id DESC LIMIT 20;

Next page: pass the last row’s published_at and id to fetch the next chunk:

SELECT id, author_id, status, title, published_at FROM posts WHERE status = 'published' AND (published_at < '2026-02-10 12:34:56' OR (published_at = '2026-02-10 12:34:56' AND id < 987654)) ORDER BY published_at DESC, id DESC LIMIT 20;

Why the id tie-breaker? Because many posts can share the same published_at. Ordering by published_at, id gives a stable cursor.

Supporting index:

CREATE INDEX idx_posts_status_pub_id ON posts (status, published_at DESC, id DESC);

This approach is consistently fast, even for “page 5000,” because the database seeks from a known point rather than skipping thousands of rows.

6) Optimize counts without hurting the database

Many pages show “Total results: 1,234,567”. A naive count can be expensive:

SELECT COUNT(*) FROM posts WHERE status='published' AND published_at >= '2026-01-01';

With the right index (status, published_at), this might be acceptable—but for complex filters it can still be heavy.

Practical options:

• Show “20+ results” or “many results” for broad searches; avoid exact totals on every request.
• Cache counts for common filters (e.g., published posts) for a short TTL.
• Use approximate counts for analytics dashboards (exactness often doesn’t matter).

If you need exact counts, ensure your filters match an index prefix so MySQL can count via index ranges.

7) Avoid patterns that disable indexes

These common mistakes force scans or make indexes useless:

• Functions on indexed columns:

  -- Bad: wraps the column WHERE DATE(published_at) = '2026-02-14'

  Prefer a range:

  WHERE published_at >= '2026-02-14 00:00:00' AND published_at < '2026-02-15 00:00:00'

• Leading wildcards in LIKE:

  -- Bad: can't use a normal btree index effectively WHERE title LIKE '%docker%'

  Use full-text search for this use case:

  ALTER TABLE posts ADD FULLTEXT INDEX ft_title_body (title, body);
  SELECT id, title
  FROM posts
  WHERE MATCH(title, body) AGAINST ('+docker +best' IN BOOLEAN MODE)
  LIMIT 20;

• Implicit type conversion:

  -- Bad: author_id is numeric, comparing to string can cause conversion WHERE author_id = '123'

  Make sure parameters are bound with the correct type.

8) Practical workflow: repeatable tuning checklist

When you’re optimizing a slow endpoint, do this every time:

• Capture the exact SQL and real parameters (not a simplified version).
• Run EXPLAIN (and if available, EXPLAIN ANALYZE) to see what MySQL actually does.
• Add or adjust a composite index that matches:
  • WHERE equality columns first
  • then range columns
  • then ORDER BY columns (in the same direction where possible)
• Re-check rows estimate, type, and Extra for filesort/temporary.
• Fix pagination if you’re using deep OFFSET.
• Verify with realistic data volume (local dev with 1k rows lies).

9) Working example: keyset pagination in Python (mysql-connector)

This snippet shows safe, parameterized keyset pagination. It returns a “next cursor” you can send back to the client.

from typing import Optional, Dict, Any, Tuple, List import mysql.connector def fetch_posts_page( conn, page_size: int = 20, cursor: Optional[Dict[str, Any]] = None, ) -> Tuple[List[Dict[str, Any]], Optional[Dict[str, Any]]]: """ cursor format: {"published_at": "YYYY-MM-DD HH:MM:SS", "id": 123} """ base_sql = """ SELECT id, author_id, status, title, published_at FROM posts WHERE status = %s """ params = ["published"] if cursor: base_sql += """ AND (published_at < %s OR (published_at = %s AND id < %s)) """ params.extend([cursor["published_at"], cursor["published_at"], cursor["id"]]) base_sql += """ ORDER BY published_at DESC, id DESC LIMIT %s """ params.append(page_size) cur = conn.cursor(dictionary=True) cur.execute(base_sql, params) rows = cur.fetchall() next_cursor = None if rows: last = rows[-1] # Normalize to string for JSON transport published_at = last["published_at"].strftime("%Y-%m-%d %H:%M:%S") if last["published_at"] else None next_cursor = {"published_at": published_at, "id": last["id"]} if published_at else None return rows, next_cursor # Usage: # conn = mysql.connector.connect(host="localhost", user="app", password="secret", database="mydb") # page1, c1 = fetch_posts_page(conn) # page2, c2 = fetch_posts_page(conn, cursor=c1)

Note the important bits:

• Stable ordering with ORDER BY published_at DESC, id DESC
• Cursor filter that matches the ordering
• Parameterized query (no string concatenation)

Wrap-up: the fastest wins you can ship this week

• Add the index your query actually needs: typically a composite index aligned with WHERE + ORDER BY.
• Use keyset pagination for large datasets; avoid deep OFFSET.
• Read EXPLAIN and look for scans, filesorts, and huge row estimates.
• Avoid “index killers” like DATE(column) in filters and leading wildcards in LIKE.

If you apply just those four, you’ll eliminate a big chunk of “mysteriously slow” MySQL endpoints—without heroic refactors.