CODEWITHSUNDEEP
phpmysqldatabase-connectionmessaging
Momen Zalabany
Momen Zalabany

Reputation: 9007

Mysql php private email like messaging system

I'm building a small messaging system for my app, the primary idea is to have 2 tables. Table1 messages

Id,sender_id,title,body,file,parent_id

Here is where messages are stored, decoupled from whom will receive it to allow for multiple recipients.

Parent I'd link to parent message if its a reply, and file is a blob to store single file attached to message

Table 2 message_users

Id,thread_id,user_id,is_read,stared,deleted

Link parent thread to target users,

Now for a single user to get count of unread messages I can do

Select count(*) from message_users where user_id = 1 and is_read is null

To get count of all messages in his inbox I can do

Select count(*) from message_users where user_id = 1;

Question is how to combine both in single optimized query ?

Upvotes: 0

Views: 972

Answers (3)

button
button

Reputation: 656

So you're trying to achieve something that will total rows that meet one condition and the total rows that meet an extra condition:

|---------|---------|
| total   | unread  |
|---------|---------|
| 20      | 12      |
|---------|---------|

As such will need something with a form along the lines of:

SELECT A total, B unread FROM message_users WHERE user_id=1

A is fairly straightforward, you already more-or-less have it: COUNT(Id).

B is marginally more complicated and might take the form SUM( IF(is_read IS NULL,1,0) ) -- add 1 each time is_read is not null; the condition will depend on your database specifics.

Or B might look like: COUNT(CASE WHEN is_read IS NULL THEN 1 END) unread -- this is saying 'when is_read is null, count another 1'; the condition will depend on your database specifics.

In total:

SELECT COUNT(Id) total, COUNT(CASE WHEN is_read IS NULL THEN 1 END) unread FROM message_users WHERE user_id=1

Or:

SELECT COUNT(Id) total, SUM( IF(is_read IS NULL,1,0) ) unread FROM message_users WHERE user_id=1

In terms of optimised, I'm not aware of a query that can necessarily go quicker than this. (Would love to know of it if it does exist!) There may be ways to speed things up if you have a problem with performance:

  • Examine your indexes: use the built in tools EXPLAIN and some reading around etc.
  • Use caches and/or pre-compute the value and store it elsewhere -- e.g. have a field unread_messages against user and grab this value directly. Obviously there will need to be some on-write invalidation, or indeed some service running to keep these values up to date. There are many ways of achieving this, tools in MySQL, hand roll your own etc etc.
  • In short, start optimising from a requirement and some real data. (My query takes 0.8s, I need the results in 0.1s and they need to be consistent 100% of the time -- how can I achieve this?) Then you can tweak and experiment with the SQL, hardware that the server runs on (maybe?), caching/pre-calculate at different points etc.

Upvotes: 2

Simo Kivistö
Simo Kivistö

Reputation: 4453

You can use sum with CASE WHEN clause. If is_read is null then +1 is added to the sum, else +0.

SELECT count(*),
  SUM(CASE WHEN is_read IS NULL THEN 1 ELSE 0 END) AS count_unread
  FROM message_users WHERE user_id = 1;

Upvotes: 1

pala_
pala_

Reputation: 9010

In MySQL, when you count a field, it only counts non null occurrences of that field, so you should be able to do something like this:

SELECT COUNT(user_id), COUNT(user_id) - COUNT(is_read) AS unread
    FROM message_users 
    WHERE user_id = 1;

Untested, but it should point you in the right direction.

Upvotes: 1

Related Questions