Need help with 2 MySql Queries. Join vs Subqueries

Question

I have 2 tables:

user: id, name message: sender_id, receiver_id, message, read_at, created_at

There are 2 results I need to retrieve and I'm trying to find the best solution. I have included queries that I'm using in the very end.

1. I need to retrieve a list of users, and also with each user have information available whether there are any unread messages from each user (them as sender, me as receiver) and whether or not there are any read messages between us ( they send I'm receiver or I send they are receivers)

2. I need Same as above, but only those members where there has been any messaging between us, sorted by unread first, then by last message received.

Can you advise please? Should this be done with joins or subqueries?

In first case I do not need the count, I just need to know whether or not there is at least one unread message. I'm posting code and my current queries, please have a look when you get a chance:

BTW, everything is the way I want in fist query.

My concern is: In second query I would like to order by messages.created_at, but I dont think I can do that with grouping? And also I dont know if this approach is the most optimized and fast.

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `user` VALUES (1,'User 1'),(2,'User 2'),(3,'User 3'),(4,'User 4'),(5,'User 5');

CREATE TABLE `message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender_id` bigint(20) DEFAULT NULL,
  `receiver_id` bigint(20) DEFAULT NULL,
  `message` text,
  `read_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

INSERT INTO `message` VALUES (1,3,1,'Messge',NULL,'2010-10-10 10:10:10'),(2,1,4,'Hey','2010-10-10 10:10:12','2010-10-10 10:10:11'),(3,4,1,'Hello','2010-10-10 10:10:19','2010-10-10 10:10:15'),(4,1,4,'Again','2010-10-10 10:10:25','2010-10-10 10:10:21'),(5,3,1,'Hiii',NULL,'2010-10-10 10:10:21');

SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u 
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
GROUP BY u.id

SELECT u.*, m_new.id as have_new, m.id as have_any
FROM user u 
LEFT JOIN message m_new ON (u.id = m_new.sender_id AND m_new.receiver_id = 1 AND m_new.read_at IS NULL)
LEFT JOIN message m ON ((u.id = m.sender_id AND m.receiver_id = 1) OR (u.id = m.receiver_id AND m.sender_id = 1))
where m.id IS NOT NULL
GROUP BY u.id

Answer 1

I'm fairly certain that JOINs are more performant than subqueries. One problem I see is that there aren't any date or foreign key indexes in your table creation scripts. You'll want those. Here's how I'd accomplish query #2, if I understood your question correctly:

SELECT u.*,
    m_new.id AS have_new,
    MAX(m_new.created_at) AS new_created,
    m.id AS have_any,
    MAX(m.created_at) AS created
FROM USER u 
LEFT JOIN message AS m_new
    ON u.id = m_new.sender_id
    AND m_new.receiver_id = 1
    AND m_new.read_at IS NULL
LEFT JOIN message AS m
    ON (u.id = m.sender_id AND m.receiver_id = 1)
    OR (u.id = m.receiver_id AND m.sender_id = 1)
WHERE m.id IS NOT NULL
GROUP BY u.id
ORDER BY new_created DESC,
    created DESC
;

---

Here are a couple of good links in SO on JOINs vs subqueries:

1. SQL: Join vs. subquery
2. Subqueries vs joins

Answer 2

The first query:

SELECT  u.*,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   sender_id = u.id
                AND receiver_id = 1
                AND read_at IS NULL
        ) have_new,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   (
                sender_id = u.id AND receiver_id = 1
                OR sender_id = 1 AND receiver_id = u.id
                )
                AND read_at IS NOT NULL
        ) have_any
FROM    user u

The second query:

SELECT  u.*,
        EXISTS
        (
        SELECT  NULL
        FROM    message
        WHERE   sender_id = u.id
                AND receiver_id = 1
                AND read_at IS NULL
        ) have_new,
        (
        SELECT  created_at
        FROM    message
        WHERE   (
                sender_id = u.id AND receiver_id = 1
                OR sender_id = 1 AND receiver_id = u.id
                )
        ORDER BY
                created_at DESC
        LIMIT 1
        ) last_message
FROM    (
        SELECT  DISTINCT sender_id AS party
        FROM    message
        WHERE   receiver_id = 1
        UNION
        SELECT  DISTINCT receiver_id
        FROM    message
        WHERE   sender_id = 1
        ) m
JOIN    user u
ON      u.id = m.party
ORDER BY
        have_new DESC, last_message DESC

Create the following indexes:

messages (sender_id, receiver_id, last_message)
messages (receiver_id, sender_id, last_message)