Allowing storage engine to shortcut group by queries

Description

This task is to allow storage engines that can execute GROUP BY
queries efficiently to intercept a full query or sub query from
MariaDB and deliver the result either to the client or to a temporary
table for further processing.

The interface is using a new 'group_by_handler' class. The new class
is needed as the original query may contain multiple tables and the
'result row' can contain fields from different tables.

Overview

During prepare, call the storage engine handlerton to ask if the storage engine can execute the group by query.
If yes:

  • The handlerton returns a group_by_handler object.

  • Create a temporary table to store result rows.

  • Initialize the group_by_handler with the temporary table and other relevant objects

  • When doing 'optimize', don't optimize join order (not needed)


When do_select() is called, if we have a group_by_handler object, the following is done, instead of the normal procedure of reading things rows by row and joining tables:

  • Initialize group_by_handler

  • While get_next_row(), returns false:

    • Depending on context, write temporary_table->record[0] to the temporary table or return it to the next level (normally the end user).

  • finish group_by_handler

Note that the above loop can be executed many times, in case of
prepared statements or sub queries.

  • When cleanup up SELECT_LEX, we will free the group_by_handler object.

More details

Assumptions when this interface is used:

  • The SELECT is a GROUP BY or summary query.

  • All tables used in SELECT comes from the same storage engine.

Suggested interface

New function for the handlerton class:

This function should return a group_by_handler object if the storage engine can resolve the query itself.

New group_by_handler class with the following data and virtual methods:

If the group_by_handler can't do the sorting, MariaDB will do this. Note that we assume that the handler can filter out things not matching HAVING (by calling having->val_bool()).

In the future we will look at doing a more abstract interface so that the storage engine doesn't have to understand the SELECT_LEX and other structures.

Assignee

Sergei Golubchik

Reporter

Michael Widenius

Labels

None

Time tracking

16h

Components

Sprint

None

Fix versions

Priority

Blocker
Configure