Category: MySQL

How the EXPLAIN Statement Works in MySQL

Posted on by Ian

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.

Continue reading →

Fix “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE with JSON format'” in MySQL

Posted on by Ian

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.

Continue reading →

How to “Unhide” an Invisible Column in MySQL

Posted on by Ian

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).

Continue reading →

Fix “This version of MySQL doesn’t yet support ‘EXPLAIN ANALYZE FOR CONNECTION'” in MySQL

Posted on by Ian

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.

Continue reading →

5 Ways to Check a Table for Invisible Columns in MySQL

Posted on by Ian

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.

Continue reading →

An Introduction to Invisible Columns in MySQL

Posted on by Ian

In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.

An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.

Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.

Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.

Continue reading →

2 Ways to Rename the Columns Returned by the TABLE Statement in MySQL

Posted on by Ian

MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.

When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.

Continue reading →