0

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'),
...

2 Answers 2

3

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

0

Check if you have some restrictive constraints or indexes. Imagine, if you have and index, you've to insert and update the index. If you have a constraint, you've to check if your insert violates the constraint and then insert.

You can disable indexes and constraints, insert the rows, and once inserted, enable indexes and constraints.

Regards, Marco.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.