0

We have an Android app that communicates with a php/MySQL server via a stateless JSON protocol.

  • A user is logged in to the app and has a corresponding user ID.
  • The app receives a list of items/posts from the server upon request.
  • When a user swipes away one of the items in the list, a "response" message is sent to server, and the item ID is added to a don't care table with the user ID.
  • Now, to replace the removed item with a new one, it's easy to ignore the items in the don't care table that match the user ID; however, the server should not return items that are currently in the user's app, i.e. items in the list that have not yet been swiped away.

What is the most efficient way (in terms of server load) to keep track of what items are currently in users' lists?

Some solutions I've thought of:

  1. Upon sending a list of items, place all item IDs in a sent-to-user table that has user-id and item-id columns. Unfortunately, that means that each list request requires a write operation. Additionally, the message could possibly not arrive at the user?
  2. With each request message, the app includes an array of items currently in the user's list. The problem is that it essentially becomes a problem of doing a lookup of a comma separated list, which is almost as bad as this, requiring a FIND_IN_SET operation. If we have a large number of items, then this will be an expensive operation.

As it stands I'm leaning towards (1).

EDIT : What it boils down to is that I would prefer option (2), if it was a sargable query. Is there a sargable way to receive a JSON array of numbers, and use those numbers in a MySQL IN query?

Community
  • 1
  • 1
karel
  • 468
  • 5
  • 14

1 Answers1

0

Can't you manage this with a simple flag in the database table? Ie: The first time you send a list to the user, write it all to a table and put the flag = "SENT" (or 0, or blank, or something). When he swipes one away, update the flag to "DONT CARE" (or 1, or something). Next time you send a list, just send stuff that's not already in the table. That way you're not sending anything he's already seen, and in the table you can see exactly what's on his list at the moment (It'll be all the stuff where that flag is "SENT", 0 or blank, whichever you choose)

GarethL
  • 1,473
  • 1
  • 15
  • 16
  • Thanks for your answer. I think that's essentially what I suggested in (1) - note that each request requires a write operation then and I don't know if the user actually ended up receiving it. – karel Apr 09 '14 at 17:13