EXPLAIN statements are two of the most common statements that concerns MySQL performance issues. When you ask people how to optimize a query, the
EXPLAIN statements are one of the first ones that get mentioned. We will explore them in this article.
DESCRIBE statement is primarily used when the need to find out information about each of table’s columns arises. This is how the output of a
DESCRIBE statement looks like:
Here’s what it means:
Fielddescribes the column name;
Typedescribes the data type of the column;
Nulldescribes whether the column can contain
Keydescribes whether the column is indexed;
Defaultdescribes the column’s default value;
Extradescribes additional information about the columns (for example, if the column has an
AUTO_INCREMENToption, the value will be
DESCRIBE statement can be useful when you have access to a MySQL server via SSH and are not sure what columns a database contains.
EXPLAIN statement provides information about how MySQL executes statements. The statement works with
UPDATE statements, and, in MySQL 8.0.19 and later, also with
This is how
EXPLAINing a simple query looks like:
Here’s what it means:
iddescribes the query ID;
SELECTtype (i.e is it a simple
SELECT? Is it a result of a
UNION? Is it a derived table? A derived table dependent on another table? etc.);
tabledescribes the table used for the output;
partitionsdescribes any matching partitions;
typedescribes the join type;
possible_keysdescribes the possible indexes that can be used;
keydescribes the index that is used;
key_lendescribes the length of the used index;
refdescribes the columns or constants that uses an index to select data from a table;
rowsdescribes an approximate amount of rows to be examined;
filtereddescribes an estimate percentage of rows that will be filtered by a query;
Extraprovides additional information.
EXPLAIN statement is used with a statement that needs explaining to see the statement execution plan.
DESCRIBE statement provides information about columns in a table. The statement isn’t the only one that can be used in order to gain insight into the table structure though – multiple other statements, such as the
DESC statement (a shorthand of the
DESCRIBE statement) and the
SHOW COLUMNS statement can be used as well.
To get more information about the table structure we can also add the
FULL keyword to the
SHOW COLUMNS statement, so the statement might also look like this:
SHOW FULL COLUMNS FROM demo_table;
SHOW COLUMNS allows column filtering by using the
LIKE operator or
WHERE clause too:
SHOW COLUMNS FROM demo_table LIKE ...;
SHOW COLUMNS FROM demo_table WHERE ...;
That means that the column filtering feature can be used to see only columns that start with a, for example, certain letter or number, we can also use the
WHERE clause to tell the database to find rows matching certain conditions.
Digging Deeper Into
EXPLAIN is frequently used to obtain a query execution plan, but it’s usefulness doesn’t end there – the
EXPLAIN statement can also be used to get information on already running queries. Suppose our database is running the following query (the
SLEEP statement is in place to make the query run slower):
SELECT COUNT(*), SLEEP(10) FROM demo_table WHERE field_1 LIKE '%Demo%';
We can use the
EXPLAIN FOR CONNECTION statement to run
EXPLAIN for already running statements in the database which can be really useful if we want to find a query that has been running for a long time and we want to figure out why.
To simulate such a scenario we will use two separate sessions that connect to a database – one session will run our slow query (see above), the other will use the
EXPLAIN FOR CONNECTION statement.
Here’s how the results look like:
DESCRIBE statement is used to obtain information about table structure. The
EXPLAIN statement on the other hand helps us to gain insight into the query execution plans. The
DESCRIBE statement can be useful if you’re not sure what structure tables you’re working with contain and the
EXPLAIN statement can be useful when you see that a query is running for a long time and want to figure out why.