mysql myisam update

Question

We use MySQL with MyIsam engine and have a situation where we need to update a huge number of rows (millions) on a daily basis while users can still be accessing the database.

We have users from different timezones from USA, UK, India, China. Data is downloaded from a third party source when the user first signs up. All users share the same set of tables, but their data does not overlap i.e. No user will ever be accessing the same rows as another user.

The third party system modifies the data so to keep our database up to date we download a new snapshot of the user’s data every night.The user’s data is updated at midnight relative to that user timezone.

Since all users share the same tables, this means the data update process can be updating the data of a user in an American timezone, while the users in the UK are actively browsing their data. This is where the problem lies. We have millions of data to be updated but we are not able to do.

Please suggest how to best update a users' data without affecting other users accessing the database. The data stored in database are numbers and all the columns are integers.

@Morgan thanks.. I am a newbie here..didn't know about this. I will try to accept answers.. searching how to do this.. — Madan Madan, Nov 04 '12 at 12:42
you can do this by clicking on the checkmark symbol on the left side of the answer. Good luck! — Morgan Wilde, Nov 04 '12 at 12:44

score 0 · Answer 1 · answered Nov 04 '12 at 15:12

0

I see two solutions:

Change to the InnoDB database engine - This will give you row level locking instead of table level. Just be aware inserts are generally slower.
Break up your tables by region. Based on your situation if your tables are segmented by region you'll never be reading and writing any table simultaneously.

answered Nov 04 '12 at 15:12

Matt S

14,976
6
57
76

@Matt..Thanks for the reply. We have used Indexing in the database. So, while changing to InnoDB Engine, will it be affected? – Madan Madan Nov 04 '12 at 17:03
@MadanMadan Indexing works the same for both. But doing many inserts and updates for InnoDB indexes is slower, so it's quicker to drop and recreate the indexes around millions of inserts, or at least instruct the DB to skip unique checks during inserts (as long as you're sure they're really unique). – Matt S Nov 05 '12 at 14:36

mysql myisam update

1 Answers1