MySQL: is checking for new records by values of auto_increment column bad?

Question

I'm making a web application which also includes notification functionality - a user may get one or more notifications (messages) from the system or other users at any time.

When there are new (unread) messages they will be displayed to the user in a popup window. The messages will be short (most of the time one sentence each, and only a few at a time), so they will be displayed completely and all at once.

The user will then have a "Dismiss" button which will mark these messages as "Read".

My question comes about implementing this "Dismiss" button. One approach would be to remember the ID's of the messages and mark each one individually. However that would require one query per message.

A better approach would be to remember the smallest ID and the largest ID, and then issue an UPDATE WHERE ID between XXX and YYY. But is this safe? Can I be certain that the IDs of new rows INSERTED in the table will be larger then these IDs? Are there no exotic conditions that might foil this approach? (clustering? load balancing? replication? something else?)

Answer 1

But is this safe?

To do this safe you will need to use encryptation.

Can I be certain that the IDs of new rows INSERTED in the table will be larger then these IDs?

Yes.

Are there no exotic conditions that might foil this approach?

As i think, no.

Answer 2

Perhaps this is off base, but why not remember all the message id's and issue an update query with a

where ID in (ID1, ID2, ID3...)

Answer 3

You could use your own counter rather than AUTO_INCREMENT. Increment your counter on each new message.

Answer 4

It's not really directly answering the question, but I don't know that "dismissing" a notification should mark messages as "read" - this seems to be counterintuitive behavior for the user.

Instead, it should just dismiss the notification. A simple way to do this would be to keep a timestamp updated by the "dismiss" button and probably also when mail is actually read.

When the user clicks on "dismiss" that timestamp is updated and the notification popup should only be displayed when there are UNREAD messages newer than that timestamp.