MYSQL bulk INSERT slow

Question

I have a MYSQL INSERT that is very very long. MYSQL freezes for some minutes and after unfreeze no data has been inserted.

Is there something wrong with my INSERT query?

INSERT INTO polim_inventory_transactions (item, type, warehouse, quantity, date) VALUES (100004, 'Inventory Tr', 'BFL', 354.0000, '2013-01-31'), (100007, 'Purchase Rec', 'BFL', 23552.0000, '2013-01-15'), (100206, 'Inventory Tr', 'BFL', 125.0000, '2013-01-08'), (100206, 'Inventory Tr', 'BFL', 75.0000, '2013-01-09'), (100206, 'Inventory Tr', 'BFL', 100.0000, '2013-01-09'), ....

do you have many index added on the table? because too much indexes could slow INSERT, UPDATE and DELETE statement. — John Woo
– John Woo, Commented Feb 14, 2013 at 9:48

vadchen · Accepted Answer · 2013-02-14 10:01:08Z

1

If this is MyISAM table than you can try locking it before insert. Also, if you have keys on this table, disabling them can be helpful too:

ALTER TABLE polim_inventory_transactions DISABLE KEYS;

In general, you can see more tips here: Bulk Data Loading for MyISAM Tables

answered Feb 14, 2013 at 10:01

vadchen

1,4621 gold badge11 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mend Over a year ago

Thank you! Disabling keys did the trick. Now the query executes under 3 seconds.

vadchen Over a year ago

Note: you need to enable keys after INSERT is finished. This is the stage that takes time if you have many/large keys.

Collectives™ on Stack Overflow

MYSQL bulk INSERT slow

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related