我正在开发一个QnA应用程序，在这个应用程序中，我可以加载特定于用户的数据，比如用户是否投票给了一篇文章，或者是否关注了写文章的用户。 我现在的做法是-
SELECT tbl_answer.*, tbl_user.username, tbl_user.dp_62, tbl_personal_info.full_name, tbl_personal_info.tagline,
IFNULL(followers.following, 0) following,
IFNULL(voters.voted, 0) voted
FROM tbl_answer
LEFT JOIN tbl_user ON tbl_user.user_id = tbl_answer.user_id
LEFT JOIN tbl_personal_info ON tbl_personal_info.user_id = tbl_answer.user_id
LEFT JOIN (
SELECT answer_id, IF(a_vote_up = 1, 1, a_vote_down * -1) AS voted
FROM tbl_answer_vote
WHERE user_id = $user_id //Retrieved from the token beforehand
) voters ON voters.answer_id = tbl_answer.answer_id
LEFT JOIN (
SELECT following_id, 1 AS following
FROM tbl_user_follow
WHERE follower_id = $user_id
) followers ON followers.following_id = tbl_answer.user_id
WHERE question_id = ?
ORDER BY selected DESC,
under_review ASC,
answer_up_vote DESC,
answer_id ASC,
answer_down_vote ASC
LIMIT 30
它的工作似乎没有那么快，有时需要超过4秒。 答案表有近百万条条目。 在最短的时间内达到这种结果的一般过程是什么？ 我在API后端使用Laravel，如果这是需要考虑的因素的话。
谢谢你的任何指导。
带有子查询的左联接与带有未索引表的联接一样好。 也有例外，子查询将生成一个带有索引的内部临时表。 这里可能不是这样。
但是-在您的情况下不需要子查询。 你只需要一个常规的左联接。 只需在on子句中包含条件on$USER_ID。 您的查询应该如下所示:
SELECT ...
FROM tbl_answer
LEFT JOIN tbl_user ON tbl_user.user_id = tbl_answer.user_id
LEFT JOIN tbl_personal_info ON tbl_personal_info.user_id = tbl_answer.user_id
-- no subqueries necessary here
LEFT JOIN tbl_answer_vote
ON tbl_answer_vote.answer_id = tbl_answer.answer_id
AND tbl_answer_vote.user_id = $user_id
LEFT JOIN tbl_user_follow
ON tbl_user_follow.following_id = tbl_answer.user_id
AND tbl_user_follow.follower_id = $user_id
WHERE ...
...
回答我自己的问题。。。 连接上的子查询是这里的瓶颈。 用IF-EXISTS替换它们，速度显著提高，现在平均需要300ms。
SELECT
Q.*,
...
...
IF(EXISTS (SELECT NULL FROM tbl_question_vote WHERE user_id = 642 AND question_id = Q.question_id), 1, 0) voted,
IF(EXISTS (SELECT NULL FROM tbl_question_bookmark WHERE user_id = 642 AND question_id = Q.question_id), 1, 0) bookmarked,
IF(EXISTS (SELECT NULL FROM tbl_user_follow WHERE follower_id = 642 AND following_id = Q.user_id), 1, 0) following
LEFT JOIN tbl_user ON tbl_user.user_id = Q.user_id
LEFT JOIN tbl_personal_info ON tbl_personal_info.user_id = Q.user_id
WHERE
under_review = 0 AND ads = 1
ORDER BY pinned DESC, prioritize DESC, question_id DESC
LIMIT 30
