Optimizing MySQL Query Performance with DESCRIBE and EXPLAIN
Tags: mysql, performance, webdev, tutorial
Published: 2022-11-12 22:00:00

<!-- wp:paragraph -->
<p><code>DESCRIBE</code> and <code>EXPLAIN</code> statements are two of the most common statements that concerns MySQL performance issues. When you ask people how to optimize a query, the <code>DESCRIBE</code> and <code>EXPLAIN</code> statements are one of the first ones that get mentioned. We will explore them in this article.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Understanding <code>DESCRIBE</code></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The <code>DESCRIBE</code> 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 <code>DESCRIBE</code> statement looks like:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":343,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://breachdirectory.com/blog/wp-content/uploads/2020/07/Demo-DESCRIBE-Output.jpg" alt="" class="wp-image-343"/><figcaption><center>Sample DESCRIBE Output</center></figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Here's what it means:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li><code>Field</code> describes the column name;</li><li><code>Type</code> describes the data type of the column;</li><li><code>Null</code> describes whether the column can contain <code>NULL</code> values;</li><li><code>Key</code> describes whether the column is indexed;</li><li><code>Default</code> describes the column's default value;</li><li><code>Extra</code> describes additional information about the columns (for example, if the column has an <code>AUTO_INCREMENT</code> option, the value will be <code>auto_increment</code>).</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>The <code>DESCRIBE</code> statement can be useful when you have access to a MySQL server via SSH and are not sure what columns a database contains.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Understanding <code>EXPLAIN</code></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The <code>EXPLAIN</code> statement provides information about how MySQL executes statements. The statement works with <code>SELECT</code>, <code>DELETE</code>, <code>INSERT</code>, <code>REPLACE</code>, <code>UPDATE</code> statements, and, in MySQL 8.0.19 and later, also with <code>TABLE</code> statements.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>This is how <code>EXPLAIN</code>ing a simple query looks like:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":344,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://breachdirectory.com/blog/wp-content/uploads/2020/07/Demo-EXPLAIN-Output-1024x171.jpg" alt="" class="wp-image-344"/><figcaption><center>Sample <code>EXPLAIN</code> Output</center></figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Here's what it means:</p>
<!-- /wp:paragraph -->

<!-- wp:list -->
<ul><li><code>id</code> describes the query ID;</li><li><code>select_type</code> describes the <code>SELECT</code> type (i.e is it a simple <code>SELECT</code>? Is it a result of a <code>UNION</code>? Is it a derived table? A derived table dependent on another table? etc.);</li><li><code>table</code> describes the table used for the output;</li><li><code>partitions</code> describes any matching partitions;</li><li><code>type</code> describes the join type;</li><li><code>possible_keys</code> describes the possible indexes that can be used;</li><li><code>key</code> describes the index that is used;</li><li><code>key_len</code> describes the length of the used index;</li><li><code>ref</code> describes the columns or constants that uses an index to select data from a table;</li><li><code>rows</code> describes an approximate amount of rows to be examined;</li><li><code>filtered</code> describes an estimate percentage of rows that will be filtered by a query;</li><li><code>Extra</code> provides additional information.</li></ul>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>The <code>EXPLAIN</code> statement is used with a statement that needs explaining to see the statement execution plan.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Alternatives To <code>DESCRIBE</code></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The <code>DESCRIBE</code> 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 <code>DESC</code> statement (a shorthand of the <code>DESCRIBE</code> statement) and the <code>SHOW COLUMNS</code> statement can be used as well.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To get more information about the table structure we can also add the <code>FULL</code> keyword to the <code>SHOW COLUMNS</code> statement, so the statement might also look like this:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SHOW FULL COLUMNS FROM demo_table;</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The <code>SHOW COLUMNS</code> allows column filtering by using the <code>LIKE</code> operator or <code>WHERE</code> clause too:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SHOW COLUMNS FROM demo_table LIKE ...;</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SHOW COLUMNS FROM demo_table WHERE ...;</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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 <code>WHERE</code> clause to tell the database to find rows matching certain conditions.</p>
<!-- /wp:paragraph -->

<!-- wp:heading -->
<h2>Digging Deeper Into <code>EXPLAIN</code></h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The <code>EXPLAIN</code> is frequently used to obtain a query execution plan, but it's usefulness doesn't end there - <strong>the <code>EXPLAIN</code> statement can also be used to get information on already running queries.</strong> Suppose our database is running the following query (the <code>SLEEP</code> statement is in place to make the query run slower):</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><code>SELECT COUNT(*), SLEEP(10) FROM demo_table WHERE field_1 LIKE '%Demo%';</code></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>We can use the <code>EXPLAIN FOR CONNECTION</code> statement to run <code>EXPLAIN</code> 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.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>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 <code>EXPLAIN FOR CONNECTION</code> statement.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Here's how the results look like:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":345,"sizeSlug":"large"} -->
<figure class="wp-block-image size-large"><img src="https://breachdirectory.com/blog/wp-content/uploads/2020/07/Demo-EXPLAIN-FOR-CONNECTION-Output.png" alt="" class="wp-image-345"/><figcaption><center>Sample <code>EXPLAIN FOR CONNECTION</code> Output</center></figcaption></figure>
<!-- /wp:image -->

<!-- wp:heading -->
<h2>Conclusion</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>The <code>DESCRIBE</code> statement is used to obtain information about table structure. The <code>EXPLAIN</code> statement on the other hand helps us to gain insight into the query execution plans. The <code>DESCRIBE</code> statement can be useful if you're not sure what structure tables you're working with contain and the <code>EXPLAIN</code> statement can be useful when you see that a query is running for a long time and want to figure out why.</p>
<!-- /wp:paragraph -->