Hi Eduardo, On Mon, Jul 12, 2021 at 11:30:35PM +0000, Berrocal, Eduardo wrote:
"A question: do you intend to push down and check conditions in arbitrary form? In case they are just equalities, it might make sense for storage engine to pretend having an index and then Batched Key Access optimization will be used, and the SQL layer will provide batches of keys to lookup."
The answer is yes. I would like to check more than just equalities, if possible. The Batched key access is something worth looking into, in case that that can be used as a way to speed up at least conditions with equalities.
A follow up question for you: When you say that traversing the linked join buffers is difficult... how difficult exactly? Like a linked list, a tree... ? I still think it is worth exploring in our case given that the speedup can be huge if we can avoid a full scan of a table with TBs of data.
Iterating the join buffer shouldn't be hard. It is just a memory area which is filled with variable-size records which have certain fields. Debugging a query that uses join buffers, one can see that the iteration over contents of the join buffer is done in JOIN_CACHE::join_matching_records() using these calls: prepare_look_for_matches(); while (get_next_candidate_for_match()) { read_next_candidate_for_match(); } The code in JOIN_CACHE::join_matching_records() is complex, because it does other things besides just iterating the cache: * Checking of "first match" flag for queries which only need one matching row from the second table. * Handling outer JOINs, where we apply the restriction from ON expression always, and apply the restriction from WHERE expression only after we've figured out if LEFT JOIN has a match. AFAIU, you should ignore these inside the storage engine (produce all matches, don't check the Item_func_trig_cond items). This will make your copy of join_matching_records simpler. BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net