5

If I want to INSERT a row only if the primary key doesn't yet exist, is it more efficient/simpler to execute INSERT directly and ignore the error in the case of duplication, or should i always run SELECT to check if it already exists first?

4

You could use WHERE NOT EXISTS to check new values before insert a new record.

INSERT INTO <table>
(
    field1, field2, field3
)
SELECT value1, value2, value3
FROM dual
WHERE NOT EXISTS (SELECT 1
                  FROM <table>
                  WHERE <pk fields> = <new values>);
CREATE TABLE foo(id int, v1 int, v2 int);
INSERT INTO foo VALUES (1, 100,100);
INSERT INTO foo VALUES (2, 200, 200);

This record exists and should not be inserted:

INSERT INTO foo (id, v1, v2)
SELECT 1, 101, 101
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=1);

This is a new record:

INSERT INTO foo (id, v1, v2)
SELECT 3, 300, 300
FROM   dual
WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=3);

The final result:

SELECT * FROM foo;
id |  v1 |  v2
-: | --: | --:
 1 | 100 | 100
 2 | 200 | 200
 3 | 300 | 300

dbfiddle here

  • This would be equivalent to doing a SELECT then INSERT if not exists, just some syntactic sugar? – James Jul 6 '17 at 0:20
2

The best (more efficient option) will depend on the expected probability of PK collision.

If the chance of collision is high, then I'd save the DML overhead and do the SELECT first before the potential INSERT.

If the chance of collision is rare, then it is likely sufficient to do an INSERT IGNORE.

Note: if the PK is an auto_increment, a failed INSERT IGNORE can potentially inflate the auto_increment (see innodb_autoinc_lock_mode for variable options).

  • 1
    Is a failed INSERT operation costlier than SELECT? I was thinking that it could be exactly the same cost because both would involve first looking up the primary key on the table, then exiting immediately (returning the row in the case of SELECT) – James Jul 6 '17 at 0:22
  • 2
    In other experiments, it seems that the overhead (network, parsing, locking, etc) for any trivial statement is 90%. Hence, I suggest INSERT IGNORE would be about as fast as a failed SELECT and nearly twice as fast as SELECT + INSERT. But, beware of 'burning' an AUTO_INCREMENT id. – Rick James Jul 10 '17 at 4:17
1

Usually IODKU is the better way:

INSERT INTO tbl ...
    ON DUPLICATE KEY UPDATE 
        ....

https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

  • In my case, i want it to ignore the insertion instead of updating. – James Jul 10 '17 at 1:57
  • 1
    @James - What about INSERT IGNORE ... ? – Rick James Jan 28 at 21:29
-6

You should read this it might shed some light -

https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/06_ora.htm

  • 6
    I'd delete this if I were you - your link is about PL/SQL which is an Oracle database tool and nothing to do with MySQL. – Vérace Jul 5 '17 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.