maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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