MySQL – How to Speed Up Inserting 60,000 Rows

insertMySQL

I'm trying to insert about 60 000 rows on my database on localhost (windows) it is now taking about an hour and still inserting. And after a while it says that maximum timeout is over.
Shouldn't it be faster? I'm using InnoDB

INSERT INTO ip2nation (ip, country) VALUES
 (687865856, 'za'),
 (689963008, 'eg'),
 (691011584, 'za'),
 (691617792, 'zw'),
 (691621888, 'lr'),
 (691625984, 'ke'),
 (691630080, 'za'),
 (691631104, 'gh'),
 (691632128, 'ng'),
 (691633152, 'zw'),
 (691634176, 'za'),
 (691650560, 'gh'),
...

Best Answer

In general, this will take a time, anyway this time can be reduced by some ways, for example you can lock the table and unlock it when to finish..

The time required for inserting a row is determined by the following factors, where the numbers indicate approximate proportions:

  • Connecting: (3)
  • Sending query to server: (2)
  • Parsing query: (2)
  • Inserting row: (1 × size of row)
  • Inserting indexes: (1 × number of indexes)
  • Closing: (1)

Please read full article form the following Link by MySql dev Team: https://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Related Question