Mysql Performance: If exists update else insert



The standard way of doing update if the value exists (by doing select on the mysql table) and then insert is not efficient:

<?php
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)
?>

it is not efficient, since this does a table/index scan for both the SELECT statement and the UPDATE statement.

It can be improved as follows:

<?php
$result = mysql_query("update test set col='test' where col_id='1';");         
if (mysql_affected_rows()==0) {
    $result = mysql_query("insert into test (col_id, col) values ('1','test');");
}
?>

Better version
By CIGraphics

<?php
$q = mysql_query("SELECT COUNT(*) AS num FROM table WHERE col = 'test'");
if ( mysql_result($q, 0) == 1 ) {
  mysql_query("UPDATE table SET col = 'new_val'");
} else {
  mysql_query("INSERT INTO table (col) VALUES ('value')");
}
?>

1 year 18 weeks ago   Tags:


Why do you not use "INSERT ... ON DUPLICATE KEY UPDATE" ?

Um and what happens if col is already 'test'? In that case you get 0 affected records as well even though the record already exists.

may be no unique key or primary key ...

SELECT COUNT(*) is the best suggestion? really?

How about select one column, at least.