I've the next tables:

users:
    user_id
    user_name

message:
    message_id
    thread_id
    to_id
    from_id
    title
    message_text
    message_date        
    status

The preferred consequence of the query I run would be to list the title of the very recent message from the newest threads a lengthy using the thread_id and username and also to sort the end result by date in climbing down order. I am only likely to be listing about 10-20 results at any given time probably.

The SQL query I emerged with appears to do this to date, however i seem like I've over complicated it which there might be a far more optimal method to write my query.

    SELECT personal_messages.message_id, 
           personal_messages.thread_id, 
           personal_messages.body, 
           users.username
    FROM users, personal_messages
    WHERE message_id IN 
        (SELECT MAX(message_id) from personal_messages GROUP BY thread_id)
    AND users.id IN 
        (SELECT users.id FROM users WHERE users.id = personal_messages.from_id)
    ORDER BY personal_messages.message_date DESC

Also, if anybody knows a method to obtain the count of all of the messages with similar thread_id, that might be awesome!

Any tips could be greatly appreciated!

Among the subselects might be unnecessary

SELECT personal_messages.message_id, personal_messages.thread_id, personal_messages.body, users.username
FROM users INNER JOIN personal_messages ON (users.id = personal_messages.from_id)
WHERE message_id IN (SELECT MAX(message_id) from personal_messages GROUP BY thread_id)
ORDER BY personal_messages.message_date DESC    

Edit: Also, if anybody knows a method to obtain the count of all of the messages with similar thread_id, that might be awesome!

SELECT MAX(message_id), COUNT(message_id) FROM personal_messages GROUP BY thread_id
SELECT tbl.message_id, personal_messages.thread_id
       , personal_messages.body, users.username
FROM users u
JOIN personal_messages tbl on tbl. from_id = u.UsersId
WHERE tbl.message_id IN (SELECT MAX(message_id) from personal_messages 
GROUP BY thread_id)
ORDER BY personal_messages.message_date DESC

I do not think you will find a lot of subqueries inside your query but it may be written more brilliantly. I believe the below query should work as well:

SELECT `PM`.`message_id`, `PM`.`thread_id`, `PM`.`body`, `U`.`username`
FROM `users` `U`
INNER JOIN `personal_messages` `PM` ON `PM`.`from_id` = `U`.`user_id`
ORDER BY `PM`.`message_date` DESC;

The problems that I have found together with your query are:

  1. The very first sub-query - SELECT MAX(message_id) from personal_messages GROUP BY thread_id - will invariably return just one resultset, so utilizing an IN does not seem sensible
  2. For that second sub-query - SELECT users.id FROM users WHERE users.id = personal_messages.from_id - you can utilize an INNER JOIN rather, as with my example

Hope the above mentioned helps.

Also, I am unsure on which info are you currently really attempting to retrieve. However the above query should provide you with the particulars which are more recent message, together with its thread id and username.

EDITED query to choose one latest message per thread:

SELECT *
FROM (
    SELECT `PM`.`message_id`, `PM`.`thread_id`, `PM`.`body`, `U`.`username`
    FROM `users` `U`
    INNER JOIN `personal_messages` `PM` ON `PM`.`from_id` = `U`.`user_id`
    ORDER BY `PM`.`message_date` DESC;
) `TT`
GROUP BY `TT`.`thread_id`;

That which you have previously is fairly good. If you wish to do all of it with one query, you can obtain the COUNT(message_id) GROUP BY thread_id included in the same subselect that will get the MAX(message_id) GROUP BY thread_id:

SELECT personal_messages.message_id,
       personal_messages.thread_id,
       personal_messages.body,
       users.username,
       thread.countmessages
FROM personal_messages
JOIN users ON users.id=personal_messages.from_id
JOIN
     (SELECT COUNT(message_id) countmessages, 
             MAX(message_id) maxmessage_id
         FROM personal_messages
         GROUP BY thread_id) AS thread 
     ON thread.maxmessage_id=personal_messages.message_id
ORDER BY personal_messages.message_date DESC