mysql multiple COUNT() from multiple tables with LEFT JOIN

Question

I want to show the conclusion of all users.

I have 3 tables.

table post

post_id(index)     user_id
1                  1
2                  3
3                  3
4                  4

table photo

photo_id(index)     user_id
1                   2
2                   4
3                   1
4                   1

table video

photo_id(index)     user_id
1                   4
2                   4
3                   3
4                   3

and in table user

user_id(index)     user_name
1                  mark
2                  tommy
3                  john
4                  james

in fact, it has more than 4 rows for every tables.

I want the result like this.

id     name      post    photo   videos
1      mark      1       2       0
2      tommy     0       1       0
3      john      2       0       2
4      james     1       1       2
5      ..        ..      ..      ..

Code below is SQL that can work correctly but very slow, I will be true appreciated if you help me how it using LEFT JOIN for it. Thanks.

SQL

"select user.*, 
(select count(*) from post where post.userid = user.userid) postCount,
(select count(*) from photo where photo.userid = user.userid) photoCount,
(select count(*) from video where video .userid = user.userid) videoCount
from user order by user.id"

(or ORDER BY postCount, photoCount or videoCount ASC or DESC as i want )

I done researched before but no any helped me.

Answer 1

    SELECT u.user_id, 
           u.user_name,
           COUNT(DISTINCT p.post_id) AS `postCount`,
           COUNT(DISTINCT ph.photo_id) AS `photoCount`,
           COUNT(DISTINCT v.video_id) AS `videoCount`
      FROM user u
 LEFT JOIN post p
        ON p.user_id = u.user_id
 LEFT JOIN photo ph
        ON ph.user_id = u.user_id
 LEFT JOIN video v
        ON v.user_id = u.user_id
  GROUP BY u.user_id
  ORDER BY postCount;

Live DEMO

Answer 2

Your method of doing this is quite reasonable. Here is your query:

select user.*, 
       (select count(*) from post where post.userid = user.userid) as postCount,
       (select count(*) from photo where photo.userid = user.userid) as photoCount,
       (select count(*) from video where video.userid = user.userid) as videoCount
from user
order by user.id;

For this query, you want the following indexes:

post(userid)
photo(userid)
video(userid)
user(id)

You probably already have the last one, because user.id is probably the primary key of the table.

Note that a left join approach is a bad idea in this case. The three tables -- posts, photos, and videos -- are independent of each other. If a user has five of each, then joining them together would produce 125 intermediate rows. If a user has fifty of each, it would be 125,000 -- a lot of extra processing.

Answer 3

Your answer is probably slow as it is using a correlated sub-query i.e. the sub query is running once for each user_id (unless the optimizer is doing something smart - which shouldn't be counted on).

You could use a left outer join and count or use something temporary like:

    SELECT u.user_id, 
           u.user_name, 
           ph.user_count AS 'photoCount', 
           p.user_count AS 'postCount', 
           v.user_count AS 'videoCount'
      FROM user u
INNER JOIN (  SELECT user_id,
                     COUNT(*) AS user_count 
                FROM photo 
            GROUP BY user_id
           ) ph
        ON ph.user_id=u.user_id
INNER JOIN (  SELECT user_id, 
                     COUNT(*) AS user_count 
                FROM post
            GROUP BY user_id
           ) p 
        ON p.user_id=u.user_id
INNER JOIN (  SELECT user_id, 
                     COUNT(*) AS user_count 
                FROM video 
            GROUP BY user_id
           ) v
        ON v.user_id=u.user_id

There are pros and cons for both (depending on indexes). Always have a look at the query plan (using EXPLAIN for MySQL).