INSERT vs UPDATE

[svar]

This looks like a common enough situation that people must have come across, but it's my first time across it
The situation is simple: I have a table which holds 'summary data', i.e.
field_name PRIMARY COUNTfield_name othersumsfield_name

I am reading in data from a stream. If the field_name exists, I must do an UPDATE. If not, it is a brand new entry and I need an INSERT. The question is, since this happens in a flow, what is the FASTEST way of making this decision. Take a count and UPDATE or INSERT
depending on ehether the COUNT(*) WHERE field_name=current_file_name is 0 or nonzero?

Thanks, svar

 

[gheist]

An indexed unique field is selected really fast so you
SELECT COUNT(FIELD) FROM TABLE WHERE FIELD = 'value'

it comes out as 0 or 1

and then UPDATE or INSERT depending on your results

UPDATE and INSERT are not comparable - they have different meanings

UPDATE can be faster if you have no index on your table,
INSERT doesnt care
SELECT is slow without index

Sometimes Multi-threading in client side is required to do the trick, sometimes benchmarking&finetuning is needed for SQL statement text