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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
