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: | |
| 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 | ||
[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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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