在查询中获取用户特定数据的最快方法是什么？

xxhby3vn 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(297)

我正在开发一个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后端使用LaaFLE，如果这是考虑的因素。
谢谢你的指导。

sql mysql performance laravel

来源：https://stackoverflow.com/questions/62593381/what-is-the-fastest-way-to-get-user-specific-data-along-with-a-query

2条答案

按热度按时间

vd8tlhqk1#

带有子查询的左联接与带有未索引表的联接一样好。也有例外，子查询将生成一个带有索引的内部临时表。这里可能不是这样。
但是，在您的案例中不需要子查询。你只需要一个正规的左连接。只要把条件写进去就行了 $user_id 在on子句中。您的查询应该如下所示：

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 ...
...

赞(0）回复(0）举报 2021-07-24

kyks70gy2#

回答我自己的问题。。。连接上的子查询是这里的瓶颈。用if-exists替换它们显著提高了速度，现在平均需要300毫秒。

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

赞(0）回复(0）举报 2021-07-24

我来回答

在查询中获取用户特定数据的最快方法是什么？

2条答案

相关问题

热门标签

最新问答