Depends on the cardinality.

If by using INSERT .. ON DUPE.. leaves you DELETING 80% of rows, performance might suffer.

Maybe in that case it's best to do TRUNCATE->CREATE->INSERT.

300 rows isn't too large a data set to populate (depending on datatypes used).

Only way to get a definitive answer is to benchmark each method.

And with the TRUNCATE->INSERT alternative you avoid searches that has to happen for each UPDATE/DELETE query.

So for a table with 300 rows where you also will delete the rows that was affected I think you are best off with trunacting and inserting the new recods.

Puzzles,

I know this is not an answer to your question, but why not use HEAP instead of InnoDB? This would completely eliminate disk I/O and as the content is regenerated every few minutes, you should not really care too much about loosing data. As update is relatively fast, table locks should not be an issue here.

As for DEL/INS vs UPDATE, you could take few thousand samples from binary logs (to match ± average workload) and run few benchmarks (probably by running two copies of SuperSmack - one for sequential DEL/INS or UPDATE procedures and the other one - for concurrent SELECT workload).

Hi all,

Thanks for your replies.

The reason I chose InnoDB and the DELETE/INSERT method is that it's very important that we get consistent data from the database at all times. With InnoDB, I can do my updates (DEL/INS) in a transaction and then COMMIT the changes in one step, meaning that all reads from the table will result in "correct" data.

The problem with the MEMORY (or HEAP) storage engine is that it doesn't support transactions, so in that case, the DEL/INS method is no longer usable. (Having people read an empty table, even for just a split second is not an option here.) The same goes for TRUNCATE, as you can't use it in a transaction, since it drops and recreates the table.

Anyway, thanks for your suggestions. I'll try benchmarking both methods and see what I come up with that way.

Thanks.