使用MySQL计数(1)，计数(2) ...etc使用联接

Question

我有四张桌子：

Table talks
table talks_fan
table talks_follow
table talks_comments

我想做到的是，每一次谈话都要考虑到所有的评论、粉丝和追随者。

到目前为止是我想出来的。

所有tables都有talk_id，仅在talks表中是主键。

SELECT
  g. *, 
  COUNT( m.talk_id ) AS num_of_comments,
  COUNT( f.talk_id ) AS num_of_followers

FROM
  talks AS g

LEFT JOIN talks_comments AS m
  USING ( talk_id )

LEFT JOIN talks_follow AS f
  USING ( talk_id )

WHERE g.privacy = 'public'
GROUP BY g.talk_id
ORDER BY g.created_date DESC 
LIMIT 30;

我也试过用这种方法

SELECT
  t.*,
  COUNT(b.talk_id) AS comments, 
  COUNT(bt.talk_id) AS followers 
FROM
  talks t
LEFT JOIN talks_follow bt
  ON bt.talk_id = t.talk_id
LEFT JOIN talks_comments b
  ON b.talk_id = t.talk_id
GROUP BY t.talk_id;

都给我同样的结果.？！

更新:创建语句

CREATE TABLE IF NOT EXISTS `talks` (
`talk_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` mediumint(9) NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`privacy` enum('public','private') NOT NULL DEFAULT 'private',
PRIMARY KEY (`talk_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

 CREATE TABLE IF NOT EXISTS `talks_comments` (
`comment_id` bigint(20) NOT NULL AUTO_INCREMENT,
`talk_id` bigint(20) NOT NULL,
`user_id` mediumint(9) NOT NULL,
`comment` text NOT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`status` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`comment_id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

 CREATE TABLE IF NOT EXISTS `talks_fan` (
`fan_id` bigint(20) NOT NULL AUTO_INCREMENT,
`talk_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`status` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`fan_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS `talks_follow` (
`follow_id` bigint(20) NOT NULL AUTO_INCREMENT,
`talk_id` bigint(20) NOT NULL,
`user_id` mediumint(9) NOT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE       CURRENT_TIMESTAMP,
PRIMARY KEY (`follow_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

工作的最后一个查询

SELECT t.* ,  COUNT( DISTINCT b.comment_id ) AS comments, 
            COUNT( DISTINCT bt.follow_id ) AS followers, 
            COUNT( DISTINCT c.fan_id ) AS fans
FROM talks t

LEFT JOIN talks_follow bt ON bt.talk_id = t.talk_id
LEFT JOIN talks_comments b ON b.talk_id = t.talk_id
LEFT JOIN talks_fan c ON c.talk_id = t.talk_id

WHERE t.privacy = 'public'
GROUP BY t.talk_id
ORDER BY t.created_date DESC 
LIMIT 30

编辑:整个问题的最终答案..。

我修改了查询并在PHP (Codeigniter)中创建了一些代码来解决我的问题--使用@Bill的推荐

        $sql="
    SELECT t.*,
                    COUNT( DISTINCT b.comment_id ) AS comments, 
                    COUNT( DISTINCT bt.follow_id ) AS followers, 
                    COUNT( DISTINCT c.fan_id ) AS fans,
                    GROUP_CONCAT( DISTINCT c.user_id ) AS list_of_fans
    FROM talks t

    LEFT JOIN talks_follow bt ON bt.talk_id = t.talk_id
    LEFT JOIN talks_comments b ON b.talk_id = t.talk_id
    LEFT JOIN talks_fan c ON c.talk_id = t.talk_id

    WHERE t.privacy = 'public'
    GROUP BY t.talk_id
    ORDER BY t.created_date DESC 
    LIMIT 30
    ";

    $query = $this->db->query($sql);
    if($query->num_rows() > 0)
    {

        $results = array();

        foreach($query->result_array() AS $talk){
            $fan_user_id = explode(",", $talk['list_of_fans']);
            foreach($fan_user_id AS $user){
                 if($user == 1 /* this supposed to be user id or session*/){
                     $talk['list_of_fans'] = 'yes';
                 }
            }

            $follower_user_id = explode(",", $talk['list_of_follower']);
            foreach($follower_user_id AS $user){
                 if($user == 1 /* this supposed to be user id or session*/){
                     $talk['list_of_follower'] = 'yes';
                 }
            }

             $results[] = array(
                    'talk_id'           => $talk['talk_id'], 
                    'user_id'           => $talk['user_id'],
                    'title'             => $talk['title'], 
                    'created_date'      => $talk['created_date'], 
                    'comments'          => $talk['comments'], 
                    'followers'         => $talk['followers'], 
                    'fans'              => $talk['fans'], 
                    'list_of_fans'      => $talk['list_of_fans'],
                    'list_of_follower'  => $talk['list_of_follower']                        
                    );

        }
    }

我仍然相信它可以在数据库中被优化，只需要使用结果.

我想，如果每一次谈话都有1000名追随者和2000名粉丝，那么结果将需要更长的时间来加载。如果把否定乘以10，或者我听错了.

编辑:为查询测试添加基准测试。

我使用了代码点火器分析器来知道查询完成执行所需的时间。

据说，我也开始在表中随意添加数据。

结果如下。

将数据应答到DB中后进行测试

Query Results time

table Talks
---------------
table data 50 rows.
Time: 0.0173 seconds

Table Rows: 644 rows
Time: 0.0535 seconds

Table Rows: 1250 rows
Time: 0.0856 seconds


Adding data to other tables
--------------------------
Talks = 1250 rows
talks_follow = 4115
talks_fan = 10 rows

Time: 2.656 seconds

Adding data to other tables
--------------------------
Talks = 1250 rows
talks_follow = 4115
talks_fan = 10 rows
talks_comments = 3650 rows

Time: 10.156 seconds

After replacing LEFT JOIN with STRAIGHT_JOIN

Time: 6.675 seconds

看来它对DB来说是极其沉重的……现在，在如何提高其性能方面，我将陷入另一个两难境地。

编辑:使用@leonardo_assumpcao建议

After rebuilding the DB using @leonardo_assumpcao suggestion
for indexing few fields..........


Adding data to other tables
--------------------------
Talks       = 6000  Rows
talks_follow    = 10000 Rows
talks_fan   = 10000 Rows
talks_comments  = 10000 Rows

Time: 17.940 second

这对大数据数据库正常吗.？

Answer 1

我可以说，这是(至少)一个最酷的选择发言，我今天改进了。

SELECT STRAIGHT_JOIN
  t.* ,
  COUNT( DISTINCT b.comment_id ) AS comments, 
  COUNT( DISTINCT bt.follow_id ) AS followers, 
  COUNT( DISTINCT c.fan_id )     AS fans

FROM
  (
    SELECT * FROM talks
    WHERE privacy = 'public'
    ORDER BY created_date DESC
    LIMIT 0, 30
  ) AS t

LEFT JOIN talks_follow   bt ON (bt.talk_id = t.talk_id)

LEFT JOIN talks_comments b  ON (b.talk_id = t.talk_id)

LEFT JOIN talks_fan      c  ON (c.talk_id = t.talk_id)

GROUP BY t.talk_id ;

但在我看来，您的问题存在于表中；获得高效查询的第一步是对所需联接上的每个字段进行索引。

我对上面显示的表做了一些修改；您可以看到它的代码这里 (更新)。

很有趣，不是吗？既然我们在这里，也拿你的错误模型：

​

​

首先使用MySQL测试数据库进行测试。希望它能解决你的表现问题。

(请原谅我的英语，这是我的第二语言)

Answer 2

您可以强制将其放入一个查询中，如下所示：

SELECT COUNT(*) num, 'talks' item         FROM talks
UNION
SELECT COUNT(*) num, 'talks_fan' item     FROM talks_fan
UNION
SELECT COUNT(*) num, 'talks_follow' item  FROM talks_follow
UNION
SELECT COUNT(*) num, 'talks_comment' item FROM talks_comment

这将为您提供一个五行结果集，每个表有一行。每一行都是特定表中的计数。

如果必须将其全部放到一行中，则可以像这样执行枢轴操作。

SELECT 
  SUM( CASE item WHEN 'talks'         THEN num ELSE 0 END ) AS 'talks', 
  SUM( CASE item WHEN 'talks_fan'     THEN num ELSE 0 END ) AS 'talks_fan', 
  SUM( CASE item WHEN 'talks_follow'  THEN num ELSE 0 END ) AS 'talks_follow', 
  SUM( CASE item WHEN 'talks_comment' THEN num ELSE 0 END ) AS 'talks_comment'
FROM 
(   SELECT COUNT(*) num, 'talks' item         FROM talks
    UNION
    SELECT COUNT(*) num, 'talks_fan' item     FROM talks_fan
    UNION
    SELECT COUNT(*) num, 'talks_follow' item  FROM talks_follow
    UNION
    SELECT COUNT(*) num, 'talks_comment' item FROM talks_comment
) counts

(这没有考虑到您的WHERE g.privacy =子句，因为我不明白。但是您可以向WHERE项中的四个查询中的一个添加一个UNION子句来处理这个问题。)

请注意，这实际上是四个单独表上的四个查询，强制进入一个查询。

顺便说一句，当COUNT(*)是表的主键时，id和COUNT(id)之间的值没有差别。COUNT(id)不计算id为NULL的行，但如果id是主键，则为NOT NULL。但是COUNT(*)更快，所以使用它。

编辑，如果您需要风扇的数量，跟随和注释行为每一个不同的谈话，这样做。这是同样的想法，做一个联合和一个支点，但有一个额外的参数。

SELECT 
      talk_id, 
      SUM( CASE item WHEN 'talks_fan'     THEN num ELSE 0 END ) AS 'talks_fan', 
      SUM( CASE item WHEN 'talks_follow'  THEN num ELSE 0 END ) AS 'talks_follow', 
      SUM( CASE item WHEN 'talks_comment' THEN num ELSE 0 END ) AS 'talks_comment'
FROM 
(   
          SELECT talk_id, COUNT(*) num, 'talks_fan' item     
            FROM talks_fan
        GROUP BY talk_id
    UNION
         SELECT talk_id, COUNT(*) num, 'talks_follow' item  
           FROM talks_follow
       GROUP BY talk_id
    UNION
         SELECT talk_id, COUNT(*) num, 'talks_comment' item 
           FROM talks_comment
       GROUP BY talk_id
) counts
GROUP BY talk_id

在这样做了很多年之后，我发现描述一个查询的最好方法是对自己说：“我需要一个结果集，每个xxx有一行，yyy、zzz和qqq列。”

Answer 3

计数相同的原因是，在联接组合了表之后才进行行计数。通过加入多个表，您将创建一个笛卡尔积。

基本上，你不仅是在计算每一次谈话的评论数量，还包括每一次演讲的关注者数量。然后，你将追随者数为每一次谈话的关注者*评论数。因此，数量是一样的，而且它们都太高了。

下面是一种简单的方法，可以编写一个查询来计数每个不同的注释、跟随者等，只需一次：

SELECT t.*, 
  COUNT(DISTINCT b.comment_id) AS comments, 
  COUNT(DISTINCT bt.follow_id) AS followers 
FROM talks t
LEFT JOIN talks_follow bt ON bt.talk_id = t.talk_id
LEFT JOIN talks_comments b ON b.talk_id = t.talk_id
GROUP BY t.talk_id;

关于您的评论:我不会在同一个查询中获取所有的追随者。你可以这样做：

SELECT t.*, 
  COUNT(DISTINCT b.comment_id) AS comments, 
  COUNT(DISTINCT bt.follow_id) AS followers, 
  GROUP_CONCAT(DISTINCT bt.follower_name) AS list_of_followers
FROM talks t
LEFT JOIN talks_follow bt ON bt.talk_id = t.talk_id
LEFT JOIN talks_comments b ON b.talk_id = t.talk_id
GROUP BY t.talk_id;

但是你得到的是一个字符串，后面的名字用逗号隔开。现在您必须编写应用程序代码来拆分逗号上的字符串，您必须担心一些跟随者名称实际上已经包含逗号，等等。

我会做第二个查询，为给定的谈话获取追随者。很可能你只想在一次特定的谈话中显示追随者。

SELECT follower_name
FROM talks_follow
WHERE talk_id = ?