0

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 |
+----+
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
| improve this answer | |
  • Worked like a charm! Nice how it outputs the sender_ids and receiver_ids as user_id. What if I want to include other columns in the output? Example: id, user_id, message, timestamp. – amatek May 22 at 19:59
  • @amatek What if I want to include other columns in the output? Select MAX(id) in addition to current ... AS user_id, use as a subquery for to select the whole row. – Akina May 22 at 20:05
1

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.)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.