Always check with SELECT before INSERT?

Question

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?

Answer 1

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

Answer 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).

Answer 3

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