Mastering MySQL Explain: A Comprehensive Guide to Query Optimization

10 min readSep 4, 2023

MySQL Explain is a powerful tool for improving the performance of your database queries. It provides valuable insights into how MySQL processes your queries and helps you identify areas for optimization. In this comprehensive guide, we’ll break down the MySQL Explain output and use simple examples to make query optimization easy to understand.

Understanding the Basics
Scenario 1: Primary Key Search
Scenario 2: Index for Single Column
Scenario 3: Composite Index
Scenario 4: Sorting
Scenario 5: Possible Keys and Choosing Indexes
Scenario 6: Partial Index Usage
Scenario 7: Length of Used Index
Scenario 8: Partial Values Usage
Scenario 9: Avoiding Full Table Scans
Scenario 10: Optimizing Complex Queries
Summary

Understanding the Basics Before we dive into examples, let’s grasp the basics of MySQL Explain. It provides a roadmap for how MySQL executes a query. Here’s the structure of an Explain output.

| id | select_type | table | type | possible_keys | key | key_len | rows | Extra |
|----|-------------|-------|------|---------------|-----|---------|------|-------|
| 1  | SIMPLE      | users | ALL  | NULL          | NULL| NULL    | 500  | Using where |

Here’s what each column means:

id: Identifier for the select statement within the query.
select_type: Describes the type of SELECT query (e.g., SIMPLE for a standalone query).
table: Specifies the table being accessed.
type: Indicates the access method used (e.g., ALL for a full table scan).
possible_keys: Lists the indexes considered for optimization.
key: Shows the actual index used (or NULL if none).
key_len: Length of the index used.
rows: Number of rows MySQL must examine.
Extra: Additional information about the query execution.

Now, let’s explore various scenarios using easy examples.

Scenario 1: Primary Key Search

Primary Key Search Imagine a table of employees with “employee_id” as the primary key.

Before Indexing:

EXPLAIN SELECT * FROM employees WHERE employee_id = 42;

Explain output:

| id | select_type | table     | type | possible_keys | key     | key_len | rows | Extra        |
|----|-------------|-----------|------|---------------|---------|---------|------|--------------|
| 1  | SIMPLE      | employees | ALL  | NULL          | NULL    | NULL    | 500  | Using where |

Explanation:

MySQL is searching for a specific employee using the primary key.
MySQL does not use an index efficiently (type: ALL).
500 rows are examined.

After Indexing:

CREATE INDEX employee_id_idx ON employees(employee_id);

Now, let’s explain the same query after creating an index on “employee_id.”

EXPLAIN SELECT * FROM employees WHERE employee_id = 42;

Explain output:

| id | select_type | table     | type  | possible_keys | key            | key_len | rows | Extra        |
|----|-------------|-----------|-------|---------------|----------------|---------|------|--------------|
| 1  | SIMPLE      | employees | const | PRIMARY       | PRIMARY        | 4       | 1    | Using index |

Explanation:

We’re still searching for a specific employee using the primary key.
MySQL efficiently uses the primary key index.
Only one row is examined.

This demonstrates the power of primary keys for precise data retrieval.

Scenario 2: Index for Single Column

Index for Single Column Let’s say you have a table of products, and you want to count products in a specific category.

Before Indexing:

EXPLAIN SELECT COUNT(*) FROM products WHERE category = 'Electronics';

Explain output:

| id | select_type | table    | type | possible_keys | key     | key_len | rows | Extra       |
|----|-------------|----------|------|---------------|---------|---------|------|-------------|
| 1  | SIMPLE      | products | ALL  | NULL          | NULL    | NULL    | 100  | Using where |

Explanation:

We’re counting products in the “Electronics” category.
MySQL does not efficiently use an index (type: ALL).
100 rows are examined.

After Indexing:

CREATE INDEX category_idx ON products(category);

Now, let’s explain the same query after creating an index on the “category” column.

EXPLAIN SELECT COUNT(*) FROM products WHERE category = 'Electronics';

Explain output:

| id | select_type | table    | type | possible_keys | key     | key_len | rows | Extra       |
|----|-------------|----------|------|---------------|---------|---------|------|-------------|
| 1  | SIMPLE      | products | ref  | category_idx  | category| 12      | 10   | Using where |

Explanation:

We’re counting products in the “Electronics” category.
MySQL uses the “category_idx” index effectively.
Only 10 rows are examined.

To optimize this query, create an index on the “category” column:

CREATE INDEX category_idx ON products(category);

Now, MySQL will quickly count products in the specified category.

Scenario 3: Composite Index

Composite Index Consider a table of orders, and you want to filter orders by both “country” and “payment_method.”

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country = 'USA' AND payment_method = 'Credit Card';

