1

For a two-player game, I need to send updated data to player every 30 seconds.

I have a table (ideally 4 tables) from where I need to select data and sent to user once he/she login. Since it is multi-player interaction game, data needs to be sync every 30-60 seconds.

My problem is, I've a very heavy query to run every 30-60 seconds. So ideally, I should send only updated and new rows to the player during sync (Its also a front end requirement for IPhone/Android game, app don't want whole data during every sync operation).

I went through MySQL: difference of two result sets and hope I'll get only updated/new records through SQL but problem is, how do I save result of last query.

Even if I save first result in Session (probably not recommended) that record will be useless as soon as new row inserted or updated. Updating session record again will definitely put lot of pressure on the server.

Can someone please suggest the best way to achieve this requirement; Not detailed solution, just some hint/link will be sufficient.

Community
  • 1
  • 1
Kapil Sharma
  • 10,135
  • 8
  • 37
  • 66
  • So what is your question clearly here? You need to output values from database ever 30-60 seconds? – samayo Nov 05 '12 at 13:32
  • @Eritrea Yes for the first time; after first time only the difference data that is added/updated after last query. – Kapil Sharma Nov 05 '12 at 13:34
  • When you insert data to SQL make sure you include time also, then if you want to output data, just do a query to return values by time (which means, show last submitted data from database) – samayo Nov 05 '12 at 13:38

2 Answers2

2

Can't you just timestamp everything?

Give every row in the tables a timestamp called something like "last_updated"

In the query, filter out all entries with a last_updated that is before the last time the query was executed (or possibly the latest last_updated that the client got the last time it called the server)

Cyberwiz
  • 11,027
  • 3
  • 20
  • 40
2

Basically, this isn't that hard. Let me provide you with a step plan.

  1. Add a datetime field to each table you want to do this on
  2. In each of your updating queries, set this field to NOW()
  3. Make sure that the application adds the time of its last update to all its requests
  4. Have the server add the time of the update to result it send to the app (which also sends the updated rows)
Jasper
  • 11,590
  • 6
  • 38
  • 55