maria-developers team mailing list archive

["Berrocal, Eduardo" <eduardo.berrocal@xxxxxxxxx>]

Hello Sergei,

Thanks a lot for your response. The way you showed seems to be the way to go.
There is just one problem: all those functions are protected inside the JOIN_CACHE (and JOIN_CACHE_*)
Classes. Which seems to suggest that code changes to the MariaDB core code is a necessity in this case.

Cheers,


Eduardo Berrocal García de Carellán
Senior Software Engineer


Intel Corporation  |  intel.com


-----Original Message-----
From: Sergey Petrunia <sergey@xxxxxxxxxxx> 
Sent: Wednesday, July 14, 2021 2:26 AM
To: Berrocal, Eduardo <eduardo.berrocal@xxxxxxxxx>
Cc: Sergei Golubchik <serg@xxxxxxxxxxx>; maria-developers@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-developers] Accessing read records during join for condition pushdown

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