[ACCEPTED]-MySQL performance DELETE or UPDATE?-performance

Accepted answer
Score: 23

UPDATE is by far much faster.

When you UPDATE, the table 10 records are just being rewritten with new 9 data.

When you DELETE, the indexes should be updated 8 (remember, you delete the whole row, not 7 only the columns you need to modify) and 6 datablocks may be moved (if you hit the 5 PCTFREE limit)

And all this must be done again on 4 INSERT.

That's why you should always use

INSERT ... ON DUPLICATE KEY UPDATE

instead 3 of REPLACE.

The former one is an UPDATE operation in case 2 of a key violation, while the latter one 1 is DELETE / INSERT.

Score: 4

It is faster to update. You can also use 2 INSERT ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

For more details 1 read update documentation

Score: 2

Rather than deleting or updating data for 3 the sake of performance, I would consider 2 partitioning.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html

This will allow you to retain 1 the data historically and not degrade performance.

Score: 1

Logically DELETE+ADD = 2 actions, UPDATE 5 = 1 action. Also deleting and adding new 4 changes records IDs on auto_increment, so 3 if those records have relationships that 2 would be broken, or would need updates too. I'd 1 go for UPDATE.

Score: 0

using an update where Column='something' should 8 use an index as long as the search criteria 7 is in the index (whether it's a seek or 6 scan is a completely different issue).

if 5 you are doing these updates a lot but dont' have 4 an index on the criteria column, i would 3 recommend creating an index on the column 2 that you are using. that should help speed 1 things up.

More Related questions