(For more resources related to this topic, see here.)

Getting ready

Import the ISFDB database which is available under Creative Commons licensing.

How to do it…

1. Open a terminal window and launch the mysql command-line client and connect to the isfdb database using the following statement.
   

   mysql isfdb

2. Next, we open another terminal window and launch another instance of the mysql command-line client.
3. Run the following command in the first window:

   ALTER TABLE title_relationships DROP KEY titles;

4. Next, in the first window, start the following example query:

   SELECT titles.title_id AS ID, 
   titles.title_title AS Title, 
   authors.author_legalname AS Name, 
   (SELECT COUNT(DISTINCT title_relationships.review_id) 
   FROM title_relationships
   WHERE title_relationships.title_id = titles.title_id)
   AS reviews 
   FROM titles,authors,canonical_author 
   WHERE 
   (SELECT COUNT(DISTINCT title_relationships.review_id) 
   FROM title_relationships 
   WHERE title_relationships.title_id = titles.title_id)>=10 
   AND canonical_author.author_id = authors.author_id 
   AND canonical_author.title_id=titles.title_id 
   AND titles.title_parent=0 ;

5. Wait for at least a minute and then run the following query to look for the details of the query that we executed in step 4 and QUERY_ID for that query:

   SELECT INFO, TIME, ID, QUERY_ID
   FROM INFORMATION_SCHEMA.PROCESSLIST
   WHERE TIME > 60G

6. Run SHOW EXPLAIN in the second window (replace id in the following command line with the numeric ID that we discovered in step 5):

   SHOW EXPLAIN FOR id

7. Run the following command in the second window to kill the query running in the first window (replace query_id in the following command line with the numeric QUERY_ID number that we discovered in step 5):

   KILL QUERY ID query_id;

8. In the first window, reverse the change we made in step 3 using the following command:

   ALTER TABLE title_relationships ADD KEY titles (title_id);

How it works…

The SHOW EXPLAIN statement allows us to obtain information about how MariaDB executes a long-running statement. This is very useful for identifying bottlenecks in our database.

The query in this article will execute efficiently only if it touches the indexes in our data. So, for demonstration purposes, we will first sabotage the title_relationships table by removing the title’s index. This causes our query to unnecessarily iterate through hundreds of thousands of rows and generally take far too long to complete. The output of steps 3 and 4 will look similar to the following screenshot:

While our sabotaged query is running, and after waiting for at least a minute, we switch to another window and look for all queries that have been running for longer than 60 seconds. Our sabotaged query will likely be the only one in the output. From this output, we get ID and QUERY_ID. The output of the command will look like the following with the ID and QUERY_ID as the last two items:

Next, we use the ID number to execute SHOW EXPLAIN for our query. Incidentally, our query looks up all titles in the database that have 10 or more reviews and displays the title, author, and the number of reviews that the title has. The EXPLAIN for our query will look similar to the following screenshot:

An easy-to-read version of this EXPLAIN is available at https://mariadb.org/ea/8v65g.

Looking at rows 4 and 5 of EXPLAIN, it’s easy to see why our query runs for so long. These two rows are dependent subqueries of the primary query (the first row). In the first query, we see that 117044 rows will be searched, and then, for the two dependent subqueries, MariaDB searches through 83389 additional rows, twice. Ouch.

If we were analyzing a slow query in the real world at this point, we would fix the query to not have such an inefficient subquery, or we would add a KEY to our table to make the subquery efficient. If we’re part of a larger development team, we could send the output of SHOW EXPLAIN and the query to the appropriate people to easily and accurately show them what the problem is with the query. In our case, we know exactly what to do; we will add back the KEY that we removed earlier.

For fun, after adding back the KEY, we could rerun the query and the SHOW EXPLAIN command to see the difference that having the KEY in place makes. We’ll have to be quick though, as with the KEY there, the query will only take a few seconds to complete (depending on the speed of our computer).

There’s more…

The output of SHOW EXPLAIN is always accompanied by a warning. The purpose of this warning is to show us the command that is being run. After running SHOW EXPLAIN on a process ID, we simply issue SHOW WARNINGSG and we will see what SQL statement the process ID is running:

This is useful for very long-running commands that after their start, takes a long time to execute, and then returns back at a time where we might not remember the command we started.

In the examples of this article, we’re using “G” as the delimiter instead of the more common “;” so that the data fits the page better. We can use either one.

See also

• The full documentation of the KILL QUERY ID command can be found at https://mariadb.com/kb/en/data-manipulation-kill-connectionquery/
• The full documentation of the SHOW EXPLAIN command can be found at https://mariadb.com/kb/en/show-explain/

Summary

In this article, we saw the functionality of the SHOW EXPLAIN feature after altering the database using various queries. Further information regarding the SHOW EXPLAIN command can be found in the official documents provided in the preceding section.

Resources for Article:

---

Further resources on this subject:

• Installing MariaDB on Windows and Mac OS X [Article]
• A Look Inside a MySQL Daemon Plugin [Article]
• Visual MySQL Database Design in MySQL Workbench [Article]

---