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;
drop procedure if exists create_entities!
create procedure create_entities()
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
end loop create_entities_for_club;
alter table clubs change column finance_entity_id finance_entity_id int unsigned not null;
alter table clubs add unique (finance_entity_id);
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.