0

I have three tables as follows

messages

id message
1 This is the first message
2 This is the second message
3 This is the third message

users

id username
1 firstuser
2 seconduser
3 thirduser

message_user_reads

id user_id message_id read_at
1 1 1 2021-02-15 12:33:13
2 1 2 2021-02-11 12:33:13
3 2 1 2021-02-10 12:33:13

From the above, I want to find out the count of messages not read by a particular user.

I have derived a query as below to solve the above puzzle but I am concerned about performance issues as messages table can have many records as time goes. Here is the query

select * from `messages` where `id` not in (select `message_id` from `message_user_reads` where `user_id` = 1)
Aniket
  • 156
  • 13

2 Answers2

4

for get the message not read you can use left join

select messages.* 
from `messages` m
left join message_user_reads mur on mur.message_id = m.id   
    and mur.user_id = 1
where mur.message_id is null

the use of left join produce better performance respect to not in

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
0
select * from 
messages msg inner join
users usr on msg.user_id = user.id 
where 
not exists  
( 
select 1 from 
message_user_reads 
where user_id = usr.id
) 
and user.id=1

You will find a good explanation in this answer

fancyuserid
  • 145
  • 13