如何联合 2 個子查詢以減少從繁瑣的表中檢索數據？

有 3 個表：user、conversation、message。

表user：

• ID，
• 登錄，
• last_login_time（最後一個使用者登錄的時間），
• …

表conversation：

• ID，
• user1_id,
• user2_id,
• message_last_read_id_user1 - 使用者 user1_id 上次閱讀消息的 ID，
• message_last_write_id_user1 - 使用者 user1_id 最後寫入消息的 id，
• message_last_read_id_user2 - 使用者 user2_id 上次閱讀消息的 ID
• message_last_write_id_user2 - 使用者 user2_id 最後寫入消息的 id，
• …

表message：

• ID，
• 對話ID，
• poster_id - 發送該消息的使用者 ID，
• 味精，
• …

目的是選擇在過去一小時內登錄的使用者，並為每個使用者選擇未讀消息的數量：

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;

unread_user1 或 unread_user2 之一總是為 0。查詢計劃在這裡http://explain.depesz.com/s/VT6

在該查詢中，兩個子查詢具有交叉連接conversation c, message m，這是非常繁重的操作。那麼也許有人知道如何將這些子查詢合併為一個？

一種想法是使用表達式進行計數：

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;

我可能在重寫時遺漏了一些部分，但希望你能得到一些想法。

如果較新的消息在表中始終具有message比舊消息更大的 ID 值，也許您可以通過刪除涉及的檢查來簡化子查詢中的過濾條件message_last_write_id_*。這些檢查似乎限制了結果集中的行數（因此可能會加速子查詢），但實際上，如果一條消息與對話匹配並且其 ID 大於上次讀取的 ID 並且發帖人不是user.id但對方，則為未讀消息user.id。

所以，這就是你可以計算的方式unread_user1：

SELECT
 COUNT(*)
FROM
 message AS m
 INNER JOIN conversation AS c
   ON  m.conversation_id = c.id
   AND m.poster_id = c.user2_id
WHERE
 c.user1_id = u.id
 AND m.id > COALESCE(c.message_last_read_id_user1, 0)

您還可以嘗試用m.id > COALESCE(c.message_last_read_id_user1, 0)析取替換謂詞，看看它是否可以為整個查詢帶來更有效的執行計劃：

 …
 AND (c.message_last_read_id_user1 IS NULL
   OR m.id > c.message_last_read_id_user1)

同樣，這將是子查詢unread_user2：

SELECT
 COUNT(*)
FROM
 message AS m
 INNER JOIN conversation AS c
   ON  m.conversation_id = c.id
   AND m.poster_id = c.user1_id
WHERE
 c.user2_id = u.id
 AND m.id > COALESCE(c.message_last_read_id_user2, 0)
 -- or like this:
 -- AND (c.message_last_read_id_user2 IS NULL
 --   OR m.id > c.message_last_read_id_user2)

引用自：https://dba.stackexchange.com/questions/125881