When MySQL Queries Crawl: A Pragmatic Optimization & Indexing Checklist
Dev

When MySQL Queries Crawl: A Pragmatic Optimization & Indexing Checklist

A query that runs in 15 milliseconds during local development with a thousand seed rows can suddenly grind to a 4-second crawl in production once your table grows to a few million records.

Over 80% of backend latency spikes trace back to the database layer. Before reaching for costly hardware scale-ups or distributed sharding, optimizing your indexes and query structures usually yields 10x to 100x performance gains with minimal effort.

Here is a practical checklist of what to inspect first when MySQL performance degrades under load.

1. What to Look for in EXPLAIN ANALYZE

Query tuning starts by understanding how the MySQL optimizer plans to execute your query:

EXPLAIN ANALYZE 
SELECT id, user_id, amount, created_at 
FROM transactions 
WHERE user_id = 1042 AND created_at >= '2026-01-01' 
ORDER BY created_at DESC;

Pay special attention to these three fields:

  1. type (Access Method):
    • 🚨 ALL (Full Table Scan): The storage engine reads every single page in the table. An immediate red flag.
    • ⚠️ index (Full Index Scan): Reads the entire index tree. Better than ALL, but still wasteful.
    • ✅ range, ref, eq_ref, const: The engine selectively reads only the matching B-Tree branch.
  2. rows vs Actual Output:
    • If rows estimated is 50,000 but the query only returns 20 rows, your index filtering is loose and needs refinement.
  3. Extra Column:
    • Using filesort: The engine couldn’t use an index for sorting and had to sort results in memory or on temporary disk space.
    • Using index: Covering index! The query was satisfied entirely within the index leaf pages without fetching table row blocks.

2. The Golden Rule of Composite Indexes

When queries filter by multiple columns, multi-column composite indexes are required. Column ordering makes or breaks performance:

[Equality (=) Columns] ➔ [Range (<, >, BETWEEN) Columns] ➔ [ORDER BY Sort Columns]

If your query is WHERE status = 'PAID' AND created_at >= '2026-01-01', creating an index on (created_at, status) is inefficient. Put the equality check first: (status, created_at). This narrows down the B-Tree search space immediately before scanning the date range.

3. The Classic ORM Trap: N+1 Queries

Whether you use Prisma, TypeORM, or Hibernate, N+1 query patterns remain the most common performance bug in modern backends.

  • The Problem: Querying 100 users, then looping through each user in application code to fetch their transactions. That results in 1 initial query + 100 subsequent queries = 101 database round trips.
  • The Fix: Eager-load associations in a single query using joins or batch operators (include: { transactions: true } or WHERE user_id IN (...)).

4. Ditch Heavy OFFSET for Keyset (Seek) Pagination

Traditional page-number pagination degrades dramatically as users paginate deeper:

-- To fetch 20 rows starting at row 1,000,000, MySQL reads and discards 1,000,000 rows!
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 1000000;

Use Keyset Pagination (Seek Method) instead. It jumps directly to the target row using the last seen ID:

-- Skips scanning millions of rows and utilizes index seeking directly
SELECT * FROM orders 
WHERE id < 154200 
ORDER BY id DESC 
LIMIT 20;

5. High-Throughput Inserts: Auto-Increment vs UUID v7

When inserting thousands of rows per second, primary key selection directly affects write latency:

  • Auto-Increment (BigInt): Appends sequentially to the end of the B-Tree with zero index page fragmentation. Fast, but exposes row counts and complicates multi-region databases.
  • Random UUID (v4): Inserts into random positions throughout the B-Tree, causing continuous page splits and heavy disk I/O.
  • UUID v7 (Recommended): Combines a Unix millisecond timestamp at the front with random bits at the tail. You get distributed uniqueness without sacrificing sequential B-Tree write throughput.

For bulk imports, always batch inserts into groups of 500–1,000 rows (INSERT INTO ... VALUES (...), (...)) rather than executing separate single-row transactions.

Start Here

Continue with the core guides that pull steady search traffic.

Keep Reading

Related guides are shown to help you explore more.

Related Posts

More in this category