Why ‘FORMAT=TRADITIONAL’ Fails but ‘explain_format=TRADITIONAL’ Succeeds When Running EXPLAIN ANALYZE in MySQL

by

If you’ve been using EXPLAIN ANALYZE to analyse your MySQL queries, you may have encountered a little quirk regarding the output format: When we explicitly use FORMAT=TRADITIONAL in our EXPLAIN ANALYZE statement, we get an error. But when our explain_format system variable is set to TRADITIONAL we don’t get an error.

What’s going on with that? Is this a bug?

First up, no it’s not a bug. It can all be explained by the way EXPLAIN ANALYZE handles our explain_format setting.

How EXPLAIN ANALYZE Handles the explain_format Setting

The explain_format system variable was added in MySQL 8.0.32. This variable determines the default output format used by EXPLAIN in the absence of a FORMAT option when displaying a query execution plan.

More specifically, it applies to EXPLAIN ANALYZE. It doesn’t apply when we use EXPLAIN to obtain information about table structure.

When we use the EXPLAIN ANALYZE statement, we have the option of explicitly specifying the output format or not. If we don’t explicitly specify this, then the format set in our explain_format system variable is used.

However, given EXPLAIN ANALYZE only supports the TREE format, this could inadvertently cause us some issues or confusion if we don’t know how it works.

Here’s how it works from (from MySQL 8.0.32, when the explain_format system variable was introduced):

  • If the value of the explain_format system variable is TREE or TRADITIONAL (or its synonym DEFAULT), then EXPLAIN ANALYZE uses the TREE format.
  • If the value of explain_format is JSON, then EXPLAIN ANALYZE returns an error unless FORMAT=TREE is explicitly specified as part of the EXPLAIN ANALYZE statement.

The reason it works like this is for backward compatibility. It ensures that EXPLAIN ANALYZE continues to use the TREE format by default, as it did prior to the introduction of explain_format.

Example

Let’s check the current value of my explain_format variable:

SELECT @@explain_format;

Result:

+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+

OK, so it’s using its default value of TRADITIONAL.

Now let’s run EXPLAIN ANALYSE without specifying the output format:

EXPLAIN ANALYZE
SELECT * FROM artists;

Result:

 -> Table scan on artists  (cost=1.85 rows=16) (actual time=0.2..0.214 rows=16 loops=1)

It’s a very simple example, but we can see that it worked without error.

Yes it worked without error, even though my explain_format is set to a non-supported value (remembering that TREE is the only supported value). That’s because of the rule mentioned above. When explain_format is set to TRADITIONAL, the EXPLAIN ANALYZE statement uses TREE. And this is for backward compatibility.

But if we explicitly specify FORMAT=TRADITIONAL in our EXPLAIN ANALYZE statement, then we’ll get an error:

EXPLAIN ANALYZE FORMAT=TRADITIONAL
SELECT * FROM artists;

Result:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format'

We can see that it works differently to having TRADITIONAL specified in the explain_format system variable.

So, rather than being a bug, it works this way by design – basically for backward compatibility reasons.

Conclusion

The EXPLAIN ANALYZE statement only supports the TREE format. Therefore, we must ensure we only use it with the TREE format. We can do this by either explicitly specifying FORMAT=TREE in our statement, or by ensuring that our explain_format system variable is set to either TRADITIONAL, DEFAULT, or TREE.

Perhaps the best way to safeguard against any errors is to explicitly specify FORMAT=TREE in our EXPLAIN ANALYZE statement.