Slow MySQL Query? Start with EXPLAIN, Not Adding Servers

When a website starts to slow down, the database often becomes the prime suspect. The problem is that many people immediately upgrade their server specifications without investigating which query is actually problematic. A single query that unnecessarily reads hundreds of thousands of rows can still be slow, even if the server is made more expensive.

This is where EXPLAIN comes in handy. This command helps us see the execution plan of the query: which tables are read first, which indexes are used, how many rows are estimated to be processed, and how the tables are joined. MySQL also provides EXPLAIN ANALYZE to compare the optimizer's estimates with the actual conditions when the query is executed. ([dev.mysql.com](https://dev.mysql.com/doc/refman/8.4/en/explain.html?utm_source=openai))

EXPLAIN is like a roadmap for the query

Imagine you ask someone to find a book in a large library. There are two ways. They can check each shelf one by one, or use a catalog that directly shows the book's location. In a database, the catalog roughly acts like an index.

Without the appropriate index, MySQL may need to read many rows to find the matching data. With an index, the database can narrow down the search. However, an index is not a magic button. Too many indexes also add overhead when new data is inserted, modified, or deleted. MySQL's own documentation emphasizes that indexes need to be chosen carefully because they have storage costs and maintenance costs. ([dev.mysql.com](https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html?utm_source=openai))

A simple example to try immediately

For instance, we have a table orders and often run the following query:

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

Before guessing, run:

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

The result is usually in the form of a table with columns like type, possible_keys, key, rows, and Extra. You don't have to understand everything at once. Start with the following three things:

• key: the index actually chosen by MySQL. If the value is empty, it doesn't necessarily indicate a problem, but it needs to be checked.
• rows: the estimated number of rows that need to be read. A very large number is suspicious, especially if the final result of the query only requires a few rows.
• type: an overview of how MySQL accesses the data. Index-based access is generally more efficient than reading the entire table, but the best meaning still depends on the size of the data and the shape of the query.

Don't just add indexes one by one

A common mistake is to create separate indexes for each column:

CREATE INDEX idx_customer ON orders (customer_id);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_created ON orders (created_at);

These indexes may help some queries, but they may not be the best choice for the example query. Since the query uses multiple conditions at once and sorts by date, we need to test the possibility of a composite index, which is an index that includes more than one column.

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

The order of columns is important. In a composite index, MySQL can utilize the leftmost part of the index arrangement. The index (customer_id, status, created_at) can assist searches based on customer_id, or based on customer_id and status. However, the same index may not help a query that only searches based on status. This concept is known as leftmost prefix. ([dev.mysql.com](https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html?utm_source=openai))

Compare before and after

Don't assume a query is definitely faster just because an index has been created. Run EXPLAIN before the change, add the index in a testing environment, and then run it again. Observe whether the key value changes and whether the estimated rows decreases.

For more realistic testing, use:

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

This command executes the query and displays actual timing information, the number of rows returned, and the number of iterations at each part of the execution plan. Since the query is actually run, use it carefully on operations that modify data and prioritize staging environments or safe data copies. ([dev.mysql.com](https://dev.mysql.com/doc/refman/8.4/en/explain.html?utm_source=openai))

If the index is not used, don't immediately blame MySQL

There are several reasons why a query may not use an available index. It could be that the table is too small, making reading the entire table cheaper. It could also be that the query conditions make the index less effective, for example, when a function is applied to a column:

SELECT * FROM users
WHERE YEAR(created_at) = 2026;

In such cases, the database may struggle to use a regular index on created_at. One more index-friendly approach is to use a date range:

SELECT * FROM users
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';

Data type comparisons are also important. Columns used in joins should have compatible types and sizes. Additionally, search patterns like LIKE '%word%' usually do not give the same opportunities for B-tree indexes as searches with fixed prefixes.

What does this mean for us?

Database performance is not just about having a big server. More importantly, it is about ensuring that applications request data in a sensible way. Simple queries executed thousands of times per minute can become a significant burden, while more complex queries may still perform well if they use the right execution plan.

Make it a habit to log slow queries, test with data that closely resembles production conditions, and read EXPLAIN before changing the database structure. If using a PHP framework, also check the queries generated by the ORM or query builder. Sometimes the problem is not with a single query visible in the code, but with the N+1 query pattern: the application retrieves a main list, then runs additional queries for each item.

What you can do now

1. Choose one page that feels slow and log the database queries being executed.
2. Run EXPLAIN on that query.
3. Check the key, rows, and Extra columns.
4. Ensure that the WHERE conditions, join columns, and sorting have appropriate index strategies.
5. Test changes in staging, then compare the times before and after.
6. Only remove indexes that are truly unused after checking their impact on other queries.

This process may feel slower than simply upgrading the hosting package. However, the results are usually more lasting: we are not just buying additional computing power, but understanding why the application is slow and addressing the root cause.

Sources & further reading

• MySQL 8.4 Reference Manual: EXPLAIN Statement
• MySQL 8.4 Reference Manual: Multiple-Column Indexes
• MySQL 8.4 Reference Manual: Optimization and Indexes
• MySQL 8.4 Reference Manual: How MySQL Uses Indexes

– Rio Yotto @rioyotto