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.
Invisible columns allow us to hide columns from certain SQL queries, such as those that use the asterisk wildcard (*) to implicitly select all columns. But along with this comes some implications for when we want to insert data into those columns.
If we want to insert data into an invisible column, we need to explicitly name that column in the column list. We can’t use a blank column list to insert data into all columns like we can with visible columns. If we try to do that, we’ll get an error. That said, we can omit the invisible column from the column list in order to have its default value inserted.
MySQL 8.3 introduced the explain_json_format_version system variable that allows us to specify which JSON output format should be used when we use the EXPLAIN statement with FORMAT=JSON.
It also applies when we omit the FORMAT argument and the explain_format system variable is set to JSON (because explain_format specifies the default format to be used by EXPLAIN).
In MySQL we can use the EXPLAIN statement to get information about SQL queries that we run against the database. We can also get information about tables and views.
EXPLAIN is a synonym for DESCRIBE (and its short form DESC). Even though these statements all work exactly the same, there’s a common convention amongst MySQL developers to use DESCRIBE for certain tasks and EXPLAIN for others. Typically, DESCRIBE is often used to get information about tables and views, whereas EXPLAIN is used to get information about queries, such as query execution plans.
Most of the major RDBMSs provide us with the ability to perform set operations.
A set operation is one that combines the results of multiple query blocks into one. For example, we could combine the result of two separate SELECT statements. The results are displayed in a single result set as though we’d run a single query.
If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format’” in MySQL, it’s probably because you’re trying to run EXPLAIN ANALYZE with the wrong output format.
The EXPLAIN ANALYZE statement only supports the TREE output format. However, there are a few things to be aware of regarding this.
Either way, the quickest way to fix the error is to use FORMAT=TREE in your EXPLAIN ANALYZE statement.
If you’ve got a table with an invisible column in MySQL, but you no longer want the column to be invisible, you can use the ALTER TABLE statement to make it visible.
Invisible columns are columns that can’t be accessed by queries that use the asterisk wildcard (*) to select all columns (although they can be accessed by explicitly naming the invisible column in the SELECT list).
In MySQL we can use the DESCRIBE statement to get information about table structure in the database, or to obtain a query execution plan for a query. For example, we can get information about column names, their data types, default values, etc.
The DESC and EXPLAIN statements are synonyms of DESCRIBE, so we can alternatively use those to do the same thing.
If you’re getting an error that reads “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION’” in MySQL, it’s probably because you’re trying to use the FOR CONNECTION option with EXPLAIN ANALYZE, but EXPLAIN ANALYZE doesn’t support the FOR CONNECTION option.
It’s true that we can use the FOR CONNECTION option with the EXPLAIN statement, we can’t use it when using the ANALYZE variant of EXPLAIN (we can use EXPLAIN with or without ANALYZE). At least, this is how it works as of this writing (MySQL 8.3).
To fix, either remove the ANALYZE keyword from the statement or don’t use the FOR CONNECTION option.
Since MySQL 8.0.23 we’ve had the ability to create invisible columns, which are columns that are normally hidden to queries (but can easily be accessed if required).
With the introduction of invisible columns comes the inevitable requirement to check a table to see if it contains any invisible columns.
Fortunately there are many ways we can do this. Below are five ways to check a table for invisible columns.