Why Your MySQL Query Is Slow? A Complete Guide to Using EXPLAIN in MySQL

Introduction

Have you ever written a MySQL query that works perfectly on your local machine but becomes painfully slow in production? This is one of the most common problems developers face as databases grow. A query that takes only a few milliseconds on a small dataset can suddenly take several seconds—or even minutes—when your tables contain millions of rows.

Fortunately, MySQL provides a powerful tool called EXPLAIN that helps you understand exactly how a query is executed. Instead of guessing why a query is slow, EXPLAIN shows the execution plan, allowing you to identify bottlenecks and optimize your SQL efficiently.

In this guide, you'll learn:

• What EXPLAIN is

• Why queries become slow

• How to read EXPLAIN output

• Real-world optimization examples

• Best practices to improve query performance

What Is EXPLAIN in MySQL?

The EXPLAIN statement tells MySQL to display the execution plan for a query instead of executing it normally.

It answers questions such as:

• Which table is read first?

• Is MySQL using an index?

• How many rows will it scan?

• Which join method is being used?

• Is the query performing a full table scan?

Simply place EXPLAIN before your SQL statement.

EXPLAIN
SELECT *
FROM users
WHERE email = 'john@example.com';

Instead of returning data, MySQL returns information about how it plans to execute the query.

Why Do MySQL Queries Become Slow?

There isn't a single reason why a query becomes slow. Common causes include:

• Missing indexes

• Using SELECT * unnecessarily

• Scanning millions of rows

• Incorrect JOIN conditions

• Sorting large datasets

• Using functions on indexed columns

• Searching with leading wildcards (LIKE '%abc')

The good news is that EXPLAIN helps identify these problems.

Sample Table

Assume we have the following table.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150),
    city VARCHAR(100),
    created_at DATETIME
);

Suppose this table contains 2 million records.

Example 1: Query Without an Index

EXPLAIN
SELECT *
FROM users
WHERE city = 'London';

Example output:

Problem

Notice the value of type = ALL.

This means MySQL performs a Full Table Scan, checking every row to find matching records.

Scanning two million rows is expensive and becomes slower as your database grows.

Adding an Index

CREATE INDEX idx_city
ON users(city);

Now run EXPLAIN again.

EXPLAIN
SELECT *
FROM users
WHERE city = 'London';

Result:

Now MySQL scans only the relevant rows instead of the entire table.

This simple index can reduce execution time dramatically.

Understanding Important EXPLAIN Columns

1. type

The type column is one of the most important indicators of query performance.

Whenever you see ALL, investigate whether an index should be added.

2. possible_keys

This column lists the indexes MySQL could use.

Example:

possible_keys: idx_email

If this value is NULL, there may not be a suitable index.

3. key

This shows the index MySQL actually chose.

Example:

key: idx_email

If this is NULL, the optimizer isn't using any index.

4. rows

This estimates how many rows MySQL expects to examine.

Examples:

rows = 15

Excellent.

rows = 1,500,000

Needs optimization.

The smaller this number, the better.

5. Extra

This provides additional execution details.

Common values include:

Using where

Rows are filtered using the WHERE clause.

Usually normal.

Using index

The query is satisfied entirely using an index.

Very efficient.

Using filesort

MySQL performs an additional sorting operation.

May become expensive for large datasets.

Using temporary

MySQL creates a temporary table.

Often appears with GROUP BY or ORDER BY.

Try to optimize such queries if performance is poor.

Example 2: ORDER BY Without an Index

EXPLAIN
SELECT *
FROM users
ORDER BY created_at DESC;

Output:

Using filesort

Because no index exists on created_at, MySQL must sort all rows manually.

Create an index:

CREATE INDEX idx_created_at
ON users(created_at);

Sorting becomes much faster.

Example 3: Function on an Indexed Column

Suppose created_at already has an index.

Bad query:

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

Although an index exists, MySQL cannot efficiently use it because the function is applied to every row.

Better approach:

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

This allows MySQL to use the index effectively.

Example 4: SELECT *

Many developers write:

SELECT *
FROM users;

If you only need the user's name and email, write:

SELECT name, email
FROM users;

Retrieving fewer columns reduces disk I/O, memory usage, and network transfer.

Example 5: LIKE with Leading Wildcards

Bad:

SELECT *
FROM users
WHERE name LIKE '%john';

The leading % prevents MySQL from using an index.

Better:

SELECT *
FROM users
WHERE name LIKE 'john%';

This allows index-based searching.

Best Practices for Faster Queries

Follow these recommendations to keep your queries efficient:

• Create indexes on frequently searched columns.

• Avoid using SELECT * unless necessary.

• Review queries using EXPLAIN before deploying them.

• Keep indexes updated and remove unused ones.

• Avoid applying functions to indexed columns in WHERE clauses.

• Limit the number of returned rows with LIMIT whenever possible.

• Use appropriate data types for your columns.

• Write efficient JOIN conditions.

Common Beginner Mistakes

Here are mistakes developers frequently make:

Not checking EXPLAIN before deployment

Creating too many indexes

Forgetting indexes after adding new features

Using functions in WHERE clauses

Assuming every index improves performance

Ignoring the rows estimate

When Should You Use EXPLAIN?

Use EXPLAIN whenever you notice:

• Slow page loading

• Slow API responses

• Long-running reports

• Complex JOIN queries

• Heavy ORDER BY or GROUP BY operations

• Database CPU spikes

• Queries scanning large tables

Making EXPLAIN part of your development workflow can prevent many production performance issues.

Conclusion

Writing a correct SQL query is only the first step—writing an efficient one is what makes your application scalable.

The EXPLAIN statement is one of the most valuable tools in MySQL because it reveals how the database engine executes your queries. By understanding columns such as type, key, rows, and Extra, you can quickly identify full table scans, missing indexes, inefficient sorting, and other performance bottlenecks.

Whenever a query feels slow, don't guess. Run EXPLAIN, analyze the execution plan, and optimize based on real data. Developing this habit will help you build faster, more reliable applications and become a more confident SQL developer.