Using SHOW EXPLAIN with running queries

Exclusive offer: get 50% off this eBook here
MariaDB Cookbook

MariaDB Cookbook — Save 50%

Over 95 recipes to unlock the power of MariaDB with this book and ebook

$29.99    $15.00
by Daniel Bartholomew | March 2014 | Cookbooks Open Source

In this article by Daniel Bartholomew, the author of the book MariaDB Cookbook, has discussed about the SHOW EXPLAIN feature of MariaDB. 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.

(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 > 60\G

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

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

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:


MariaDB Cookbook Over 95 recipes to unlock the power of MariaDB with this book and ebook
Published: March 2014
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Daniel Bartholomew

Daniel Bartholomew has been using Linux since 1997 and databases since 1998. He is a frequent contributor to various magazines, including The Linux Journal, Linux Pro, Ubuntu, User, and Tux.

He has been involved with the MariaDB project since shortly after it began in early 2009. He currently works for SkySQL and splits his time between MariaDB documentation and maintaining the bits and pieces (including build, e-mail, web, and other servers), which keeps the MariaDB project running smoothly. In addition to his day-to-day responsibilities, he also serves as the MariaDB release coordinator and has been deeply involved with almost every MariaDB release.

He lives in Raleigh, North Carolina, U.S.A. with his lovely wife and awesome children.

Books From Packt


Getting Started with MariaDB
Getting Started with MariaDB

Mastering phpMyAdmin 3.4 for Effective MySQL Management
Mastering phpMyAdmin 3.4 for Effective MySQL Management

Learning Cassandra for Administrators
Learning Cassandra for Administrators

Mastering Apache Cassandra
Mastering Apache Cassandra

Getting Started with NoSQL
Getting Started with NoSQL

MySQL Management and Administration with Navicat
MySQL Management and Administration with Navicat

Microsoft SQL Server 2012 with Hadoop
Microsoft SQL Server 2012 with Hadoop

Creating your MySQL Database: Practical Design Tips and Techniques
Creating your MySQL Database: Practical Design Tips and Techniques


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software