Mysql – Get the rows where id_user_rq exists in id_user_ap and viceversa

SELECT x.id1, x.id2 
FROM   tbl x
WHERE  EXISTS (
    SELECT *
    FROM   tbl y
    WHERE  (y.id1, y.id2) = (x.id2, x.id1)
    );

Assuming that the combination of (id1, id2) is unique, so no DISTINCT. This would still allow (1,2) and (2,1) to coexist (and both will be returned), but no two rows with (1,2).

Also assuming id1 != id2 for every row (deriving from your test data and question). A unique entry (1,1) without doppelganger would be returned by this query. If my first assumption is true, you can exclude id1 = id2 right away. Else, you'd need something like row_number() to identify duplicates ...

A multicolumn index would greatly speed up such a query:

CREATE UNIQUE INDEX tbl_id1_id2_idx ON tbl (id1, id2);

Use it without UNIQUE keyword, should (id1, id2) not be unique after all.

Or, you can use those other techniques discussed under your prequel question, accordingly modified.

The problem is query #4 needs a small adjustment. Now the query is:

SELECT p.*
    , rcp.*
    , msg.*
    , msg.id as message_id 
FROM default_messages msg 
    LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) 
    LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) 
WHERE (msg.sender_user_id = 1 AND msg.deleted = 0) 
       OR (rcp.user_id = 1 AND rcp.deleted = 0) 
ORDER BY msg.date DESC;