Bug #25194 please implement bulk inserts for federated table
Submitted: 20 Dec 2006 3:55 Modified: 5 Aug 2010 13:15
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S4 (Feature request)
Version:5.1, 5.5 OS:Any (*)
Assigned to: CPU Architecture:Any
Tags: bulk insert, federated table
Triage: Needs Triage: D4 (Minor)

[20 Dec 2006 3:55] Shane Bester
Description:
When doing bulk inserts into a federated table, performance suffers
because a query like;

INSERT INTO federated_table SELECT * FROM myisam_table;

is converted into

INSERT INTO `sample_table` (id, name, other) VALUES  ('1', 'test', '3')
INSERT INTO `sample_table` (id, name, other) VALUES  ('2', 'test1', '4')
INSERT INTO `sample_table` (id, name, other) VALUES  ('4', 'test5', '6')

and sent to the remote server one insert per row.

How to repeat:
on the remote host:
---------------------
CREATE TABLE myisam_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=MyISAM
DEFAULT CHARSET=latin1

on the local host:
------------------
CREATE TABLE myisam_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=MyISAM
DEFAULT CHARSET=latin1

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@192.168.250.3/test/sample_table';

insert into myisam_table values (1,'test',3);
insert into myisam_table values (2,'test1',4);
insert into myisam_table values (4,'test5',6);
insert into federated_table select * from myisam_table;

Suggested fix:
It looks intended behaviour, so this really is a feature request.
I guess federated engine could build a multirow insert to
help speed up the query, since network latency is obviously an issue.

In the mean time, it should be documented in the limitations:

http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html
[3 Jan 2007 11:58] MC Brown
I've added the following to the list of limits for Federated tables: 

Performance on a <literal>FEDERATED</literal> table when
  performing bulk inserts (for example, on a <literal>INSERT INTO ... SELECT
  ...</literal> statement) is slower than with other table types because each
  selected row is treated as an individual <literal>INSERT</literal> statement
  on the federated table.