mysql multiple COUNT() from multiple tables with LEFT JOIN

mysql, php

Solution

    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

Problem

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.

Original source