Explain output:

| id | select_type | table  | type | possible_keys      | key              | key_len | rows | Extra          |
|----|-------------|--------|------|--------------------|------------------|---------|------|----------------|
| 1  | SIMPLE      | orders | ALL  | NULL               | NULL             | NULL    | 100  | Using filesort |

Explanation:

We’re filtering orders based on both “country” and “payment_method.”
MySQL does not use any index for filtering (type: ALL).
All 100 rows are examined.

After Indexing:

CREATE INDEX country_payment_idx ON orders(country, payment_method);

Now, let’s explain the same query after creating a composite index on both columns.

EXPLAIN SELECT * FROM orders WHERE country = 'USA' AND payment_method = 'Credit Card';

Explain output:

| id | select_type | table  | type | possible_keys      | key              | key_len | rows | Extra                |
|----|-------------|--------|------|--------------------|------------------|---------|------|----------------------|
| 1  | SIMPLE      | orders | ref  | country_payment_idx| country_payment  | 194     | 10   | Using index condition |

Explanation:

We’re still filtering orders based on both “country” and “payment_method.”
MySQL efficiently uses the “country_payment_idx” composite index.
The “key_len” indicates a partial index usage (194).
Only 10 rows are examined.

To optimize this query, create a composite index on both columns:

CREATE INDEX country_payment_idx ON orders(country, payment_method);

Now, MySQL efficiently filters orders using the composite index.

Scenario 4: Sorting

Sorting Sorting is crucial for many queries. Suppose you want to retrieve the latest 10 orders by order date.

Before Indexing:

EXPLAIN SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

Explain output:

| id | select_type | table  | type | possible_keys | key        | key_len | rows | Extra          |
|----|-------------|--------|------|---------------|------------|---------|------|----------------|
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL       | NULL    | 100  | Using filesort |

Explanation:

We’re sorting orders by “order_date” in descending order.
MySQL can’t use any index for sorting (type: ALL).
All 100 rows are examined.

After Indexing:

CREATE INDEX order_date_idx ON orders(order_date);

Now, let’s explain the same query after creating an index on the “order_date” column.

EXPLAIN SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

Explain output:

| id | select_type | table  | type | possible_keys | key         | key_len | rows | Extra           |
|----|-------------|--------|------|---------------|-------------|---------|------|-----------------|
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL        | NULL    | 100  | Using filesort  |

Explanation:

We’re still sorting orders by “order_date” in descending order.
MySQL is not efficiently using the index (type: ALL).
All 100 rows are examined.

To optimize sorting, create an index on the “order_date” column:

CREATE INDEX order_date_idx ON orders(order_date);

Now, MySQL will efficiently use the index for sorting.

Scenario 5: Possible Keys and Choosing Indexes

Possible Keys and Choosing Indexes When MySQL processes a query, it considers multiple indexes to determine the most efficient one to use. Let’s examine this concept with a query that filters orders by “country.”

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country = 'Canada';

The Explain output may provide insights into MySQL’s index selection process:

Explain output:

| id | select_type | table  | type | possible_keys | key   | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|------|---------|------|-------------|
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | 100  | Using where |

Explanation:

We’re filtering orders by “country.”
MySQL considers no useful indexes (type: ALL).
100 rows are examined.

MySQL’s query optimizer is quite effective at selecting the appropriate index for improved query performance. In this case, it chose no index because there was none suitable, resulting in inefficient data retrieval.

After Indexing:

CREATE INDEX country_idx ON orders(country);

Now, let’s explain the same query after creating an index on the “country” column.

EXPLAIN SELECT * FROM orders WHERE country = 'Canada';

Explain output:

| id | select_type | table  | type | possible_keys | key   | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|------|---------|------|-------------|
| 1  | SIMPLE      | orders | ref  | country_idx  | country | 12      | 5    | Using where |

Explanation:

We’re still filtering orders by “country.”
MySQL now uses the “country_idx” index effectively.
Only 5 rows are examined.

MySQL’s query optimizer selected the appropriate index after indexing the “country” column, resulting in efficient data retrieval.

Scenario 6: Partial Index Usage

Partial Index Usage Sometimes, MySQL can utilize only a part of an index, known as a “partial index.” Let’s explore this concept further.

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country LIKE 'Un%';

The Explain output might look like this:

Explain output:

| id | select_type | table  | type | possible_keys | key     | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|---------|---------|------|-------------|
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL    | NULL    | 100  | Using where |

Explanation:

The query uses the LIKE operator to filter rows where the “country” column starts with “Un.”
MySQL doesn’t efficiently use an index (type: ALL).
100 rows are examined.

