How to unite 2 subqueries to reduce retrieving data from cumbersome tables?
One idea is to count using an expression:

SELECT u.id, u.login, u.last_login_time
     , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0) 
                     &lt; COALESCE(message_last_write_id_user2, 0) 
                   AND m.id &gt; COALESCE(message_last_read_id_user1, 0) 
                   AND m.id &lt;= COALESCE(message_last_write_id_user2, 0) 
                   AND m.poster_id = c.user2_id
                  THEN 1
             END) as unread_user1
     , COUNT(CASE WHEN COALESCE(message_last_read_id_user2, 0) 
                     &lt; COALESCE(message_last_write_id_user1, 0) 
                   AND c.user1_id &gt; 0 
                   AND m.id &gt; COALESCE(message_last_read_id_user2, 0) 
                   AND m.id &lt;= COALESCE(message_last_write_id_user1, 0) 
                   AND m.poster_id &lt;&gt; c.user2_id
                  THEN 1
             END) as unread_user2   
FROM user u 
JOIN conversation c
    ON c.user1_id = u.id
JOIN message m
    ON m.conversation_id = c.id
WHERE u.last_login_time &gt;= 1452504418
GROUP BY u.id, u.login, u.last_login_time;


I might have missed some parts when rewriting, but hopefully you will get some ideas.