Menu
I have a MyISAM table with more than 10^7 rows. When adding data to it, I have to update ~10 rows at the end. Is it faster to delete them and then insert the new ones, or is it faster to update those rows? Data that should be updated is not part of the index. What about index/data fragmentation
349
As a rule of thumb I would say a update is usually faster then an delete. As it had been stated an update has to do less work then a delete. But it depends largly how the update is done and how the table structure for the delete is. Here are some steps that the database will do when executing each command.
For best future query performance, it's better to do an update to keep the same extents. Delete and insert will not necessarily use the same extents. For a table of that size, it would be unlikely to do so. Furthermore, delete can leave "holes" in your data.
Insertion is inserting a new key and update is updating the value of an existing key. If that is the case (a very common case) , update would be faster than insertion because update involves an indexed lookup and changing an existing value without touching the index.
Inserting rows in a table is faster than deleting them. Loading data into a new table using create-table-as-select (CTAS) is faster still. So if you're removing most of the rows from a table, instead of issuing a delete you can: Create a new table saving the rows you want to keep.
UPDATE is by far much faster.
When you UPDATE, the table records are just being rewritten with new data.
When you DELETE, the indexes should be updated (remember, you delete the whole row, not only the columns you need to modify) and datablocks may be moved (if you hit the PCTFREE limit)
And all this must be done again on INSERT.
That's why you should always use
INSERT ... ON DUPLICATE KEY UPDATE
instead of REPLACE.
The former one is an UPDATE operation in case of a key violation, while the latter one is DELETE / INSERT.
146
It is faster to update. You can also use 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 read update documentation
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
