The MariaDB subquery cache feature added in MariaDB 5.3 is not widely known. Let’s see what it is and how it works.

What is a subquery cache?

The MariaDB subquery cache optimizes the execution of correlated subqueries. Correlated subqueries refer to a value from the parent query. For example:

SELECT id FROM product WHERE price NOT IN (SELECT MAX(price) FROM product GROUP BY category);

MariaDB only uses this optimization if the parent query is a SELECT, not an UPDATE or a DELETE. The subquery results get cached only for the duration of the parent query.

MariaDB added the subquery cache in v5.3. It is controlled by …

The big picture here is to track events in a flow with some changing state of an event.

That is done in two way

• Storing multiple rows in a flow table using an event id and event column representing different states. 
• Maintain 1 to 1 relation for event specific states in separate tables (flow_event1 to  flow_event5) 

A query generator take various conditions and dynamically build exists subqueries to filter all events that does not match various user conditions.

Using MySQL 5.5 on a 50M rows table the following queries are taking hours.  

SELECT DISTINCT(id) FROM flow main  WHERE date BETWEEN '2013-06-22 10:11:50' AND '2013-06-23 10:11:50' AND event = 'STATE5'  AND  
not exists (SELECT 1    FROM flow_crm sub    WHERE main.id = sub.id     AND …