Slow MYSQL Insert to DB MyISAM

Question

I have a file (insert.sql) which has 250k rows like these with no key, no index :

INSERT `project_383`.`entity_metrics_build_1` VALUES ('d402afeb4630267f383b99875f37162d', 'ClMaxCycl', '-1');

INSERT `project_383`.`entity_metrics_build_1` VALUES ('d402afeb4630267f383b99875f37162d', 'ClLMethodsCalled', '0');

I input it to the my MyISAM table using mysql -u root -p project < insert.sql, the total time is 5 minutes.

I see in another thread, people say they can insert millions of rows under 1 seconds. I really don't understand. Can somebody explain for me why my SQL is so slow ?

My Server is 16gb Cpu xeon.

I would expect this is to do with MyISAM's table locking when inserting... but I can't be sure from looking at your queries they look pretty small.. — ɴᴀᴛᴇ ᴅᴇᴠ
– ɴᴀᴛᴇ ᴅᴇᴠ, Commented May 24, 2013 at 7:22

Barmar · Accepted Answer · 2013-05-24 07:23:07Z

1

Combine them into a single INSERT with multiple VALUES clauses:

INSERT `project_383`.`entity_metrics_build_1`
VALUES ('d402afeb4630267f383b99875f37162d', 'ClMaxCycl', '-1'),
       ('d402afeb4630267f383b99875f37162d', 'ClLMethodsCalled', '0'),
       ...;

If you look at the file created by mysqldump, this is how it does it.

answered May 24, 2013 at 7:23

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

eggyal Over a year ago

Agreed. Or create a delimited data file and use LOAD DATA / mysqlimport.

Collectives™ on Stack Overflow

Slow MYSQL Insert to DB MyISAM

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related