MariaDB EXPLAIN

By using EXPLAIN, you are asking MariaDB to show you its “thought process” before it ever touches your data. In this comprehensive guide, I’ll walk you through how to use this statement like a pro to eliminate bottlenecks and improve your performance.

MariaDB EXPLAIN

What is MariaDB EXPLAIN?

The EXPLAIN statement is an administrative command that provides a detailed execution plan for your SQL queries. When you prepend a query with the EXPLAIN keyword, MariaDB doesn’t execute the query. Instead, it analyzes the structure, checks the available indexes, and outputs a table describing how it intends to retrieve the data.

Think of it as a GPS route map for your data. Before you start the car (execute the query), the GPS tells you which highways (indexes) it will use and where it expects traffic jams (full table scans).

Why Use EXPLAIN?

• Identify Missing Indexes: It tells you exactly where a new index could save thousands of rows of scanning.
• Analyze Join Order: It reveals if MariaDB is joining tables in an inefficient sequence.
• Prevent Full Table Scans: It flags queries that are forced to read every single row in a table.
• Optimize Subqueries: It helps you decide if a subquery should be refactored into a JOIN.

The Core Syntax

In MariaDB, using EXPLAIN is incredibly straightforward. You can use it with SELECT, UPDATE, DELETE, INSERT, and REPLACE statements.

SQL

EXPLAIN SELECT | DELETE | UPDATE

-- Standard usage
EXPLAIN SELECT * FROM orders WHERE customer_id = 502;

-- Modern JSON format for deep analysis
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 502;

Example

Let’s understand using the below demonstration.

EXPLAIN SELECT * FROM sales;

After executing the above query, I got the expected output as shown in the screenshot below.

Look how EXPLAIN shows the table name, number of keys in column key, the key length in column key_len and the number of rows in column rows, etc.

Decoding the EXPLAIN Output Columns

Column	Description	Priority Level
id	The sequential identifier for each SELECT within the query.	Low
select_type	The type of query (SIMPLE, PRIMARY, SUBQUERY, etc.).	Medium
table	The name of the table the row refers to.	Low
type	The join type (e.g., system, const, eq_ref, ALL). The most important column.	Critical
possible_keys	The indexes MariaDB could have used.	High
key	The index MariaDB actually decided to use.	Critical
key_len	The length of the chosen key in bytes.	Medium
rows	The estimated number of rows MariaDB must examine.	High
Extra	Additional info like “Using filesort” or “Using temporary”.	High

The “Type” Column

If you only look at one column, make it the type column. This tells you how MariaDB is finding the rows. Here is the hierarchy from fastest to slowest:

1. system & const

The table has only one matching row. This is the fastest possible type because the value is treated as a constant. This usually happens when you use a Primary Key or Unique Index in your WHERE clause.

2. eq_ref

One row is read from this table for each combination of rows from the previous table. This is the best possible join type and occurs when all parts of an index are used by the join.

3. ref

MariaDB uses a non-unique index to find rows. This is generally very good performance, especially if the index is highly selective (matches only a few rows).

4. range

The index is used to find rows within a specific range, such as using BETWEEN, >, or <.

5. index

This is the “Index Scan.” MariaDB scans the entire index tree. While faster than a full table scan, it still indicates that you might need a more specific index.

6. ALL

The dreaded “Full Table Scan.” MariaDB reads every single row in the table to find a match. For a table with millions of rows, this is a performance killer.

Expert Tip: If you see type: ALL in a high-traffic query, you almost certainly need to add an index on the columns used in your WHERE or JOIN clauses.

Understanding the “Extra” Field

The Extra column contains “fine-print” details that can reveal hidden performance drains.

• Using filesort: This is a major warning. It means MariaDB must do an extra pass to find out how to retrieve the rows in sorted order. Usually solved by adding an index that matches your ORDER BY clause.
• Using temporary: To resolve the query, MariaDB needs to create a temporary table. This often happens with complex GROUP BY or DISTINCT queries.
• Using index: This is excellent! It means the query is a “Covering Index” query—MariaDB found everything it needed in the index and didn’t even have to look at the actual table data.

Best Practices

After analyzing your plan, use these strategies to improve your results:

• Index the “Where” and “Join” Columns: Ensure columns used for filtering or connecting tables have appropriate indexes.
• *Avoid SELECT : Only retrieve the columns you need. This increases the chances of using a “Covering Index.”
• Match Data Types: Ensure that if you are joining order_id (INT) to transaction_id (INT), both columns have the exact same data type and collation. Mismatched types can prevent index usage.
• Use the Smallest Column Length: In the USA, if you’re storing a 5-digit Zip Code, don’t use a LONGTEXT. Use a VARCHAR(5) or INT. Smaller columns make for faster indexes.

Summary:

Follow this checklist:

1. Is the type for all tables better than index or ALL?
2. Is the key column showing the index I expected?
3. Is the rows count significantly higher than the actual result set? (If so, the index isn’t selective enough).
4. Does the Extra column show Using filesort? If yes, I re-evaluate the ORDER BY logic.

Mastering EXPLAIN transforms you from a developer who “hopes” the database is fast to a specialist who “knows” why it is.

You may also like the following articles:

• MariaDB TINYINT
• MariaDB SHOW COLUMNS
• How to Create Table in MariaDB
• How to List Databases in MariaDB
• How to Show Users in MariaDB
• MariaDB Between