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

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

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