MySQL Optimization in Practice: Fix Slow Queries with Indexes, EXPLAIN, and Pagination

MySQL Optimization in Practice: Fix Slow Queries with Indexes, EXPLAIN, and Pagination

Slow MySQL queries usually do not start as dramatic production incidents. They begin as a page that takes 300 ms longer than expected, then a dashboard that times out after the data grows, then an API endpoint that blocks other requests. The good news: many performance issues can be fixed with a small set of practical habits.

This article walks through a realistic MySQL optimization workflow for junior and mid-level developers. You will learn how to inspect a slow query, add the right index, avoid common pagination traps, and keep performance checks repeatable.

Example Scenario: A Slow Orders API

Imagine a web app with an orders table. The product team wants an endpoint that lists recent paid orders for a customer.

CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL );

The application query looks simple:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

On a table with a few thousand rows, this may feel instant. On a table with millions of orders, it can become expensive if MySQL has to scan many rows, filter them, sort them, and only then return 20 records.

Step 1: Use EXPLAIN Before Guessing

Do not start by adding random indexes. First, ask MySQL how it plans to execute the query with EXPLAIN.

EXPLAIN SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

You may see output where type is ALL, which means a full table scan. You may also see Using filesort in the Extra column, which means MySQL needs an extra sorting step.

Important columns to check:

• type: ALL is usually bad for large tables; ref, range, or const are often better.
• possible_keys: indexes MySQL could use.
• key: the index MySQL actually chose.
• rows: estimated number of rows MySQL expects to inspect.
• Extra: useful warnings such as Using filesort or Using temporary.

The goal is not to memorize every possible EXPLAIN result. The goal is to spot obvious waste: too many scanned rows, missing indexes, unnecessary sorting, and temporary tables.

Step 2: Add a Composite Index That Matches the Query

For this query, MySQL filters by customer_id, filters by status, and sorts by created_at DESC. A useful index should follow that access pattern.

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

Now run EXPLAIN again:

EXPLAIN SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

After the index is added, MySQL can jump directly to rows for one customer and one status, already ordered by created_at. That is much cheaper than scanning the whole table and sorting the result.

A common beginner mistake is creating separate indexes like this:

CREATE INDEX idx_orders_customer_id ON orders (customer_id); CREATE INDEX idx_orders_status ON orders (status); CREATE INDEX idx_orders_created_at ON orders (created_at);

Separate indexes can help some queries, but they are not the same as a well-designed composite index. If a query commonly uses multiple columns together, design the index around the complete query shape.

Step 3: Avoid SELECT *

Another easy win is to select only the columns you need.

Less ideal:

SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

Better:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

This matters because wide rows cost more to read, transfer, and hydrate into objects in your application. It also gives you the option of using a covering index for some queries.

For example, this index contains every column needed by the query:

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

This can let MySQL answer the query from the index without reading the full table row. However, covering indexes are not free. They take disk space and slow down inserts and updates. Use them for high-traffic queries where measurement shows a real benefit.

Step 4: Replace Deep OFFSET Pagination

Offset pagination is common in APIs:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

The problem is that MySQL still has to walk through many rows before returning the requested page. The deeper the page, the slower the query can become.

For feeds, dashboards, and “load more” interfaces, use keyset pagination instead. The first page:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20;

Suppose the last row from the first page has:

created_at = '2026-05-10 14:30:00' id = 987654

The next page can use those values as a cursor:

SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' AND ( created_at < '2026-05-10 14:30:00' OR (created_at = '2026-05-10 14:30:00' AND id < 987654) ) ORDER BY created_at DESC, id DESC LIMIT 20;

For this query, update the index so it supports the full sort order:

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

Keyset pagination is not ideal when users need to jump directly to page 500. But for infinite scrolling, event logs, order history, and admin lists, it is usually more stable than deep offsets.

Step 5: Keep Indexes Useful, Not Excessive

Indexes improve reads, but they add overhead to writes. Every time you insert, update, or delete rows, MySQL must maintain affected indexes.

Before adding a new index, ask:

• Which exact query will this index improve?
• Is this query frequent enough to justify the index?
• Does an existing composite index already cover the same leftmost columns?
• Will this index slow down a write-heavy table?

For example, this index:

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

can also help queries that filter by customer_id and status. You may not need a separate index on only (customer_id, status). This is called the leftmost prefix principle: MySQL can use the leading columns of a composite index, but not arbitrary columns from the middle of it.

Step 6: Test from Your Application Code

Database optimization is not finished when the SQL looks better. You should also test how your application sends and reads the query.

Here is a small Python example using mysql-connector-python that measures query time:

import time import mysql.connector connection = mysql.connector.connect( host="localhost", user="app_user", password="secret", database="shop" ) query = """ SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = %s AND status = %s ORDER BY created_at DESC, id DESC LIMIT 20 """ start = time.perf_counter() with connection.cursor(dictionary=True) as cursor: cursor.execute(query, (42, "paid")) rows = cursor.fetchall() duration_ms = (time.perf_counter() - start) * 1000 print(f"Fetched {len(rows)} rows in {duration_ms:.2f} ms") connection.close()

Notice two important details. First, the query uses parameters instead of string interpolation. This protects against SQL injection and helps keep code clean. Second, the measurement happens around the actual database call, not around unrelated application logic.

Step 7: Add a Slow Query Log in Development

For local or staging environments, a slow query log helps you catch problems before they reach production. You can enable it temporarily:

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 0.2;

This logs queries slower than 200 ms. In production, be careful with aggressive logging because it can create large log files. A safer approach is to enable it during investigation windows, ship logs to your observability stack, and review the most frequent slow queries.

You can also inspect query behavior with:

EXPLAIN ANALYZE SELECT id, customer_id, status, total_cents, created_at FROM orders WHERE customer_id = 42 AND status = 'paid' ORDER BY created_at DESC, id DESC LIMIT 20;

EXPLAIN ANALYZE executes the query and provides runtime details. Use it carefully on heavy queries because it actually runs them.

A Practical MySQL Optimization Checklist

• Start with the slowest real query, not a theoretical one.
• Run EXPLAIN before changing indexes.
• Create composite indexes that match WHERE, ORDER BY, and LIMIT.
• Avoid SELECT * in API and dashboard queries.
• Replace deep OFFSET pagination with keyset pagination where possible.
• Remove duplicate or unused indexes after verifying they are unnecessary.
• Measure from the application, not only from a database console.

Conclusion

MySQL optimization is less about magic settings and more about repeatable investigation. Find a slow query, inspect it with EXPLAIN, design an index around the actual filter and sort pattern, then measure again. For many web applications, these basics fix the most painful database bottlenecks: full table scans, expensive sorts, oversized result sets, and slow pagination.

As your application grows, treat query performance as part of normal development. A query that is harmless with 10,000 rows may become a production issue with 10 million. The earlier your team learns to read execution plans and design practical indexes, the fewer database emergencies you will have later.