Re: Performance of ON DUPLICATE KEY and other options?

[Mark Modrall]

Jay Pipes wrote:
> I'm not sure I understand you. What is the
> purpose of updating the inputTable at all?
>

Well, all tables start empty when you create them. In the process of filling up a table like inputTable, where there may be multiple occurrences of the data you're putting in that you don't want duplicated (where you want a unique identifier to stand in for the value in normalized tables), you have to have some way of knowing if the value you want to put in is new or a duplicate of a value loaded prior.

When you're running through your raw input, you don't know if the value's already in there. You could try
SELECT qid from inputTable where input='?in'
first and then do an INSERT if you get no response.

Or you could use the ON DUPLICATE KEY UPDATE qid=LAST_INSERT_ID(qid) mechanism on your INSERT to try both with one statement. My question was whether the former approach performed better than ON DUPLICATE KEY UPDATE.

If the above is long winded and/or missing your point, the other thing I think you might mean is that there are/were other columns in inputTable that also track other aspects about the rows in inputTable. I left those out to simplify the example and discussion. In my case those things involve booleans indicating whether the input was as is or changed by a normalization process. I can also imagine a case where inputTable might have an integer column counting the number of different users who used the same value, etc.

So to flesh out the original example with the other boolean columns, the non-ON DUP logic would look
SELECT qid FROM inputTable where input='?in'
if (qid not null)
UPDATE inputTable SET rawInput=true where qid=?qid
else
INSERT INTO inputTable (input, rawInput) VALUES (?in, ?raw);

Thanks
_mark