MySQL get last Message (VarChar)

Question

I'm trying to obtain the last message from a certain thread but can't seem to get my query right. I don't seem to get the last message from a certain thread.

Introduction

I use 3 tables -- message, message_users, message_threads

All messages belong to a certain thread (Kind of like how forums and posts work?) Users can add multiple users to a certain thread, so multiple users can message each other in the same thread.

Database structure

messages table
    id  |   t_id  |  u_id  |  message  |  time
--------+---------+--------+-----------+--------
      1 |    1    |   1    |  hello!   | timestamp
      2 |    1    |   2    |  hi!      | timestamp
      3 |    2    |   1    |  testing  | timestamp


message_threads
   id |  author |  subject  
------+---------+-----------
    1 |    1    | Subject 1  
    2 |    3    | Subject 2 
    3 |    2    | Subject 3


message_users
   id |   t_id  |  u_id  |  read  | nonotice
------+---------+--------+--------+----------
    1 |    1    |   1    |   0    |   0
    2 |    1    |   2    |   0    |   0
    3 |    2    |   1    |   1    |   0

Query

This is the query I'm using, it almost works except it doesn't get the last ID, neither the last message, any ideas? (Yes, I've looked and searched for previous questions asked regarding this subject but wasn't able to get the query fixed based on the answers somehow)

SELECT
  m2.message,
  mt.id AS thread_id,
  max(m.id) AS message_id, 
  max(m.time) AS time, 
  mt.subject
FROM message_users AS mu
LEFT JOIN message_threads AS mt ON mt.id = mu.t_id
LEFT JOIN message AS m  ON m.t_id = mt.id
INNER JOIN (
  select message as messages_text 
  from message group by t_id
) AS m2 ON m2.id = m.id
WHERE mu.u_id = 1 AND mu.read = 0 AND mu.nonotice = 0
GROUP BY mt.id
ORDER BY m.id DESC

Answer 1

SELECT
  m.t_id,
  m.message,
  m.id AS max_message_id,
  m.ts AS max_message_ts,
  t.subject
FROM messages AS m
JOIN message_threads AS t ON m.t_id = t.id
JOIN (
  SELECT t_id, max(id) max_id FROM messages GROUP BY 1
) AS m2 ON m.id = m2.max_id
JOIN message_users AS u ON u.t_id = t.id AND u.u_id = 1 AND u.`read` = 0 AND u.nonotice = 0

If above SQL becomes slow, you can do either of the following:

1 - add where into the inner-join SQL to make that result smaller, which is:

SELECT
  m.t_id,
  m.message,
  m.id AS max_message_id,
  m.ts AS max_message_ts,
  m2.subject
FROM messages AS m
JOIN (
  SELECT t.id, max(m.id) AS max_id, t.subject
  FROM messages AS m
  JOIN message_threads AS t ON m.t_id = t.id
  JOIN message_users AS u ON u.t_id = t.id 
     AND u.u_id = 1 AND u.`read` = 0 AND u.nonotice = 0
  GROUP BY 1
) AS m2 ON m.id = m2.max_id

2 - redesign message_thread add calulated columns: (last_message_id, last_updated) etc

PS - try to avoid MySQL keywords as your column names