Doing a large number of upserts as fast as possible

Posted by Jason Swett on Stack Overflow See other posts from Stack Overflow or by Jason Swett
Published on 2011-01-14T21:48:09Z Indexed on 2011/01/14 21:54 UTC
Read the original article Hit count: 482

Filed under:
|
|

My app (which uses MySQL) is doing a large number of subsequent upserts. Right now my SQL looks like this:

INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('VICTOR H KINDELL OR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')
INSERT IGNORE INTO customer (name,customer_number,social_security_number,phone) VALUES ('TRACY L WALTER PERSONAL REP FOR','123','123','123')

So far I've found INSERT IGNORE to be the fastest way to achieve upserts. Selecting a record to see if it exists and then either updating it or inserting a new one is too slow. Even this is not as fast as I'd like because I need to do a separate statement for each record. Sometimes I'll have around 50,000 of these statements in a row.

Is there a way to take care of all of these in just one statement, without deleting any existing records?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about Performance