SQL query - how to efficiently receive unreadable messages

Question

What is the best way to collect messages that have not been read by this user?

Existing Tables

Message table
----------------------------------
id    title    body    sentAt

User table
----------------------------------
id    username

Read Messages table
----------------------------------
user_id    message_id

I think something like

select 
  m.id, m.title, m.sentAt, u.username
from 
  message m,
  [user] u
where 
  u.id = 1 and -- @userId parameter
  m.id not in 
    (select r.message_id from read_messages r where r.user_id = u.id)

Unfortunately, for me I do not really understand the execution plan. / Adam

Answer 1

Offering an alternative approach:

I had the same problem at work before. I spent a good week trying to figure out how to do this. As a result, I created a connection table, as you did, but the table contains only unread messages, and does not track the messages read.

Insofar as

• The status quo is "everyone has read all their messages."
• Receiving unread messages (or their number) should be as fast as possible.
• - .

, , , ( *), " " , , - , .

, " " , , . , , "SELECT COUNT(*) FROM unread WHERE user = foo".

, . , , - . . , , . (, , ", " ), .

, .

YMMV, HTH.

Answer 2

NOT IN . - :

SELECT
  m.id, m.title, m.sentAt
FROM
  message m
  LEFT JOIN [Read Messages] rm
    ON m.message_id = rm.message_id AND rm.user_id = @userID
WHERE
  rm.user_id IS NULL

, .

LEFT JOINing . WHERE user_id NULL , .

Answer 3

An alternative HP offer is probably right for you. However, if this is not the case, Id suggests using NOT EXISTS rather than LEFT JOIN if possible in your environment.

At least on MS SQL, he would give a slightly cheaper query plan since he did not need the last filtering (user_id IS NULL)

SELECT  ...
FROM    message m
WHERE   NOT EXISTS (
    SELECT 1
    FROM    read_messages rm
    WHERE   rm.usr_id = ...
    AND rm.msg_id = m.msg_id
)