What are peoples' thoughts on the most performance efficient way to do the following query:

  • 3 column table

  • if the combination of col_1 and col_2 values already exist UPDATE col_3

  • else INSERT new row

I assume i need some kind if UPDATE ON DUPLICATE KEY (which i've never used before), however I do not have a 'KEY' but instead a pair of two values (columns) to make a key...


You can create a PRIMARY or UNIQUE key out of multiple columns (called a composite key) in MySQL, which'll allow ON DUPLICATE KEY to work just fine.

// create a composite index
CREATE INDEX my_composite_index ON my_table (column1, column2);

// insert or update
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2') ON DUPLICATE KEY UPDATE column3=column3+1;
  • thanks ceejayoz, are you able to give me some sample code for ON DUPLICATE KEY please? – Haroldo Jul 5 '10 at 19:46
  • @Haroldo Done, see edit. – ceejayoz Jul 5 '10 at 20:06
  • awesome, really kind of you - thanks so much! I should quickly check - i only need to create the index once right? – Haroldo Jul 5 '10 at 21:09
  • Yeah. Indexes can be very helpful for performance - I advise reading the MySQL docs on them. – ceejayoz Jul 5 '10 at 21:14

Most efficient way is to create UNIQUE KEY and use ON DUPLICATE KEY UPDATE.

Slower way is to:
SELECT TABLE (you need an index anyway for the best performance)
if exists, UPDATE


Edit: Ignore my suggestions

You can use a composite key as ceejayoz said, however I think you need REPLACE INTO instead of UPDATE ON DUPLICATE KEY because REPLACE also inserts if no duplicate is found.

Note: I don't know the workings of UPDATE ON DUPLICATE KEY but it sounds like it doesn't perform inserts.

Your Answer

