Olavi Sau Olavi Sau -4 years ago 59
SQL Question

What is the fastest way to insert a new has relation to an existing entry?

I need to insert a finance_entity for each club in a one to one relationship.
I decided to run this in the mysql server, because single inserts are even slower. How could I optimize this to run faster? Is there some way to hack insert select to do this?

I cannot put the club_id on finance_entity, since more then one relation points to it.

alter table clubs add column finance_entity_id int unsigned null after id;
DELIMITER !
drop procedure if exists create_entities!
create procedure create_entities()
begin
create_entities_for_club: loop
set @club_key = (select id from clubs where finance_entity_id is null limit 1);

insert into finance_entity (id) value (null);
update clubs set finance_entity_id = last_insert_id() where id = @club_key;


if @club_key is null then
leave create_entities_for_club;
end if;
end loop create_entities_for_club;
end!
call create_entities_for_club()!

DELIMITER ;

alter table clubs change column finance_entity_id finance_entity_id int unsigned not null;
alter table clubs add unique (finance_entity_id);

Answer Source

Setting a temporary key seems to be the fastest approach.

In addition to this, it's essential for there to be enough size in the buffer, turns out my testing machine only had 16M of buffer size.

Add these to your my.ini or my.cnf

innodb_buffer_pool_size=1024M
innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 256M
innodb_log_buffer_size = 512M

Use this to insert to the table, adding and index to club_id, might make it even faster.

alter table finance_entity add column club_id int unsigned null;
insert into finance_entity (club_id) select id from clubs;

update clubs join finance_entity on clubs.id = finance_entity.club_id 
set finance_entity_id = finance_entity.id;

alter table finance_entity drop column club_id;

This ran in 1.6 seconds for 20 thousand entries.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download