MySQL/PHP - SELECT DISTINCT column based on another column with ORDER and LIMIT

Question

I have a table named messages that is as follows:

+-----+-----------+-------------+-----+
| id  | sender_id | receiver_id | ... |
+-----+-----------+-------------+-----+
|   . |         . |           . | ... |
|   . |         . |           . | ... |
| 120 |        19 |          11 | ... |
| 121 |         1 |           3 | ... |
| 122 |         9 |          18 | ... |
| 123 |         2 |           1 | ... |
| 124 |         1 |          24 | ... |
| 125 |         3 |           1 | ... |
| 126 |         7 |           5 | ... |
| 127 |        24 |           1 | ... |
| 128 |        25 |           1 | ... |
| 129 |         1 |          25 | ... |
| 130 |         7 |           3 | ... |
| 131 |         3 |           5 | ... |
+-----+-----------+-------------+-----+

How can I select the last 3 unique users that have communicated with User 1? In other words, how can I select:

• distinct sender_ids whose receiver_id is 1 or distinct receiver_ids whose sender_id is 1
• in descending order and limited to 3?

The result would look like this:

+-----+-----------+-------------+-----+
| id  | sender_id | receiver_id | ... |
+-----+-----------+-------------+-----+
| 129 |         1 |          25 | ... |
| 127 |        24 |           1 | ... |
| 125 |         3 |           1 | ... |
+-----+-----------+-------------+-----+

or simply:

+----+
| 25 |
| 24 |
|  3 |
+----+

Answer 1

Test:

SELECT CASE WHEN sender_id = 1 THEN receiver_id ELSE sender_id END user_id
FROM messages 
WHERE 1 IN (sender_id, receiver_id)
GROUP BY user_id
ORDER BY MAX(id) DESC LIMIT 3

Answer 2

1 IN (a,b) won't scale. It will do a full table scan. This will make use of indexes:

( SELECT id, receiver_id FROM messages WHERE sender_id = 1   LIMIT 3 )
UNION ALL
( SELECT id, sender_id   FROM messages WHERE receiver_id = 1 LIMIT 3 )
LIMIT 3 

together with both

INDEX(sender_id, receiver_id, id),
INDEX(receiver_id, sender_id, id)

Now, assuming you want the rest of the columns for the 3 rows:

SELECT m.*
    FROM ( ... ) AS x    -- put the above UNION inside here
    JOIN messages USING(id)

You have not said what your "descending order" is. Perhaps id? Perhaps a datetime? id will be a little easier, so here goes:

SELECT m.*
    FROM (
            ( SELECT id, receiver_id FROM messages WHERE sender_id = 1
                  ORDER BY id DESC  LIMIT 3 )
            UNION ALL
            ( SELECT id, sender_id   FROM messages WHERE receiver_id = 1
                  ORDER BY id DESC  LIMIT 3 )
            ORDER BY id DESC  LIMIT 3   -- yes, repeated
         ) AS x
    JOIN messages USING(id)
    ORDER BY id DESC    -- yes, ORDER BY is repeated again

Discussion:

• Each side of the UNION can use an index to quickly find the requested row(s).
• id is assumed to be the PRIMARY KEY of messages.
• Each INDEX is "covering" -- all the columns needed in the SELECT are present in the INDEX.
• Changing from ordering by id to a datetime disrupts the "covering", but can be remedied.
• Getting 3 rows from each subquery, then chopping to 3 again -- necessary to allow for a variety of cases of who emailed whom and when.
• The final JOIN needs to look in the data's BTree only 3 times -- that is the goal of the "covering" and the initial "derived" table (the one with the UNION).
• For only thousands of rows, this complexity is not needed. But if you have millions of rows, it is desirable. For billions of rows, the complexity is essential.
• OFFSET gets trickier. A discussion here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or
• UNION ALL is faster than UNION DISTINCT and seems to be appropriate in this case. (A message from a user to himself will show up twice with ALL.)