mysql - 优化这个mysql查询GROUP BY + ORDER BY + UNION ALL

Question

SELECT * FROM (
                SELECT qid,via,date,uid, via as cool FROM questions WHERE via != '' AND (uid = 3 OR `uid` IN (SELECT uid_followed FROM followers WHERE uid_follower =  3 AND unfollowed = 0))
                AND via NOT IN (SELECT qid FROM votes WHERE uid =  3)
                UNION ALL
                SELECT qid,via,date,uid, qid as cool FROM questions WHERE via = '' AND (uid = 3 OR `uid` IN (SELECT uid_followed FROM followers WHERE uid_follower =  3 AND unfollowed = 0))
                AND qid NOT IN (SELECT qid FROM votes WHERE uid =  3)
                ) as a
                GROUP BY cool
                ORDER BY date DESC

它花费了 3 秒多的时间，我已经在正确的列中为列 qid 和 via 放置了索引。复杂性在于我想要一个唯一的 qid，有些行有 qid 但实际上是通过 via 字段到其他 qid 的超链接。并且 qid 大多数没有 uid = 3 (userID 3) 的投票条目此外，它必须按时间顺序排序。

Answer 1

试试这个:

SELECT qid,via,date,uid,
case
    when via = '' then qid
    else via
end as cool
FROM questions
WHERE (via != ''
AND via NOT IN (SELECT qid FROM votes WHERE uid =  3)
OR via = ''
AND qid NOT IN (SELECT qid FROM votes WHERE uid =  3))
AND (uid = 3 OR `uid` IN (SELECT uid_followed FROM followers WHERE uid_follower =  3 AND unfollowed = 0))
ORDER BY date DESC

没有 UNION ALL 和 GROUP BY

告诉我是否可以。我使用 OR 逻辑运算符将两个查询压缩为一个查询。

您不关心 via 字段的 NULL 值。我使用您的表示法(!= '' 或 = '')，但也许您会考虑 NULL 值。