CODEWITHSUNDEEP
phpmysqlmyisam
websiteguru
websiteguru

Reputation: 493

UPDATE, DELETE then INSERT or REPLACE INTO: fastest/scalable MySQL(MyISAM) method

I have a table that gets updated very regularly throughout the day, so Im looking for the most scalable method for updating rows. These updates happen in large batches, so each update may include around 1000 rows.

Currently, I'm looping through each of these 1000 rows and running a single update query... while it doesn't take long to execute, it just seems wasteful compared to one simple mass insert statement. So REPLACE INTO makes sense, since its basically deleting the old rows and inserting new ones, but how does that compare to a manual "delete where id in array" then mass insert? Exact same? Slightly different? Is there a better method?

The key here is that these aren't single row queries but mass row queries. So the question is, what is the most scalable way to run these updates. I say "scalable" and not "fastest" because these updates happen at regular intervals on a production server with active users, so speed is important but not at the cost of locking up the server.

Upvotes: 1

Views: 4818

Answers (2)

dkellner
dkellner

Reputation: 9966

I would NOT recommend InnoDB. It seems logical to use it because the papers say it's for this purpose and row level locking is a good thing - now whenever we tried it we've found that it's slower, much slower than MyISAM, and this can never be compensated by what you gain with the locking difference. It's simply not worth it.

Instead, I'm trying to answer the original question.

UPDATE is the fastest way.

Period.

REPLACE is almost never the proper way.

It's more like a patch to avoid transactions in certain cases, and it works well for that; but whenever you can manage, do a SELECT COUNT instead, to find out if you have rows with the same key, then choose UPDATE if the answer is yes and INSERT otherwise. Yes, this means 3 separate steps instead of one, I know. That's why I say "whenever you can manage". So, again: COUNT plus UPDATE is faster - REPLACE, on the other hand, is "atomic".

Ah yes, forgot to mention: from MySQL 4.1+ you have "INSERT ON DUPLICATE KEY UPDATE" which does all above in one step.

Make your rows fixed size if possible - this makes UPDATEs a lot faster.

If your table shows "Row size: dynamic" in PMA, your rows are able to grow/shrink and it's not for free. VARCHAR and other text fields usually do this. Now if you have a small row and you update it to something bigger, obviously UPDATE needs to look for a bigger space to fit the new row and remove the original one. In other words, MySQL will do something like an insert plus delete in this case. It's costly. If there's a way, use fixed rows for tables needing frequent updates. This will also reduce "table overhead".

You can update many rows with a single query

In mysql, you have CASE - so you can do something like "update all my rows, setting name to Jennifer if id=1, George if id=2, ...". It's complex to use it for updating if there are many fields but yes, you can do it, there is a way, and you can wrap it in a php function so that only query length will matter in the future. This way, your updates are atomic. You can call it "poor man's transaction".

Many updates are better than one

If you have many users, obviously you don't want to lock queries for more than a few ms. In this case, doing more UPDATEs is a lot better than one single big transaction because other processes can do their job and access the same tables in the meantime.

Upvotes: 0

Charles
Charles

Reputation: 51411

You want to use InnoDB for this instead of MyISAM. Why? Because when you're performing bulk inserts and deletes, wrapping the entire thing in a transaction can be a huge performance boost.

No matter what you end up doing to the data, that change alone could be huge.

With an appropriate transaction isolation level, your users could continue using the table while you change everything about it, only seeing the changes once you commit, without worry about table locks.

With regard to the actual data update, avoid deletes. Deletes are slow. Do updates, and delete only things you need to update. Avoid the REPLACE INTO magic as well, as it does a delete before an insert.

Upvotes: 5

Related Questions