We noticed an issue with the partition pruning on Maria DB with Toku DB engine. Partition pruning doesn’t seem to work as expected for an inner query, this is illustrated with a simple example below.
Let us consider two tables employee and dept with company_id being the column on which partition is defined. When we try to fetch the list of departments for a given company_id in an inner query then all the partitions are being accessed instead of narrowing down to the partition specified in the criteria. When we run the same query as an outer query it is working fine, it is working fine on MySQL 5.6.16. Please let us know if this is a defect.
DDL and DML statements are attached in "Partition Pruning issue data setup.sql". This file also has an explain plan for a simple select statement. Please note that for the inner query all 3 partitions are being listed. Explain plan is attached in "Explain Plan for the query.csv". We are running into this issue with our complex queries, the one listed here is just for explaining the problem.
This issue is causing a huge performance problems for our system and right now it is a blocker for us to pursue MaraDB further.
Thanks for your time, please let us know if you need any more details.
Updated the ticket with the "Explain Plan for query in MySQL5.6.16.csv". Please note that in MySQL inner query access only the relevant partition.