(For more resources related to this topic, see here.)
Import the ISFDB database which is available under Creative Commons licensing.
mysql isfdb
ALTER TABLE title_relationships DROP KEY titles;
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 ;
SELECT INFO, TIME, ID, QUERY_ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 60G
SHOW EXPLAIN FOR id
KILL QUERY ID query_id;
ALTER TABLE title_relationships ADD KEY titles (title_id);
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).
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.
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.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…