Array multi insert uname and email check

Question

I have an array I want to check whether the username and Email values exists in database, if it does not exist insert the username and email values in the database. Using Multi insert since there are around 80000 inserts to be preformed.

$arr1 = Array
(
    [0] => Array       ( [Username] => uname1,  [Email] => email1  )

    [1] => Array   (  [Username] => uname2,  [Email] => email2  )

    [2] => Array   (  [Username] => uname3,  [Email] => email3 )

)

Answer 1

A SQL statement with 80000 INSERTs will function slower than 80000 separate INSERTs, because the monolithic, multi-INSERT SQL statement-string won't need to be built. For overwriting existing rows, you will need a primary key to ensure that duplicate records are overwritten: http://dev.mysql.com/doc/refman/5.1/en/replace.html.

foreach ($arr1 as $user){
    $sql = "REPLACE INTO `users` SET `Username`='" . mysql_escape_string($user["Username"]) . "', `Email`='" . mysql_escape_string($user["Email"]) . "'";
    mysql_query($sql);
}

My code assumes that Username is the primary key. If not, add to the SET clause to include the primary key. If Username is your primary key, consider using an integer-based one as primary keys based on strings are slow.

Answer 2

The simplest way would be to create a unique index on username+email. Then you can just do an INSERT IGNORE. Although you probably do not want to bulk insert 80000 records using PHP unless you also adjust the timeout parameters. It would be better to break that up into bulk inserts of say 500 at a time. Then it will scale well.

Using INSERT IGNORE with the unique index will just insert records that don't already have a match in the index. Do NOT do single insert and/or lookups. While that will scale, it will be slow. Chunk your data, it will scale and perform well.