Optimizing MySQL Query Performance with DESCRIBE and EXPLAIN

DESCRIBE and EXPLAIN statements are two of the most common statements that concerns MySQL performance issues. When you ask people how to optimize a query, the DESCRIBE and EXPLAIN statements are one of the first ones that get mentioned. We will explore them in this article.

Understanding DESCRIBE

The 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:

Sample DESCRIBE Output

Here's what it means:

• Field describes the column name;
• Type describes the data type of the column;
• Null describes whether the column can contain NULL values;
• Key describes whether the column is indexed;
• Default describes the column's default value;
• Extra describes additional information about the columns (for example, if the column has an AUTO_INCREMENT option, the value will be auto_increment).

The DESCRIBE statement can be useful when you have access to a MySQL server via SSH and are not sure what columns a database contains.

Understanding EXPLAIN

The EXPLAIN statement provides information about how MySQL executes statements. The statement works with SELECT, DELETE, INSERT, REPLACE, UPDATE statements, and, in MySQL 8.0.19 and later, also with TABLE statements.

This is how EXPLAINing a simple query looks like:

Sample EXPLAIN Output

Here's what it means:

• id describes the query ID;
• select_type describes the SELECT type (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.);
• table describes the table used for the output;
• partitions describes any matching partitions;
• type describes the join type;
• possible_keys describes the possible indexes that can be used;
• key describes the index that is used;
• key_len describes the length of the used index;
• ref describes the columns or constants that uses an index to select data from a table;
• rows describes an approximate amount of rows to be examined;
• filtered describes an estimate percentage of rows that will be filtered by a query;
• Extra provides additional information.

The EXPLAIN statement is used with a statement that needs explaining to see the statement execution plan.

Alternatives To DESCRIBE

The 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;

The 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

The 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:

Sample EXPLAIN FOR CONNECTION Output

Conclusion

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