Delete + Insert or Update?

[Anon]

hi, since my last question in regards of designing databases for a virtual stock exchange site, my site has grown quite a bit after 2 months. thanks for the advices. (plug: www.xmoo.com)

anyway, i'm now working on a ranking system, which, at the end of each day, i'll run a script to gather all players' info and do the calculation etc. my question is, in terms of speed and efficiency, which of the following options is better?

1) DELETE the whole table, and let the script INSERT each player's stats again.

2) UPDATE the table with new info gathered by the script, with the use of WHERE player=""

of course other suggestions are welcome.

thanks

will.

[Anon]

have you considered using timestamp or date data fields to query your results over a time interval? Then you could perform daily calculations and have to neither UPDATE or DELETE data.

[[deleted]]

2 ofcourse.

1 would require that you first lock the table, then select all rows into memory, then delete all rows, then update the data in memory, then insert the new data, then unlock the table.

2 simply updates those records that have changed.

(Imagine how 1 would work if you had 500 million rows...)

---

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

[Anon]

can you tell me more about it? can mysql updates automatically at a predefined time?