2

I need to update about 100 000 records in MySQL table (with indexes) so this process can take long time. i'm searching solution which will work faster.

I have three solutions but i have no time for speed tests.

Solutions:

  1. usual UPDATE with each new record in array loop (bad perfomance)

  2. using UPDATE syntax like here Update multiple rows with one query? - can't find any perfomance result

  3. using LOAD DATA INFILE with the same value for key field, i guess in this case it will call UPDATE instead UNSERT - i guess should work faster when ever

Do you know which is solution is best. The one important criteria is execution speed.

Thanks.

Community
  • 1
  • 1
user1016265
  • 2,307
  • 3
  • 32
  • 49

1 Answers1

0
  • LOAD DATA INFILE the fastest way to upsert large amount of data from file;

  • second solution is not so bad as you might think. Especially if you can execute something like

    update table set field = values where id in (list_of_ids)

  • but it would be better to post your update query.

ravnur
  • 2,772
  • 19
  • 28