Using SHOW EXPLAIN with running queries_MariaDB Cookbook-QQ阅读女频现言网

上QQ阅读APP看书，第一时间看更新

Using SHOW EXPLAIN with running queries

The SHOW EXPLAIN feature was introduced in MariaDB 10.0. It enables us to get an EXPLAIN (that is, a description of the query plan) of the query running in a given thread.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe of this chapter.

How to do it...

Open a terminal window and launch the mysql command-line client and connect to the isfdb database.
```
mysql isfdb
```
Next, we open another terminal window and launch another instance of the mysql command-line client.

Run the following command in the first window:

ALTER TABLE title_relationships DROP KEY titles;

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 ;

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 > 60\G
```
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
```
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;
```
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 recipe 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 titles 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:

Note

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 WARNINGS\G 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.

Note

In the examples of this recipe, 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.