Hfrhyu

I have a web server on which I've implemented my own messaging system.
I am at a phase where I need to create an API that checks if the user has new messages.

My DB table is simple:

ID - Auto Increment, Primary Key (Bigint)
Sender - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Recipient - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Message - Varchar (256) //UTF8 BIN

I am considering making an API that will estimate if there are new messages for a given user. I am thinking of using one of these methods:

A) Select count(*) of messages where sender or recipient is me.

(if this number > previous number, I have a new message)

B) Select max(ID) of messages where sender or recipient is me.

(if max(ID) > than previous number, I have a new message)

My question is: Can I calculate somehow what method will consume fewer server resources? Or is there some article? Maybe another method I didn't mention?

In MySQL InnoDB, SELECT COUNT(*) WHERE secondary_index = ? is an expensive operation and when the user has a lot of messages, this query might take a long time. Even when using an index, the engine still needs to count all matching records. The performance will degrade with growing total message count.

On the other hand, SELECT MAX(id) WHERE secondary_index = ? can deliver the highest id in that index very efficiently by doing a so-called loose index scan. The performance will stay almost constant.

If you want to understand why, consider looking up the B+Tree data structure which InnoDB uses to organise its data.

I suggest you go with SELECT MAX(id), if the requirement is only to check if there are new messages (and not the count of them).

Also, if you rely on the message count you might open a gap for race conditions. What if the user deletes a message and receives a new one between two polling intervals?

To have the information that someone has new messages - do exactly that. Update the field in users table (I'm assuming that's the name) when a new message is recorded in the system. You have the recipient's ID, that's all you need. You can create an after insert trigger (assumption: there's users2messages table) that updates users table with a boolean flag indicating there's a message.

This approach is by far faster than counting indexes, be the index primary or secondary. When the user performs an action, you can update the users table with has_messages = 0, when a new message arrives - you update the table with has_messages = 1. It's simple, it works, it scales and using triggers to maintain it makes it easy and seamless.
I'm sure there will be nay-sayers who don't like triggers, you can do it manually at the point of associating a user with a new message.

If you need to know the number of new messages then using
Select count(*) from Messages where user_id in (sender, recipient) and id > last_seen_id would be your best option.

I'm a fan of using exists where possible, so to determine IF there are new messages, my query would be Select exists(Select 1 from Messages where user_id in (sender, recipient) and id > last_seen_id). The benefit of exists is that as soon as it finds 1 record it returns true.

Edit: To avoid any confusion in reading this answer, both of those queries would also include a check for other_user_id in (sender, recipient) in order to only return the messages between 2 specific users.

@FeHora You talk about not using keys to save db space. The table designs wastes more db space.

ID - Auto Increment, Primary Key (Bigint)

Is bigint really necessary? Let us assume, the a message is send every second. The a int unsigned is enough for 126 years. And if you have really so much messages, a key is mandatory.

Sender - Varchar (32) // Foreign Key to UserID hash from Users DB Table
Recipient - Varchar (32) // Foreign Key to UserID hash from Users DB Table

Why not using the UserID (usually an int unsigned).

Then I would add a seen flags. Btw, you can add for all filed the attribute not null.

seen tinyint not NULL.

Last not least I recomment the variant of @Mjh : Define a flag has_messages, or new_messages, or both in the user record. Usually, the user record is loaded so it is NOT an additional database query.