Indexes slowing Insertions(Can't use Explain Insert to Analyse)

Question

I have a mysql database that 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. I am confused if i should drop the indexes but i don't know how not to cause catastrophes.

Answer 1

INSERT has to update all the indexes. It also has to check all the foreign key constraints, which requires lookups in the other tables' indexes.

The foreign key checks may be what's slowing you down. If you're sure that all the data you're inserting is clean, you can temporarily disable these checks while you're doing your mass insertion:

SET FOREIGN_KEY_CHECKS = 0;
INSERT ...
INSERT ...
...
SET FOREIGN_KEY_CHECKS = 1;

Documentation