5

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.

Improve this question
4
  • you should be using joins not subqueries Commented Jul 13, 2014 at 21:45
  • 1
    @Dagon . . . That is not strictly correct. A naive approach using join's would be a very bad idea in this case. Commented Jul 13, 2014 at 21:48
  • @Dagon i thing probably use join, can you kind give me for that with join? Commented Jul 13, 2014 at 21:52
  • @Dagon - technically a subquery is a join is a subquery, depending on the optimizer and whether you've appropriately correlated. IMO subqueries are more intuitive in this case. Advanced optimizers can transform between the 2 depending on statistics gathering. Commented Jul 13, 2014 at 21:56

3 Answers 3

Reset to default
7 
    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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I think this should work, but it still give wrong results for some of user rows.
@user3695637 was missing a DISTINCT and the sub-query will very likely be faster rather than JOIN's in this case as suggested by Gordon.
I believe that Gordon's result is faster, at least in some cases.
4

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.

Improve this answer

3 Comments

yeah, i have already set it as an index before. does it faster than join?
For the joins to work feasibly, you would need to pre-aggregate the tables in subqueries. Your approach is very reasonable.
For one case I tried, this ran about 100 times as fast as Prix's solution.
2

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.