what is the best way to insert a lot of data to mysql database?
1 Answer
You are not just doing an insert, but also using on duplicate key update.
I would suggest that you take a bulk-load approach:
- Put the 18-25,000 things into a file, appropriately delimited.
- Load the data from the file using
load data infileinto a staging table. - Use the
insert . . . on duplicate key updateon the staging table.
The final step looks similar to your query:
INSERT INTO players (name, online, world, level, vocation, month, week, today)
SELECT name, online, world, level, vocation, time, time, time
FROM players_staging
ON DUPLICATE KEY UPDATE
online = values(online), world = values(world), level = values(level),
vocation = values(vocation), month = month + values(time),
week = week + values(time), today = today + values(time);
Bulk operations are faster for two reasons. First, they requires fewer calls into the databases -- setting up queries, returning results, and other overhead going back and forth. Second, they reduce the amount of logging and locking required for the operations.
3 Comments
Tibia Rook
I have no idea how to do that type of stuff. how do i output it to a file? is it some special format? like xml? or what? i am a newbie at this, so bare in mind.
Tibia Rook
still cannot figure this out, @gordon linoff
Rick James
@TibiaRook - research the term "CSV" or "Comma Separated Values" or "Tab Separated Values". Then look for
LOAD DATA INFILE in MySQL. Put those two together to get high speed ingestion. Oh, also, you need to know how to create and write a file from C#.
BULK INSERT.appendat the end and close it off with semi-colons? If so, then I had a lot of trouble with it. Because I couldn't insert my variables like @level, they are assigned later on at the bottom as you can see in my code. So I dont know how to do this. I find it extremely difficult with these sql queries. I sat with stirngbuild until 5am this morning, no kidding. And I just went to bed cus I was so dead tired about it. it's driving me insaneINSERT; that does run faster (like 10x).INSERT ...; INSERT ...;is essentially no faster thanINSERT ...;, thenINSERT ...;. This is faster:INSERT INTO t (a,b) VALUES (1,2), (5,6), ...;