MySQL Optimization in Practice: Find and Fix Slow Queries with Indexes

Slow database queries are one of the most common reasons a web application feels sluggish. The good news: many MySQL performance problems are not solved by “bigger servers” first. They are solved by understanding how queries read data, adding the right indexes, and avoiding patterns that force MySQL to scan too many rows.

This guide walks through a practical workflow junior and mid-level developers can use on real projects: inspect a slow query, read the execution plan, add indexes, and rewrite common query patterns. The examples use a simple e-commerce-style database, but the same ideas apply to dashboards, SaaS apps, APIs, and admin panels.

Example Schema

Imagine an application with users, orders, and order items. A common page shows the latest paid orders for one customer.

CREATE TABLE users ( id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL, name VARCHAR(120) NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY users_email_unique (email) ); CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(30) NOT NULL, total_cents INT NOT NULL, created_at DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE order_items ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_id BIGINT NOT NULL, product_name VARCHAR(255) NOT NULL, quantity INT NOT NULL, price_cents INT NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) );

Now consider this query:

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

This looks harmless. But without the right index, MySQL may need to inspect many rows in orders, filter them, sort them, and then return only 10 records.

Step 1: Use EXPLAIN Before Guessing

Optimization starts with measurement. In MySQL, the simplest first tool is EXPLAIN. It shows how MySQL plans to execute a query.

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

Pay attention to these columns:

type: shows how efficiently MySQL accesses rows. Values like ref or range are usually better than ALL.
possible_keys: indexes MySQL could use.
key: the index MySQL actually chose.
rows: estimated number of rows MySQL expects to examine.
Extra: may show warnings like Using filesort or Using temporary.

If type is ALL, MySQL is doing a full table scan. That is not always bad for tiny tables, but it becomes expensive as data grows.

Step 2: Add a Composite Index That Matches the Query

A single-column index on user_id helps, but this query filters by user_id and status, then sorts by created_at. A better index matches that access pattern.

CREATE INDEX orders_user_status_created_idx ON orders (user_id, status, created_at DESC);

Now run EXPLAIN again:

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

The ideal result is that MySQL uses orders_user_status_created_idx, examines fewer rows, and avoids an expensive sort. The order of columns matters. In this case:

user_id comes first because the query filters by a specific user.
status comes second because it is also an equality filter.
created_at comes last because it supports sorting after the filters.

A useful rule of thumb: put equality filters first, then range or sorting columns. This is not a universal law, but it is a strong starting point for many application queries.

Step 3: Avoid Functions on Indexed Columns

Indexes work best when MySQL can compare raw column values. A common mistake is wrapping an indexed column in a function.

Less efficient:

SELECT id, total_cents FROM orders WHERE DATE(created_at) = '2026-05-05';

This looks readable, but DATE(created_at) forces MySQL to calculate a value for each row before comparing it. That can prevent efficient index usage.

Better:

SELECT id, total_cents FROM orders WHERE created_at >= '2026-05-05 00:00:00' AND created_at < '2026-05-06 00:00:00';

Now MySQL can use an index on created_at or a composite index that includes it.

CREATE INDEX orders_created_idx ON orders (created_at);

In application code, generate date ranges before sending the query. Do not make the database transform every row if a simple boundary comparison would work.

Step 4: Select Only the Columns You Need

Using SELECT * is convenient during development, but it can waste memory, network bandwidth, and disk reads. It also makes queries more fragile because new columns automatically become part of the result.

Avoid this:

SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

Prefer this:

SELECT id, status, total_cents, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

Sometimes, a query can be satisfied directly from the index. This is called a covering index. For example:

CREATE INDEX orders_user_created_total_idx ON orders (user_id, created_at DESC, total_cents, status);

Then this query may be able to read only the index:

SELECT status, total_cents, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

Covering indexes can be powerful, but do not create huge indexes for every query. Indexes speed up reads but add cost to inserts, updates, deletes, and storage.

Step 5: Fix Pagination Before It Becomes Slow

Offset-based pagination is common:

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

The problem is that MySQL still has to walk past many rows before returning the page. Page 1 may be fast, while page 500 becomes slow.

For feeds, logs, and admin lists, keyset pagination is usually better. Instead of asking for “page 500,” ask for “the next 20 records after this known position.”

SELECT id, total_cents, created_at FROM orders WHERE status = 'paid' AND created_at < '2026-05-05 15:30:00' ORDER BY created_at DESC LIMIT 20;

Add an index that supports the filter and sort:

CREATE INDEX orders_status_created_idx ON orders (status, created_at DESC);

In your API response, return the last record’s created_at value as the next cursor.

{ "data": [ { "id": 501, "total_cents": 3499, "created_at": "2026-05-05 15:30:00" } ], "next_cursor": "2026-05-05 15:30:00" }

If multiple rows can have the same timestamp, use a tie-breaker with id:

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

Then use this index:

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

Step 6: Watch Out for N+1 Queries

An N+1 query problem happens when your app loads one list, then runs one extra query per row. For example:

-- First query SELECT id, total_cents FROM orders WHERE user_id = 42 LIMIT 10; -- Then repeated 10 times SELECT product_name, quantity FROM order_items WHERE order_id = ?;

For 10 orders, this runs 11 queries. For 100 orders, it runs 101 queries. Instead, fetch related rows in one query.

SELECT order_id, product_name, quantity FROM order_items WHERE order_id IN (101, 102, 103, 104, 105);

Make sure the foreign key column is indexed:

CREATE INDEX order_items_order_idx ON order_items (order_id);

Most ORMs can eager-load relationships. The exact syntax differs, but the goal is the same: load the main records and their related records in batches, not one row at a time.

Step 7: Keep an Index Review Checklist

Before adding an index, ask practical questions:

Which query is slow?
Which columns are used in WHERE, JOIN, ORDER BY, and GROUP BY?
Does EXPLAIN show a full table scan?
Will a composite index match the query better than several separate indexes?
Is this query frequent enough to justify the write overhead?

Also remove unused indexes when you find them. Too many indexes can slow down writes and increase storage usage. For a write-heavy table, every new index should have a clear reason.

Conclusion

MySQL optimization is not magic. Start with the slow query, inspect it with EXPLAIN, then design indexes around real access patterns. Avoid functions on indexed columns, select only the columns you need, replace deep OFFSET pagination with keyset pagination, and fix N+1 queries early. These habits make applications faster without making the code harder to understand.

MySQL Optimization in Practice: Find and Fix Slow Queries with Indexes