Re: [SQLObject] Efficiently updating rows?

[Oleg B. <ph...@ph...>]

Hello! Sorry for the later answer.

On Sat, Apr 02, 2011 at 07:41:17PM +0000, Mark wrote:
> I've got 100,000 users running my app which stores their 
> data locally. I'm now releasing cell phone apps and need 
> to sync the data through a web server.
> 
> Each user would have on average 10,000 rows of

   Wow, that's the task!

> item:
>    string item_name
>    int  days_since_last_update
>    int  item_value
> 
> What will happen about 50 times a day is the server will
> receive a list of 5 items to update their values ( if current 
> day is greater than that row's days_since_last_update+2 ) ...
> 
> So given item names: A, B, C, D, E whats the most 
> efficient way to get those 5 items and then update their
>  values if the current_day is greater than that items 
> days_since_last_update? 

   I don't see any other way but SELECT and UPDATE.

> Also each row is actually about 200 bytes. Thats 2mb 
> per user or 200gb for the database? Do I need to be 
> concerned about performance of my MySQL database? 

   Yes, I think you should worry about big memory, a fast disk, buffers
and indices. May be a few fast disks - MySQL 5.0+ can do data
partitioning, but you have to devise a good partitioning schema.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.