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.
Import the ISFDB database as described in the Importing the data exported by mysqldump recipe in Chapter 2, Diving Deep into MariaDB.
Restart MariaDB to clear the subquery cache.
Launch the
mysqlcommand-line client application and connect to theisfdbdatabase on our MariaDB server.Run the following command to show our usage of the subquery cache:
SHOW STATUS LIKE 'subquery%';Because we just restarted MariaDB and cleared the subquery cache, the output will look like the following screenshot:
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...