通过+ ORDER BY + UNION ALL优化此mysql查询组

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

没有工会的所有和团体

如果可以的话告诉我。我使用OR逻辑运算符将两个查询压缩在一起。

您不需要处理via字段的空值。我使用您的表示法(!=‘或= '')，但是您可能会考虑空值。