4

I have two huge tables. I want to update all fields in the second table with the first if productid and userid matches or insert new if not exist. Will a JOIN be better than a simple UPDATE? What about REPLACE INTO? Can someone help me with the best optimal decision? because the tables are huge.

Thanks.

karto
  • 3,538
  • 8
  • 43
  • 68
  • lots of data records (about 100billion) and many users performing the same function at a time. – karto Apr 10 '12 at 16:59
  • 2
    Hey, This may not be the best solution, but i would use 2 queries for this, the first to update all fields if exist, and then the second to insert where not exist. – Joshua Kissoon Apr 10 '12 at 17:27
  • 4
    http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables – dqhendricks Apr 10 '12 at 17:40
  • 3
    I think a big question here is wether you can stop the db while doing the update or not. Since you may also use php it may be an idea to use a limit clause in the sql and let php do a number of records at a time to avoid total lock on the db until it is finished processing. – jornare Apr 10 '12 at 19:31
  • I'd use Joshua's solution, but with a subquery so everything would be done at the SQL, so no need to "stop" the db. Not sure how to do this query, though. – Leonel Apr 15 '12 at 06:15

1 Answers1

1

100 billion is pretty rare so I assume you won't find a reliable answer on stackoverflow.

You should do your own benchmarks, look at the EXPLAIN keyword on mysql.com and write a short script (php one here) to analyze the time spent.

On a parallel note, I'm not sure whether MySQL is the best choice for a 100 billion database.

user103307
  • 19
  • 1