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

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

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.