Importing a LOT of data (With regular INSERTs)

vitalyb

I backupped my table and got something like this:

INSERT INTO `im_items` VALUES (33197, '1585671363');
INSERT INTO `im_items` VALUES (33197, '1585671363');
INSERT INTO `im_items` VALUES (33197, '1585671363');
INSERT INTO `im_items` VALUES (33197, '1585671363');
INSERT INTO `im_items` VALUES (33197, '1585671363');
INSERT INTO `im_items` VALUES (33197, '1585671363');
...

The list goes on for about 200,000 rows.

When I tried to execute this script in EMS MySQL manager it made it in a tempo of about 1000 rows per minute (not exact).

Since this is awfully slow, I changed it to:

INSERT INTO `im_items` VALUES (33197, '1585671363')
, (33198, '1569713308')
, (33199, '0531071170')
, (33200, '0553714651')
, (33201, '0345437896')
, (33202, '0415926092')
, (33203, '0892965088')
...

That boosted the execution quite a lot, but still, the actual handling was sluggish. The whole IDE pretty much froze when I tried to paste so much SQL into it. I fear the day when I have to recover more than million rows...

So, I was wondering, is there a better way to do it? Something that won't stuck when I process so much data? Any suggestions will be much appreciated.

Thanks.

kdevos

I had the same problem when I had to move some large mysql-databases some time ago. I didn't have access to the shell or the database files and phpmyadmin kept returning time-outs (one of the tables had 760.000 rows), so I had to do it another way.

I decided to upload the table-dump, along with a script that inserted the values one by one by reading each line of the file, and running it one by one.

I simply had to extend the maximum runtime of script execution with ini_set to allow the script to run longer than 30 seconds and everything worked (not very fast, but at least all the values got inserted).

Maybe a amateuristic way of doing it, but hey, it worked! (since nothing else did...).