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

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

It takes over 3 seconds and I have put indexes on columns qid and via in the correct columns. The complexity is that I want a unique qid and that some rows have qid but are infact hyperlinks to other qids via the via field. And the qid most not have an entry in votes for uid = 3 (userID 3) Plus it must be sorted cronologically.

它花了3秒多的时间,我已经在列qid和via上放置了索引。复杂性是我想要一个独特的qid,并且有些行有qid但是通过via字段实际上是到其他qids的超链接。而且qid最多没有uid = 3(userID 3)的投票条目。它必须按时间顺序排序。

1 个解决方案

#1

Try this:

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

Without UNION ALL and GROUP BY

没有UNION ALL和GROUP BY

Tell me if it's ok. I compact two queries in one using OR logic operator.

告诉我它是否正常。我使用OR逻辑运算符将两个查询压缩在一起。

You don't take care about NULL value of via field. I use your notation (!= '' or = '') but perhaps you'd consider NULL value.

您不必关心via字段的NULL值。我使用你的符号(!=''或=''),但也许你会考虑NULL值。

#1