After Indexing:

CREATE INDEX country_idx ON orders(country);

Now, let’s explain the same query after creating an index on the “country” column.

EXPLAIN SELECT * FROM orders WHERE country LIKE 'Un%';

Explain output:

| id | select_type | table  | type  | possible_keys | key     | key_len | rows | Extra       |
|----|-------------|--------|-------|---------------|---------|---------|------|-------------|
| 1  | SIMPLE      | orders | range | country_idx   | country | 12      | 30   | Using where |

Explanation:

The query still uses the LIKE operator to filter rows where the “country” column starts with “Un.”
MySQL now efficiently uses the “country_idx” index.
Only 30 rows are examined.

Partial index usage can be beneficial when your query filters based on a subset of data within a column, reducing the number of rows examined.

Scenario 7: Length of Used Index

Length of Used Index We can understand if MySQL used only part of an index by checking the key_len column value of the EXPLAIN statement. For example, our query that uses both columns of an index:

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country = 'USA' AND payment_method = 'Credit Card';

This will return 194 for the key_len column value, which is a partial length of the index:

Explain output:

| id | select_type | table  | type | possible_keys      | key              | key_len | rows | Extra          |
|----|-------------|--------|------|--------------------|------------------|---------|------|----------------|

Our other query will return a different key_len value:

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country = 'USA'

As we can see:

Explain output:

| id | select_type | table  | type | possible_keys | key     | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|---------|---------|------|-------------|

That’s how we know if a full index was used for the query or only a part of it.

Scenario 8: Partial Values Usage

Partial Values Usage MySQL is so cool it can even use a part of a column value in the index for query execution. Let’s assume the following query:

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country LIKE 'Un%';

As we can see, MySQL is still able to use the index:

Explain output:

| id | select_type | table  | type | possible_keys | key      | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|----------|---------|------|-------------|

In this case, MySQL will filter results based on a partial value of an index column. But the same rule applies here, the partial column value should be a prefix, or otherwise, the index can’t be used:

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country LIKE '%ne';

This will lead to a full table scan:

Explain output:

| id | select_type | table  | type | possible_keys | key      | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|----------|---------|------|-------------|

That’s because the “country” column is in the index but is not an index prefix.

Scenario 9: Avoiding Full Table Scans

Avoiding Full Table scan indexes can significantly improve query performance, but when MySQL has to examine a large number of rows, it can still lead to slower queries. To avoid full table scans, consider creating larger indexes that cover more query cases.

Before Indexing:

EXPLAIN SELECT * FROM orders WHERE country = 'USA';

Explain output:

| id | select_type | table  | type | possible_keys | key   | key_len | rows | Extra       |
|----|-------------|--------|------|---------------|------|---------|------|-------------|

After Creating a Larger Index:

CREATE INDEX filter ON orders(country, industry, order_date);

With this larger index, MySQL can efficiently use it for various filtering and sorting scenarios, reducing the need for full table scans.

Scenario 10: Optimizing Complex Queries While we’ve explored straightforward examples, complex queries may involve subqueries, joins, and more. Applying the principles of index usage and query optimization can greatly enhance the performance of your more intricate database queries. Always analyze the Explain output to identify potential bottlenecks and areas for improvement.

Summary

Mastering MySQL Explain is a valuable skill for optimizing your database queries. Remember these key takeaways:

Use indexes effectively to reduce row examination.
Consider composite indexes for multi-column filters.
Understand partial index usage for specific filtering.
Optimize sorting by creating indexes on sorting columns.
Make use of partial values in index columns for efficient queries.
Avoid full table scans by creating comprehensive indexes.
Continuously optimize complex queries for peak performance.

By applying these concepts and continuously monitoring query performance, you can ensure that your MySQL database operates efficiently and delivers rapid responses to your application’s requests.

Mastering MySQL Explain: A Comprehensive Guide to Query Optimization

Table of Contents

Scenario 1: Primary Key Search

Before Indexing:

After Indexing:

Scenario 2: Index for Single Column

Before Indexing:

After Indexing:

Scenario 3: Composite Index

Before Indexing:

After Indexing:

Scenario 4: Sorting

Before Indexing:

After Indexing:

Scenario 5: Possible Keys and Choosing Indexes

Before Indexing:

After Indexing:

Scenario 6: Partial Index Usage

Before Indexing:

After Indexing:

Scenario 7: Length of Used Index

Before Indexing:

Before Indexing:

Explain output:

Scenario 8: Partial Values Usage

Before Indexing:

Before Indexing:

Scenario 9: Avoiding Full Table Scans

Before Indexing:

After Creating a Larger Index:

Summary

Written by Sohel Rana