Bug #24444 Please add FEDERATED query feature
Submitted: 20 Nov 2006 18:36 Modified: 5 Nov 2009 12:51
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S4 (Feature request)
Version:NA OS:Any (NA)
Assigned to: CPU Architecture:Any
Tags: federated, query, VIEW

[20 Nov 2006 18:36] Roland Bouman 
Description:
The FEDERATED storage engine could be made much more useful if it would allow a mapping to a (remote) query rather than a specific base table. Effectively this would be a federated view rather than a federated table.

There are numerous purposes, but monitoring, data warehousing and auditing purposes come to mind. 

If an extra option would be added that allows the query to be executed fully on the remote host, performance could be imroved too for some scenarios. Instead of joining multiple federated tables, a join can executed entirely on the remote host, sending only the result rows.  

How to repeat:
CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@remote_host:9306//
select     t1.id,t2.name,t2.other 
from       remote_db1.remote_table1 t1
inner join remote_db2.remote_table1 t2
on         using(join_col)
';

Suggested fix:
implement described functionality
[20 Nov 2006 23:22] Sergey Petrunya 
Timour had an idea of achieving a similar effect by doing "the opposite": let the optimizer at frontend detect a case when we have a join of several remote tables from the same backend, and push the join operation down to that backend.

This "opposite" way seems to be more generic/powerful, but on the other hand it may be harder to implement. There are no specs for either variant yet, so it's hard to tell whether implementation of Timour's idea will provide everything that Roland requests (or vice versa).

In any case, whoever picks up this bug should check with Timour.
[21 Nov 2006 7:07] Roland Bouman 
Thanks sergey!

I think the pushdown according to Timour's plan is wonderful too. In some ways it might be more flexible. However, my proposal is different, and also flexible but in other wasys. 

My initial requirement is actually very simple and straight forward, and you will probably notice immeditately why you cannot solve it using the proposed approach by Timour:

create table mysql_remote_version(
    version varchar(12) not null
)
engine=federated
connection='mysql://user@host//select version()'

or even

create table mysql_remote_proc(
    id int
)
engine=federated
connection='mysql://user@host//call procname()'

(this second one is just an idea)