mysql - SQL - 多重联接(新闻源)

Question

我正在尝试为我的网站创建一个新闻源，其中显示 3 种类型的信息:问题、答案(与问题相关)和讨论。

我有一个名为“事件”的主表，用于记录平台的所有事件，以及用于每种记录类型(用户、问题、答案和讨论)的单独表，如下所示:

餐 table 事件

EventID  EventType   fkQuestionID   fkAnswerID   fkDiscussionID
1        Question    1              NULL         NULL
2        Question    2              NULL         NULL
3        Discussion  NULL           NULL         1
4        Answer      1              1            NULL
5        Question    3              NULL         NULL
6        Discussion  NULL           NULL         2
7        Answer      2              2            NULL
8        Discussion  NULL           NULL         3

表格问题

QuestionID  fkUserID    QuestionTitle
1           1           Who is Homer Simpson?
2           2           What is the capital of Madagascar?
3           3           What superpower would you choose?

表格答案

AnswerID    fkQuestionID    fkUserID    Answer
1           1               2           He is a characted of a...
2           2               4           The capital is...

表格讨论

DiscussionID    fkUserID    DiscussionTitle
1               3           The best day of my life
2               1           The worst zombie movies
3               2           The funiest scens of...

表用户

UserID  Name
1       Jack
2       Ana
3       Rose
4       Brad

这是预期的结果:

到目前为止，我创建了以下查询:

SELECT E.EventID,
E.EventType,
E.fkQuestionID,
E.fkAnswerID,
E.fkDiscussionID,
Q.QuestionTitle,
U1.Name as QuestionBy,
A.Answer,
U2.Name as AnswerBy,
D.DiscussionTitle,
U3.Name as PostBy
FROM events E
LEFT JOIN questions Q ON Q.QuestionID=E.fkQuestionID
LEFT JOIN users U1 ON U1.UserID=Q.fkUserID
LEFT JOIN (
SELECT fkQuestionID, MAX(AnswerID) AS AnswerID
FROM answers
GROUP BY fkQuestionID
) t ON t.fkQuestionID = E.fkQuestionID  //show the last answer
LEFT JOIN answers A ON A.pkAnswerID = t.AnswerID
LEFT JOIN users U2 ON U2.UserID=A.fkUserID
LEFT JOIN dicussions D ON D.DiscussionID=E.fkDiscussionID
LEFT JOIN users U3 ON U3.UserID=D.fkUserID

鉴于表 Events 将存储大量行，我对一些事情有疑问，还对性能缓慢表示怀疑:

• 当前查询已花费 +2 秒来处理(30 行)。由于我正在制作一种新闻提要页面，而不是一次对所有数据进行大量查询，我正在考虑使用循环并进行几个单独的查询来为每种类型的事件带来剩余信息并使用 ajax 加载它(减少服务器响应并增加页面加载的印象)。这是一个愚蠢的想法吗？

• 对于与表 Users 的联接，是否有更好的方法来代替对每种类型的事件使用许多 LEFT JOINS(U1、U2、U3)？我担心对所有类型的事件使用许多 LEFT JOINS。

• 如果有人写了一个新答案，我会尝试仅提供与该问题相关的最后一个答案(最大答案ID)。但是，我正在努力隐藏与同一问题相关的其他事件(以避免同一问题的重复提要/事件)。有谁知道我该如何解决这个问题？

Answer 1

我个人认为事件表没有任何理由。

我将通过 UNION ALL 查询来解决这个问题，并将问题和答案链接到查询的同一行。类似于:

SELECT 
'Question' as EventType,
Q.QuestionID,
t.AnswerID,
NULL as DiscussionID,
Q.QuestionTitle,
U1.Name as QuestionBy,
A.Answer,
U2.Name as AnswerBy,
NULL as DiscussionTitle,
NULL as PostBy
FROM  questions Q 
JOIN users U1 ON U1.UserID=Q.fkUserID
LEFT JOIN (
SELECT fkQuestionID, MAX(AnswerID) AS AnswerID
FROM answers
GROUP BY fkQuestionID
) t ON t.fkQuestionID = Q.QuestionID  
LEFT JOIN answers A ON A.pkAnswerID = t.AnswerID
LEFT JOIN users U2 ON U2.UserID=A.fkUserID


UNION ALL

SELECT 
'Discussion' as EventType,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
D.DiscussionTitle,
U3.Name as PostBy
FROM  dicussions D 
LEFT JOIN users U3 ON U3.UserID=D.fkUserID

当然，您可能希望使用 where 子句来限制特定用户。如果您想要他们回答的问题以及他们提出的问题，请在按用户查看时添加另一个 UNION ALL。

此时，您还应该检查索引以确保它们位于所有连接字段上。