1

I'm looking for the optimal way in terms of speed and performance to add new entries to a database.

I'm pulling a feed which results in an array of aprox. 50.000 (50k) entries.

I have a database which already contains quite a lot of the entries and instead of doing a truncate and adding them all, I want to only add the new entries to the database.

Each of the entries in the array has a unique ID so they are easily selected in the database.

I'm thinking the most "optimal" approach would be to iterate the array, then make a select for each element in the array to see if it's already in the database and if it isn't add it to the database.

Something about that does however seem wrong, as it's alot of queries.

Is there a faster/better way to do it?

dustinos3
  • 888
  • 2
  • 15
  • 25
Yan
  • 11
  • 2

2 Answers2

2

I'd save the 50k entries into a CSV file, and use LOAD DATA LOCAL INFILE 'filename'.

Add the REPLACE option to overwrite any records in the database that match the unique key, or the IGNORE option to avoid overwriting records, ignoring the new data in your CSV file.

Read https://dev.mysql.com/doc/refman/8.0/en/load-data.html

Also see my presentation Load Data Fast! where I measure performance of different methods of inserting bulk data.

Bill Karwin
  • 462,430
  • 80
  • 609
  • 762
0

The most efficient way is INSERT INTO+ tweaks described here

Eriks Klotins
  • 3,546
  • 1
  • 8
  • 23