MariaDB CONNECT Storage Engine vs FEDERATED(X)

As Stewart mentioned in in post where-are-they-now-mysql-storage-engines : “Federated It’s still there… but is effectively unmaintained and dead. There’s even FederatedX in MariaDB which is an improvement, but still, the MySQL server really doesn’t lend itself kindly to this type of engine… it’s always been an oddity only suitable for very specific tasks.”

The CONNECT storage engine is lifting the FEDERATED(X) limitations. It is no more a MySQL table only stuff.
It gives the MySQL access to local or remote MySQL table : in that case you can use a subset of columns, reorder them and have the limit clause correctly applied directly on the target.
But you now also have the capability to do an ODBC access to a data source.
This data source can be a local data source accessed through a DSN : Excel, Access, firebird . in that case the multiple ODBC tables concept apply. The concept of multiple table allows ODBC tables that are physically represented by multiple files. For instance to Excel or Access tables you ca views a set of monthly files as a single table.
This can also be any remote data source for which there is an ODBC driver : ORACLE, SQL Server, DB2 …

What is nice is that these MySQL or ODBC type CONNECT tables can be mixed with the CONNECT TBL Type (Table List) table. This allow to execute queries distributed on multiple remote or local servers.
This is a very powerfull extension of the concept of MERGE table. Table can be of any type. Subset of columns can be used.

4 comments to MariaDB CONNECT Storage Engine vs FEDERATED(X)

  • So CONNECT is going to make SpiderSE obsolete?

    • Serge

      definitely not.
      Spider is based on the concept of distributed partitionning.
      The CONNECT Storage Engine cover a variety of use cases : ODBC, XML …
      The TBL table type that defines a table based on a set of tables that can be distributed is closed to SPIDER.
      But it does not use partitioning.

  • Might be better to answer this in another blog post.
    1) Does this use index predicates when accessing the remote data source?
    2) Does this use non-index predicates when accessing the remote data source?
    3) Does this use BKA to reduce round trips to the remote data source?

    In the past, Federated would use index predicates but not non-index predicates when accessing the remote data source. Changes in the past few years (maybe BKA) made it possible to also provide non-index predicates to the remote data source. That might allow it to generate a more efficient query plan.

    I don’t think Federated had support for BKA.

    So if I understand what this product might be doing and if it adds support for #2 and #3 then this can be interesting.

    • Serge

      Hi Mark,

      yes for the to first points as the CONNECT storage engine tries to push as many possible filtering condition. This greatly help for ODBC and MySQL table type that can be remote.

      The CONNECT storage engine does not yet implements the Batched Key Access. We are looking at it as it would greatly improve join with a remote table type (ODBC or MYSQL)

      CONNECT also allow to define a table as a list of tables that can be remote.
      We are currently looking at introducing parallelism in the execution for this type of table.

      The launchpad repository with the ongoing dev is there.
      The main developer is Olivier Bertrand with the Help of Sergei Golubchik and Alexander Barkov.

      doc is there :

Leave a Reply




8 + two =

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">