The She-BAPerformance Question #1
I promised to write this up for for the folks who attended the Boston MySQL September User Group meeting, so here's performance question #1 that was asked:
How can a bulk insert be speeded up?
We discussed disabling keys with
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
However, as a post by Frank Mash not too long ago and comments explain, this has no effect on InnoDB tables.
For InnoDB tables, you can load the data in primary key order, which makes the loading much faster. Basically, InnoDB stores the data in primary key order on disk. If there is no primary key specified, the internal engine makes one anyway, so you might as well specify one and take advantage of it.
As well, you can SET UNIQUE CHECKS=0 before the load and SET UNIQUE CHECK=1 after the load if there are unique constraints. The final suggestion is to SET AUTOCOMMIT=0 before the load and SET AUTOCOMMIT=1 after the load, again to speed things up.
Hi Buddy, This is a small
Hi Buddy,
This is a small but great article.
Thanks for sharing it with us.
Diptanjan
HI, Thanks for the info..it
HI,
Thanks for the info..it really of great help for me..
Anmol
Couple of comments. 1) It
Couple of comments.
1) It would be right to say ALTER TABLE DISABLE KEYS/ENABLE KEYS Only works for MyISAM Tables. It does not work for HEAP, Innodb, BDB etc. I know Innodb and MyISAM is only two storage engines people care at this point but this can change in the future.
2) This only helps for really bulk inserts. Plus disabling keys means your table can't be really used in queries any more. If table is available you can use shadow table technique, if you're loading proportionally few rows (ie 1 million in 100.000.000 rows table) using large bulk_insert_tree may be better solution even for MyISAM table.
3) Assuming it is fastest to load data in single transaction is wrong. Plus if you would have the crash during long running transaction you may be in trouble. It is best to avoid long running transactions and commit at reasonable intervals - ie every 10.000 rows
You can also turn off
You can also turn off foreign key checks with SET FOREIGN_KEY_CHECKS = 0 . That removes significant overhead from tables that have foreign keys -- foreign keys require lookups on every row.