SELECT u.id, u.login, u.last_login_time, 
( 
  SELECT count ( * ) FROM conversation c, message m 
  WHERE c.user1_id = u.id AND m.conversation_id = c.id AND
        COALESCE(c.message_last_read_id_user1, 0) < COALESCE(message_last_write_id_user2, 0) AND 
        m.id > COALESCE(message_last_read_id_user1, 0) AND m.id <= COALESCE(message_last_write_id_user2, 0) AND 
        m.poster_id = c.user2_id
) AS unread_user1, 
( 
  SELECT count ( * ) FROM conversation c, message m 
  WHERE user2_id = u.id AND m.conversation_id = c.id AND 
        COALESCE(message_last_read_id_user2, 0) < COALESCE(message_last_write_id_user1, 0) AND 
	c.user1_id > 0 AND m.id > COALESCE(message_last_read_id_user2, 0) AND m.id <= COALESCE(message_last_write_id_user1, 0) AND 
        m.poster_id != c.user2_id 
) AS unread_user2
FROM user u WHERE u.last_login_time >= 1452504418;

SELECT u.id, u.login, u.last_login_time
     , COUNT(CASE WHEN COALESCE(c.message_last_read_id_user1, 0) 
                     < COALESCE(message_last_write_id_user2, 0) 
                   AND m.id > COALESCE(message_last_read_id_user1, 0) 
                   AND m.id <= 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) 
                     < COALESCE(message_last_write_id_user1, 0) 
                   AND c.user1_id > 0 
                   AND m.id > COALESCE(message_last_read_id_user2, 0) 
                   AND m.id <= COALESCE(message_last_write_id_user1, 0) 
                   AND m.poster_id <> 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 >= 1452504418
GROUP BY u.id, u.login, u.last_login_time;

SELECT u.id, tmp.* 
FROM user u,
(  SELECT c.user1_id, count(m.id)
   FROM conversation c 
   JOIN message m ON m.conversation_id = c.id
   GROUP BY c.user1_id
) tmp
WHERE 
 tmp.user1_id = u.id AND
 u.last_login_time >= 1452610663 - 3600;

explain analyze SELECT u.id, tmp.* 
FROM co_user u,
(SELECT c.user_id, count(m.record_id)
FROM co_consultation c 
JOIN co_record m
ON m.record_consultation_id = c.id
GROUP BY c.user_id
) tmp
WHERE tmp.user_id = u.id and u.last_login_time >= 1452617409 - 3600;