如何合并获取评论与用户投票的两条MySQL查询以优化性能？

问题背景 #

现有两张数据表：comments_table（评论表）和votes_table（投票表），表结构及数据如下：

comments_table #

表结构：

id int(11) unsigned NOT NULL AUTO_INCREMENT,
media_id int(11) unsigned DEFAULT NULL,
user_id int(11) unsigned DEFAULT NULL, -- 评论用户ID
title varchar(300) DEFAULT NULL,
url varchar(300) DEFAULT NULL,
c_date datetime,
comment longtext DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`)

示例数据：

votes_table #

表结构：

comment_id int(11) unsigned DEFAULT NULL,
user_id int(11) unsigned DEFAULT NULL, -- 投票用户ID
vote tinyint(1) DEFAULT 0,
INDEX `comment_id` (`comment_id`)

示例数据：

原本使用两条WordPress数据库查询：

1. 获取指定media_id的所有评论及每条评论的总投票数
2. 获取指定user_id的用户投票记录

现在需要将这两条查询合并为一条，让每条评论结果中同时包含总投票数和当前用户对该评论的投票值。

解决方案 #

可以通过两次左连接投票表实现：一次用于计算总投票数，另一次用于关联当前用户的投票记录。

合并后的SQL查询 #

SELECT 
    ct.id, 
    ct.comment, 
    ct.user_id, 
    ct.user_display_name, 
    ct.avatar, 
    ct.c_date,
    SUM(vt_total.vote) AS total_votes, -- 总投票数
    COALESCE(vt_user.vote, 0) AS user_vote -- 当前用户的投票值，无投票则返回0
FROM $comments_table as ct 
LEFT JOIN $votes_table vt_total ON ct.id = vt_total.comment_id
LEFT JOIN $votes_table vt_user ON ct.id = vt_user.comment_id AND vt_user.user_id = %d
WHERE ct.media_id = %d
GROUP BY ct.id, vt_user.vote
ORDER BY ct.c_date DESC

对应的WordPress PHP代码 #

$comments_with_user_votes = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT 
            ct.id, 
            ct.comment, 
            ct.user_id, 
            ct.user_display_name, 
            ct.avatar, 
            ct.c_date,
            SUM(vt_total.vote) AS total_votes,
            COALESCE(vt_user.vote, 0) AS user_vote
        FROM $comments_table as ct 
        LEFT JOIN $votes_table vt_total ON ct.id = vt_total.comment_id
        LEFT JOIN $votes_table vt_user ON ct.id = vt_user.comment_id AND vt_user.user_id = %d
        WHERE ct.media_id = %d
        GROUP BY ct.id, vt_user.vote
        ORDER BY ct.c_date DESC",
        $user_id,
        $media_id
    ),
    ARRAY_A
);

关键说明 #

1. 双左连接投票表：
   • vt_total关联所有投票记录，通过SUM()计算总投票数
   • vt_user仅关联当前用户的投票记录，用COALESCE()处理无投票的情况（返回0）
2. GROUP BY 调整：把vt_user.vote加入分组，避免单个评论因用户投票记录导致分组异常
3. 性能优势：减少一次数据库请求，避免PHP层面的数组匹配操作，同时利用现有索引（media_id、comment_id）保证查询效率

内容的提问来源于stack exchange，提问作者Toniq