Indexes slowing Insertions(Can't use Explain Insert to Analyse) I have a MySQL database,  I am parsing files from a log and inputting it inside. 

So far including a simple 4MB file in the database could take me about 4 minutes to insert ! and i have about 10,000 of such files to process. 

At first i thought it's my script but after further digging i learned that this could be related to indexes.

I have a table with 4 columns, 1 primary key and 2 foreign keys which are by default indexes.

From this is it possible to know if the indexes affect insertion performances ? I can't seem to use Explain Insert with MySQL so i don't know of any more information to provide.

CREATE TABLE `as_path` (
  `AS_Path_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Path_Index` int(11) DEFAULT NULL,
  `AS_No` int(11) NOT NULL,
  `Update_ID` int(11) NOT NULL,
  PRIMARY KEY (`AS_Path_ID`,`Update_ID`,`AS_No`),
  KEY `AS_No_fk_idx` (`AS_No`),
  KEY `Update_ID_fk_as_path_idx` (`Update_ID`),
  CONSTRAINT `AS_No_fk` FOREIGN KEY (`AS_No`) REFERENCES `as_detail` (`AS_No`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path` FOREIGN KEY (`Update_ID`) REFERENCES `update_detail` (`Update_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1074256 DEFAULT CHARSET=utf8;
A file of 4 MB should not take 4 minutes to insert.I see your table have InnoDB engine.

I hope you first need to optimize basic InnoDB settings for that please have a look at Innodb Performance Optimization Basics 

Now How to speed up InnoDB Inserts

There's an entire document  dedicated to bulk loading data into InnoDB. The main points:


disable autocommit to avoid an extra log flush for each insert statement: SET autocommit=0;...sql import;COMMIT;
disable foreign and unique checks (you can't disable all indexes completely):
SET unique_checks=0;
SET foreign_key_checks=0;


Setting innodb_flush_log_at_trx_commit = 0 (or 2) may also speedup inserts. Please note, ACID rules would love that value to be at 1.