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.. — naththedeveloper, May 24 '13 at 07:22

score 1 · Accepted Answer · answered May 24 '13 at 07:23

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 '13 at 07:23

Barmar

741,623
53
500
612

Agreed. Or create a delimited data file and use `LOAD DATA` / mysqlimport. – eggyal May 24 '13 at 07:23

Slow MYSQL Insert to DB MyISAM

1 Answers1