The code behind pushdown select performs the following operations during Pushdown_select::init():

  handler->table= create_tmp_table(thd, &tmp_table_param, types,

                                   (ORDER *) 0, false, 0,

                                   TMP_TABLE_ALL_COLUMNS, 1,

                                   &empty_clex_str, true, false);

  if (!handler->table)

    DBUG_RETURN(true);

  if (handler->table->fill_item_list(&result_columns))

    DBUG_RETURN(true);

That needs a lot of allocation and initialization:

• TABLE - 976 bytes
• TABLE_SHARE - 1424 bytes
• An array of Fields inside TABLE - at least 208 bytes each Field
• An array of Item_field, one Item for every Field - at least 320 bytes each

In fact we don't need so many structures to perform a pushed select.

Instead, we could allocate an array of literal Items, e.g. Item_int, Item_real, Item_string, Item_time_literal, etc, depending on the data type.

A proof of concept patch

The attached patch demonstrates this approach for a SELECT consisting of a single column, for the FederatedX storage engine, assuming a column of an integer data type.
Note, the patch was tested only for the case when the results are sent to the client (e.g. not to another table in INSERT..SELECT or anywhere else, this needs some more coding).

Testing the prototype

Remote database DDL:

CREATE OR REPLACE TABLE t1fedm (a INT);

INSERT INTO t1fedm VALUES (1),(2),(3);

Local database DDL:

CREATE OR REPLACE TABLE t1fedm (

  a INT DEFAULT NULL

) ENGINE=FEDERATED CONNECTION='mysql://root:@192.168.0.10:3307/test/t1fedm';

Local database query:

SET global federated_pushdown=0;

SELECT * FROM t1fedm;

SET global federated_pushdown=1;

SELECT * FROM t1fedm;

MariaDB [test]> set global federated_pushdown=0;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT a FROM t1fedm;

+------+

| a    |

+------+

|    1 |

|    2 |

|    3 |

| NULL |

| NULL |

+------+

5 rows in set (0.006 sec)

MariaDB [test]> set global federated_pushdown=1;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT a FROM t1fedm;

+------+

| hack |  <-- Notice the Item_int created in the experimental Pushdown_select::init() code

+------+

|    1 |

|    2 |

|    3 |

| NULL |

| NULL |

+------+

5 rows in set (0.003 sec)

Notice, the new concept prototype code returns a correct result set, the same to the non-pushed query.

Further improvement directions

As the next step, we could also get rid of literal Items and replace them to:

• an array of generic value containers (either in ValueBuffer or in NativeBuffer), or just an iterator, so we need just one value container
• new virtual methods in Type_handler to populate a value container
• new virtual methods in Type_handler to send a value containers to the result sink

Benefits:

• This array can be created during the first pushed SELECT and later reused by consequent pushed SELECTs.
• Protocol::send_result_set_row() already uses ValueBuffer, so this code can be shared with pushed SELECTs.
• TODO: think what other benefits (over the array of literal Items) we can gain with this approach