Optimizing queries with the subquery cache

The subquery cache is one of the several methods utilized by MariaDB to improve the performance of statements with subqueries. This is a feature unique to MariaDB and makes subqueries in MariaDB much faster than competing databases.

Getting ready

Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in Chapter 2, Diving Deep into MariaDB.

How to do it...

1. Restart MariaDB to clear the subquery cache.

2. Launch the mysql command-line client application and connect to the isfdb database on our MariaDB server.

3. Run the following command to show our usage of the subquery cache:

   SHOW STATUS LIKE 'subquery%';
   

4. Because we just restarted MariaDB and cleared the subquery cache, the output will look like the following screenshot:

   

5. Run the following 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...