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
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