The set statistics subquerycache on command displays the number of cache hits and misses and the number of rows in the cache for each subquery. The following example shows subquery cache statistics:
set statistics subquerycache on
select type, title_id
from titles
where price > all
(select price
from titles
where advance < 15000)
Statement: 1 Subquery: 1 cache size: 75 hits: 4925 misses: 75
If the statement includes subqueries on either side of a union, the subqueries are numbered sequentially through both sides of the union.
