I have the following tables for a messaging system and I was wondering how I would go about querying the DB for how many conversations have new messages.

My tables are as follows

Conversation
------------
id
subject

Messages
--------
id
conversation_id
user_id (sender)
message
timestamp (time sent)

Participants
------------
conversation_id
user_id
last_read (time stamp of last view user viewed conversation)

I'm trying to do the following query but it returns no results:

SELECT COUNT(m.conversation_id) AS count
FROM (messages_message m)
INNER JOIN messages_participants p ON p.conversation_id = m.conversation_id
WHERE `m`.`timestamp` &开发者_如何学运维gt; 'p.last_read'
AND `p`.`user_id` = '5'
GROUP BY m.conversation_id
LIMIT 1 

Also, I probably will have to run this on every page load - any tips of making it as fast as possible?

Cheers

EDIT

I've got another somewhat related question if anybody would be so kind as to help out.

I'm trying to retrieve the subject, last message in conversation, timestamp of last convo and number of new messages. I believe I have a working query but it looks a bit badly put together. What sort of improvements can I do to this?

SELECT SQL_CALC_FOUND_ROWS c.*, last_msg.*, new_msgs.count as new_msgs_count 
                        FROM ( messages_conversation c ) 
                        INNER JOIN messages_participants p ON p.user_id = '5' 
                        INNER JOIN ( SELECT m.* 
                                    FROM (messages_message m) 
                                    ORDER BY m.timestamp DESC 
                                    LIMIT 1) last_msg 
                                ON c.id = last_msg.conversation_id 
                        LEFT JOIN ( SELECT COUNT(m.id) AS count, m.conversation_id, m.timestamp  
                                    FROM (messages_message m) ) new_msgs 
                                ON c.id = new_msgs.conversation_id AND new_msgs.timestamp > p.last_read 
                        LIMIT 0,10

Should I determine if the conversations is unread by doing an IF statement in MySQL or should I convert and compare timestamps on PHP?

Thanks again, RS7

'p.last_read' as quoted above is a string constant - remove the quotes from this and see whether that changes anything, RS7. If user_id is an integer than remove the quotes from '5' as well.

As far as performance goes, ensure you have indexes on all the relevant columns. messages_participants.user_id and messages_message.timestamp being two important columns to index.

Yes, you have problem in your query.

Firstly, you should have noticed that you count the column you are grouping, so the count result will be 1. Secondly, you are comparing the timestamp to a string : m.timestamp > 'p.last_read'. Finally, avoid using LIMIT when you know your query will return one row (be self-confident :p).

Try:

SELECT
  COUNT(m.conversation_id) AS count
FROM
  messages_message m
INNER JOIN
  messages_participants p ON p.conversation_id = m.conversation_id
WHERE
  m.timestamp > p.last_read
  AND p.user_id = 5

if you want to increase the query running time you can create a new index in message_participants (conversation_id, user_id) to index the conversations per users and then change your query with:

SELECT
  COUNT(m.conversation_id) AS count
FROM
  messages_message m
INNER JOIN
  messages_participants p ON p.conversation_id = m.conversation_id AND p.user_id = 5
WHERE
  m.timestamp > p.last_read

So that your DB engine can now filter the JOIN by simply looking at the index table. You could go deeper in this thought by indexing the timestampe too : (timestamp, conversation_id, user_id) and put the where condition in the join condition.

Whatever you choose, always put the most selective field first, to increase selectivity.

EDIT

First, let's comment your query:

SELECT
  SQL_CALC_FOUND_ROWS c.*,
  last_msg.*,
  new_msgs.count as new_msgs_count
FROM
  messages_conversation c
INNER JOIN
  messages_participants p ON p.user_id = 5 -- Join with every conversations of user 5; if id is an integer, avoid writing '5' (string converted to an integer).
INNER JOIN
( -- Select every message : you could already select here messages from user 5
  SELECT
    * 
  FROM
    messages_message m
  ORDER BY -- this is not the goal of ORDER BY. Use MAX to obtain to latest timestamp.
    m.timestamp DESC 
  LIMIT 1
) last_msg ON c.id = last_msg.conversation_id  -- this query return one row and you want to have the latest timestamp for each conversation.
LEFT JOIN
(
  SELECT
    COUNT(m.id) AS count,
    m.conversation_id,
    m.timestamp  
  FROM
    messages_message m
) new_msgs ON c.id = new_msgs.conversation_id AND new_msgs.timestamp > p.last_read 
LIMIT 0,10

Let's rephrase your query: select the number of new messages of a conversation subject, its last message and timestamp for user @id.

Do it step by step:

Selecting last message, timestamp in conversation for each user:

SELECT -- select the latest timestamp with its message
  max(timestamp),
  message
FROM
  messages_message
GROUP BY
  user_id

Aggregates functions (MAX, MIN, SUM, ...) work on the current group. Read this like "for each groups, calculate the aggregate functions, then select what I need where my conditions are true". So it will result in one row per group. So this last query selects the last message and timestamp of every user in the messages_message table. As you can see, it is easy to select this value for a specific user adding the WHERE clause:

SELECT
  MAX(timestamp),
  message
FROM
  messages_message
WHERE
  user_id = @id
GROUP BY
  user_id

Number of messages per conversation: for each conversation, count the number of messages

SELECT
  COUNT(m.id) -- assuming id column is unique, otherwise count distinct value.
FROM
  messages_conversation c
INNER JOIN -- The current user participated to the conversation
  messages_participant p ON p.conversation_id = c.id AND p.user_id = @id
OUTER JOIN -- Messages of the conversation where the current user participated, newer than last read its time
  messages_message m ON m.conversation_id = c.id AND m.timestamp > p.last_read = @id
GROUP BY
  c.id -- for each conversation

INNER JOIN won't return rows for conversations where the current user did not participated. Then OUTER JOIN will join with NULL columns if the condition is false, so that COUNT will return 0 - there is not new messages.

Putting it all together.

Select the last message and timestamp in conversation where the current user participated and the number of new messages in each conversation. Which is a JOIN between the two last queries.

SELECT
   last_msg.conversation_id,
   last_msg.message,
   last_msg.max_timestamp,
   new_msgs.nb
FROM
(
  SELECT
    MAX(timestamp)   AS max_timestamp,
    message,
    conversation_id
  FROM
    messages_message
  WHERE
    user_id = @id
  GROUP BY
    user_id
) last_msg
JOIN
(
  SELECT
    c.id         AS conversation_id
    COUNT(m.id)  AS nb
  FROM
    messages_conversation c
  INNER JOIN
    messages_participant p ON p.conversation_id = c.id AND p.user_id = @id
  OUTER JOIN
    messages_message m ON m.conversation_id = c.id AND m.timestamp > p.last_read = @id
  GROUP BY
    C.id
) new_msgs ON new_msgs.conversation_id = last_msg.conversation_id
-- put here and only here a order by if necessary :)